In Part 1 of this two-part series, we looked at installing PostGIS, a promising extension for the PostgreSQL database that adds spatial objects to a database.
Specifically, we covered:
- Installing the prerequisite libraries for PostGIS.
- Setting up PostGIS on Linux.
- Creating your first spatial database – TestGIS.
- Importing data into TestGIS
In Part 2, we will now cover
- Connecting to TestGIS with PHP
- Connecting to TestGIS with Java
- Connecting to TestGIS with .Net
- Visualizing data from TestGIS on a Map
Connecting to TestGIS with PHP
The PHP PostgreSQL extensions provide a quick way for PHP developers to connect with PostgreSQL and hence PostGIS.
Getting Ready
We need to install the Apache Web Server, PHP5 and the php-pgsql package. We do so by using the following command
sudo apt-get install apache2 php5 php5-pgsql
How to do it
Open the Terminal and navigate to your /var/www/html/ folder and create a new folder named chapter1 by the following command
sudo mkdir chapter1
Create a new PHP file named country.php using the vi editor or your favorite text editor. We will use the CRUD model for creating our country class, which will allow us to :
- Create a new country and insert it in the country table in the TestGIS database
- Read out all the country values in the database
- Update any existing country in the TestGIS database
- Delete a country from the TestGIS database
In our country.php file, add the following code.
<?php
class country {
public $id;
public $name;
public $boundary;
public $name_alias;
//Database Variables
public $connection;
public function __construct() {
$this->connection = pg_connect("host=localhost port=5432 dbname=TestGIS user=postgis password=postgis") or print("cant connect");
}
public function __destruct() {
}
public function create() {
}
public function read() {
$result = pg_query($this->connection, "SELECT id, name, ST_asText(boundary), name_alias FROM country");
if (!$result) {
echo "An error occured.\n";
exit;
}
else {
echo "<table border=1>";
echo "<tr><td>ID</td><td>Name</td>
<td\>Boundary</td></tr>";
while ($row = pg_fetch_row($result)) {
echo "<tr>";
echo "<td valign=top>".$row[0]."</td>";
echo "<td valign=top>".$row[1]."</td>";
echo "<td valign=top>".$row[2]."</td>";
echo "</tr>";
}
echo "</table>";
}
pg_close($this->connection);
}
public function update(){
}
public function delete(){
}
}
$myCountry = new country();
$myCountry->read();
?>
Our code execution begins by creating a new object of the country class and calling its read()method. The read method begins with connecting to the postgis database – TestGIS via the pg_connect() PHP function. Once the connection is established, we run a SQL Query using the PHP pg_query() function. The results of the query are fetched one at a time using the pg_fetch_row() function. We style the output using HTML tables and rows. Run the script in your browser, and you should see the following result.
We now modify the create() function to read values from the Flickr GEO API via the XML interface to get the boundary information for United States at http://www.flickr.com/services/api/explore/flickr.places.getInfo. Note the WOEID for United States is 23424977. The WOEID is a Where On Earth ID for a place (country, city, metro, district), a key term for Yahoo Geo Technologies. Find More information at http://developer.yahoo.com/geo/
Also note that we defined our database table with SRID type as 4326, so we need to process the XML response from the flickr.places.getInfo API call, to convert the latitude, longitude pair to a longitude, latitude pair. We do so by parsing each latitude, longitude pair and reversing it.
Here is the full create() method (replace the xxxxxxxxxxxxx with your own key)
public function create($id,$name) {
$boundary = "";
$xml = simplexml_load_file("http://api.flickr.com/services/rest/?method=flickr.places.getInfo&api_key=xxxxxxxxxxxxxxx &woe_id=23424977");
$result =$xml->xpath("place/shapedata/polylines/polyline");
for($i=0;$i<count($result);$i++)
{
$newResult = explode(" ", $result[$i]);
$finalResult = "";
$latLonArray = "";
for($j=0;$j<count($newResult);$j++)
{
// Reverse the latitude and longitude pairs
$finalResult = explode(",", $newResult[$j]);
if($j<count($newResult)-1)
$latLonArray .= $finalResult[1]." ".$finalResult[0].",";
else
$latLonArray .=$finalResult[1]." ".$finalResult[0];
// End of Latitude / Longitude reverse.
}
if($i<count($result)-1)
{
$boundary.= "(".$latLonArray."),";
}
else
{
$boundary.= "(".$latLonArray.")";
}
}
$insertSQL = "INSERT INTO country (id, name,boundary) VALUES
($id, '$name', ST_GeographyFromText ('MULTIPOLYGON((\$boundary))'))";
$result = pg_query($this->connection, $insertSQL);
if (!$result)
{
echo "An error occurred.\n";
exit;
}
else
{
echo "Query ran just fine";
}
}
We call the create() method as
$myCountry = new country();
$myCountry->create("1","United States");**
The highlighted code is where we generate our INSERT statement for our TestGIS table country; we specify the ID as “1” and name of the country as “United States”.
We can insert any other country by changing the parameters in the create() function, as well as the flick geo API by passing the right WOEID.
How it works
The PostGIS functions can be used as regular SQL queries via the PHP PostgreSQL functions. So no separate library is needed. We used the PHP SimpleXML functions to parse the Flick GEO API and convert the multiple polygons (in latitude, longitude format) for a country into longitude, latitude based polygons and inserted the same in our country table. We used XPath to traverse the XML response.
The key in the used in the XML API at
http://api.flickr.com/services/rest/?method=flickr.places.getInfo&apikey=xxxxxxxxxxxx&woeid=23424977 gets expired after some time, to get the new key visit http://www.flickr.com/services/api/explore/flickr.places.getInfo and use the WOEID for United States – 23424977 – to get a new API key.
Tips
Find the PHP source code for this recipe within the VirtualBox Ubuntu Image available for download on the author’s blog. http://justgeeks.in/postgis-article
Connecting to TestGIS with Java
We looked at working with a PostGIS database with PHP, but there more developers work with Java than PHP (See Tiobe Programming Language Index –http://www.tiobe.com/index.php/content/paperinfo/tpci/index.html that gives a picture of Java’s popularity over PHP and a few other languages). As well as the popularity and mass reach of the Android mobile ecosystem, Java and J2EE based solutions have a substantial stronghold in the market.
So let us now run a quick example of connecting to PostGIS via Java.
Getting ready
To write and compile Java applications in our VirtualBox Ubuntu Installation, we check the current Java version and install the latest OpenJDK version 7 with the following commands
postgis@postgis:~\$ sudo apt-get install openjdk-7-jdk
For writing our java applications, we install the NetBeans IDE by downloading an installer from http://netbeans.org or using the following command on Ubuntu
**sudo apt-get install netbeans **
We also need to download the JDBC package for PostgreSQL, which can be obtained from http://jdbc.postgresql.org/
How to do it
Start a new project in NetBeans and name it as TestGIS, as shown in the figure below.
Set the Main Class as country, click Finish to complete the wizard. Next within your project folder, create a new folder named “lib” and copy the JDBC jar file for PostgreSQL that we downloaded before. Add this jar file to your project library.
In your country.java file, add the following code to fetch information from our TestGIS database
package TestGIS;
import java.sql.*;
/**
* @author zeeshan
*/
public class country {
/**
@param args the command line arguments
*/
public static void main(String[] args) {
Connection connection = null;
Statement sql = null;
ResultSet result = null;
String url = "jdbc:postgresql://localhost:5432/TestGIS";
String username = "postgis";
String password = "postgis";
try
{
connection = DriverManager.getConnection(url, username,password);
sql = connection.createStatement();
result = sql.executeQuery("select * from country");
if (result.next()) {
System.out.println(result.getString(1)+ ","+ result.getString(2)+","+result.getString(3));
}
}
catch (SQLException e)
{
System.out.println("Error " +e);
}
}
}
Run the program by hitting F6 within Netbeans. Yyou should see the first rows from our country table as
How it Works
We used the standard JDBC API for Java and loaded a custom driver for PostgreSQL and used it to connect Java to our PostGIS database. For more information on JDBC – please visit http://docs.oracle.com/javase/tutorial/jdbc/index.html
Connecting to TestGIS with .Net
We explored working with our PostGIS database with PHP and Java. For Microsoft .Net developers, the choices for a Spatial Database have been limited. With the recent release of SQL Server 2012 there has been a new start for Spatial data processing for the Windows world, however nothing compares to the power of PostGIS (as of this writing). So we write a simple .Net project that shows an example of connecting to PostGIS with .Net (C#).
Getting ready
Download and install Visual C# 2010 Express from http://www.microsoft.com/visualstudio/en-us/products/2010-editions/visual-csharp-express. We also need the .Net Data Provider for PostgreSQL – Npgsql, which is available for download at http://npgsql.projects.postgresql.org/
Since we are working in the Windows domain, we also need to setup a postgis installation in Windows and create the same TestGIS database. Follow the PostGIS installation for Windows athttp://postgis.refractions.net/download/windows/ to get your Windows environment ready for this example.
Another way would be to configure the Linux based setup on a live server and connect to that live database from .Net (C#). Still another way would be to configure our VirtualBox Ubuntu installation and allow it to accept PostgreSQL connections from the host. We assume a regular Windows based installation of PostGIS for this tutorial, but feel free to try any of the alternatives discussed.
How to do it
Open Microsoft Visual C# 2010 Express and create a new project titled “HelloPostGISCSharp” by selecting the Windows Form Application template.
Open the Form1.cs and add a large TextBox and Label.
Add a reference to the Ngsql library we downloaded (make sure you download the library matching your .Net Framework version – we used version 3.5) by using the Solution Explorer on the right hand side.
In your Form1.cs file, import the Npgsql library by using the following command
** using Npgsql;**
Double click your Form to take you to the Form1_Load method and add the following code within the function.
private void Form1_Load(object sender, EventArgs e)
{
string serverName = "localhost";
string port = "5432";
string userName = "postgis";
string password = "postgis";
string databaseName = "TestGIS";
string connString = String.Format("Server={0};Port={1}; User Id={2};Password={3};Database={4};",
serverName, port, userName, password, databaseName);
try
{
NpgsqlConnection pgsqlConnection = new NpgsqlConnection(connString);
pgsqlConnection.Open();
NpgsqlCommand command = new NpgsqlCommand ("select id,name,ST\_asText(boundary)
from country limit 1", pgsqlConnection);
NpgsqlDataReader dr = command.ExecuteReader();
while(dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
textBox1.Text+=dr[i] + "\t";
}
}
pgsqlConnection.Close();
}
catch
{
Console.WriteLine("Connection failed");
}
}
Running the example produces the following result.
How it works
We created a database connection string, passing the host, database name, username and password for our TestGIS database. The NpgsqlConnection, NpgsqlCommand and NpgsqlDataReader classes from the Npgsql library are used to create a connection to the database, run a SQL command and parse the resultant rows respectively. The textbox is populated with values for each row.
Tips
The Npgsql library has great documentation at http://npgsql.projects.postgresql.org/docs/manual/UserManual.html, covering all aspects of database statements, from select to insert statements, as well as prepared statements. So if you are planning to make a killer GIS product in .Net, do not hesitate to use PostGIS.
Exploring PostGIS 2.1
So far we have discussed installing and connecting to our PostGIS database. Let us now explore the features in PostGIS 2.1.
Getting ready
PostGIS 2.0 adds support for GML – Geography Markup Language, an XML based modelling language for geographic systems. Read more about GML at http://www.opengeospatial.org/standards/gml and how it is different to the Google Earth promoted KML at http://en.wikipedia.org/wiki/GeographyMarkupLanguage#GMLandKML
How to do it
Open the Terminal on your Ubuntu Linux and start the PostgreSQL prompt (psql) as
postgis@postgis:~\$ psql -Upostgis -W TestGIS
At the psql prompt, enter \x and press enter, we use this to enable expanded display at the psql prompt
TestGIS=# \x
Expanded display is on.
Now we use the ST_AsGML function on our country table to export the boundary data in the GML specification with the following command
TestGIS=# SELECT ST_AsGML(boundary) from country limit 1;
You should get the following output
<gml:MultiPolygon srsName=”EPSG:4326″> <gml:polygonMember><gml:Polygon> <gml:outerBoundaryIs>….
Tips
A full list of new functions added, enhanced or changed can be found at http://postgis.refractions.net/docs/PostGISSpecialFunctions_Index.html
Visualizing data from TestGIS on a Map
The true power of a GIS system is the ability to work well with maps. We use the Leaflet library from Cloudmade.com, a company that provides range of applications and utilities for location based services and Maps. We use leaflet and PostGIS to visualize a map with data from our TestGIS database.
Getting ready
To get an overview of how Leaflet works, visit http://leaflet.cloudmade.com/. The library works in a very similar way to the Google Maps API. You need to register your application at http://cloudmade.com/register and get a key. We could use Google Maps API in this example, however the Google Maps API forbids loading a GEOJSON or KML file from a non-public URL, so working with Google Maps and local GEOJSON or KML data is not possible.
How to do it
Within your /var/www/chapter1 folder, create new file named map.php and enter the following PHP code.
<!DOCTYPE html>
<html>
<head>
<title>Visualizing PostGIS data on Maps</title>
<meta name="viewport"
content="width=device-width, initial-scale=1.0, user-scalable=no">
<meta charset="UTF-8">
<link rel="stylesheet" href="http://code.leafletjs.com/leaflet-0.3.1/leaflet.css" />
<!--[if lte IE 8]>
<link rel="stylesheet" href="http://code.leafletjs.com/leaflet-0.3.1/leaflet.ie.css" />
<![endif]-->
<script src="http://code.leafletjs.com/leaflet-0.3.1/leaflet.js"></script>
<script src="boundary.js" type="text/javascript"></script>
</head>
<body>
<div id="map" style="height: 600px"></div>
<script>
var map = new L.Map('map');
var mapKey = "xxxxxxx"; // Get your key from cloudmade.com
var cloudmadeUrl = 'http://{s}.tile.cloudmade.com/'+mapKey+'/997/256/{z}/{x}/{y}.png',
cloudmadeAttribution = 'Map data © 2011 OpenStreetMap contributors, Imagery © 2011 CloudMade',
cloudmade = new L.TileLayer(cloudmadeUrl, {maxZoom: 18, attribution: cloudmadeAttribution});
map.setView(new L.LatLng(37.775057,-122.433701), 3).addLayer(cloudmade);
var geojsonLayer = new L.GeoJSON(boundary);
map.addLayer(geojsonLayer);
</script>
<script type="text/javascript">if(!NREUMQ.f){NREUMQ.f=function(){NREUMQ.push(["load",new Date().getTime()]);var e=document.createElement("script");e.type="text/javascript";e.src=(("http:"===document.location.protocol)?"http:":"https:")+"//"+"js-agent.newrelic.com/nr-100.js";document.body.appendChild(e);if(NREUMQ.a)NREUMQ.a();};NREUMQ.a=window.onload;window.onload=NREUMQ.f;};NREUMQ.push(["nrfj","beacon-1.newrelic.com","7d8608a34f","3053298","YFdVYEsAVxdYAhAICVkddldNCFYKFhQXBBQYRkJAVhNQBVUSSwQCXkY=",0,118,new Date().getTime(),"","","","",""]);</script><script type="text/javascript">if(!NREUMQ.f){NREUMQ.f=function(){NREUMQ.push(["load",new Date().getTime()]);var e=document.createElement("script");e.type="text/javascript";e.src=(("http:"===document.location.protocol)?"http:":"https:")+"//"+"js-agent.newrelic.com/nr-100.js";document.body.appendChild(e);if(NREUMQ.a)NREUMQ.a();};NREUMQ.a=window.onload;window.onload=NREUMQ.f;};NREUMQ.push(["nrfj","beacon-1.newrelic.com","7d8608a34f","3053298","YFdVYEsAVxdYAhAICVkddldNCFYKFhQXBBQYRkJAVhNQBVUSSwQCXkY=",0,87,new Date().getTime(),"","","","",""]);</script><script type="text/javascript">if(!NREUMQ.f){NREUMQ.f=function(){NREUMQ.push(["load",new Date().getTime()]);var e=document.createElement("script");e.type="text/javascript";e.src=(("http:"===document.location.protocol)?"http:":"https:")+"//"+"js-agent.newrelic.com/nr-100.js";document.body.appendChild(e);if(NREUMQ.a)NREUMQ.a();};NREUMQ.a=window.onload;window.onload=NREUMQ.f;};NREUMQ.push(["nrfj","beacon-1.newrelic.com","7d8608a34f","3053298","YFdVYEsAVxdYAhAICVkddldNCFYKFhQXBBQYRkJAVhNQBVUSSwQCXkY=",0,115,new Date().getTime(),"","","","",""]);</script><script type="text/javascript">if(!NREUMQ.f){NREUMQ.f=function(){NREUMQ.push(["load",new Date().getTime()]);var e=document.createElement("script");e.type="text/javascript";e.src=(("http:"===document.location.protocol)?"http:":"https:")+"//"+"js-agent.newrelic.com/nr-100.js";document.body.appendChild(e);if(NREUMQ.a)NREUMQ.a();};NREUMQ.a=window.onload;window.onload=NREUMQ.f;};NREUMQ.push(["nrfj","beacon-1.newrelic.com","7d8608a34f","3053298","YFdVYEsAVxdYAhAICVkddldNCFYKFhQXBBQYRkJAVhNQBVUSSwQCXkY=",0,116,new Date().getTime(),"","","","",""]);</script></body>
</html>
Now open your terminal and run the following SQL query on our TestGIS database and the country table.
TestGIS=# SELECT id, name, ST_AsGeoJSON(boundary), name_alias FROM country where id=1;
Copy over the result and paste it in a new file named boundary.js within the same folder. With the following format, copy the result of the st_asgeojson field over to the geometry option.
var boundary = {
"type": "Feature",
"geometry": {
Your boundary.js file should now look like
var boundary = {
"type": "Feature",
"geometry": {
"type": "MultiPolygon",
"coordinates":[[[[-121.334671020510001,49.001365661621001],[-121.15495300293,49.000759124756001],[-121.064849853520002,49.002490997313998],[-120.772705078119998,49.021659851073998],[-120.645988464360002,48.997882843017997],[-120.183563232419999,48.997470855712997],[-119.937149047849999,48.999710083007997],[-119.665832519529999,49.015579223632997],……………..
Running the example in your local browser will now show the following result.
How it works
The code works in a similar way to Google Maps’ JavaScript based maps. We load a map and center it around San Francisco with a zoom level of 3, we then load the US boundary via the GeoJSON obtained from the country table in our TestGIS database and add it as a layer to the map.
Tips
Another option to visualize the PostGIS data is via OpenLayers. For example to visualize the geographical boundaries of United States, we copy and paste the GeoJSON from our SQL result ontohttp://openlayers.org/dev/examples/vector-formats.html, which produces the following result. PacktPub.com has an excellent book on OpenLayers – https://www.packtpub.com/openlayers-2-1-javascript-web-mapping-library-beginners-guide/book, so if you want to explore OpenLayers, buy the book.
The code for this article can be found at my dropbox public folder (alternate download at GDrive), while the link for the PostGIS VirtualBox Image is here.
That wraps up our two part tutorial. Let me know how you get on in the comments below.
Author: Zeeshan Chawdhary