Building a Google Maps Application with PostgreSQL

Google Map

"Using PHP/MySQL with Google Maps" shows how to pull data from a SQL database as XML and display that on the map.

"From InfoWindows to a Database: Saving User-Added Form Data" shows you how to enable users to add geo-tagged information to a database via your map application.

Migrating this to PostgreSQL was worth the effort as it opened opportunities to integrate the Google Maps application with PostGIS.  See Google Maps (API 3) application and related story: Getting Productive With QGIS, Postgresql, PostGIS, and Google maps

The following code snippets give examples of the PostgreSQL adapted code - no other changes are needed from the referenced articles. Learn more about this in our online training module - Getting Productive with (Q)GIS

1. PHP code to output the XML to view geo-tagged points saved in a PostgreSQL table

 

include("dbinfo.php"
// Start XML file, create parent node
$dom = new DOMDocument("1.0"
$node = $dom->createElement("markers"
$parnode = $dom->appendChild($node);
// Opens a connection to a pgSQL server
$conn_string = "host=$host port=5432 dbname=$db user=$dbuser password=$pwd";
$conn = pg_connect($conn_string);  
if(!$conn){
    die ("Could not open connection to database server"
}
$query = "SELECT * FROM marker";
$result = pg_query($conn, $query);
if(!$result){
    die('Invalid query : ' . pg_last_error());
}
header("Content-type: text/xml"
// Iterate through the rows, adding XML nodes for each
while ($row = pg_fetch_assoc($result)){
// ADD TO XML DOCUMENT NODE
$node = $dom->createElement("marker"
$newnode = $parnode->appendChild($node);
$newnode->setAttribute("id",$row['id']);
$newnode->setAttribute("name",$row['name']);
$newnode->setAttribute("address", $row['address']);
$newnode->setAttribute("lat", $row['lat']);
$newnode->setAttribute("lng", $row['lng']);
$newnode->setAttribute("type", $row['type']);
}
echo $dom->saveXML();
?>

2. PHP code to save geo-tagged points clicked on a Google Map to a PostgreSQL table

 

require("dbinfo.php"
// Opens a connection to a pgSQL server
$conn_string = "host=$host port=5432 dbname=$db user=$dbuser password=$pwd";
$conn = pg_connect($conn_string);
if(!$conn){
    die ("Not connected : " . pg_last_error());
}
// Gets data from URL parameters
$name = $_GET['name'];
$address = $_GET['address'];
$lat = $_GET['lat'];
$lng = $_GET['lng'];
$type = $_GET['type'];
// Insert new row with user data
$query = sprintf("INSERT INTO marker " .
         " (name, address, lat, lng, type ) " .
         " VALUES ('%s', '%s', '%s', '%s', '%s'",
         pg_escape_string($name),
         pg_escape_string($address),
         $lat,
         $lng,
         pg_escape_string($type));
$result = pg_query($conn,$query);
if (!$result) {
  die('Invalid query: ' . pg_last_error());
}
?>

Tag: postgresql postgis google map gis