Building a Google Maps Application with PostgreSQL


Google Map
How to save points from Google maps in a PostgreSQL database. This has been adapted from 2 articles for MySQL, referenced from The Google Geo Developers Blog -

New Articles: Powering Maps API v3 Apps with MySQL

"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

 

Share It

Comments are closed