Connecting PostGIS to Leaflet using PHP
Let’s get started, shall we?
As you can see, I’ve provided a data folder with a complete shapefile of some example data I had lying around. This open-access data is frac sand mines and facilities in western Wisconsin, and comes from the Wisconsin Center for Investigative Journalism. The first step is getting the data into a PostGIS-enabled database using pgAdminIII’s PostGIS Shapefile and DBF Loader (enabling this plug-in is slightly tricky; I recommend these instructions). After you have created or connected to your PostGIS database, select the loader plug-in from the pgAdminIII Plugins menu. Click “Add File”, navigate to the data directory, and select the shapefile. Make sure you change the number under the SRID column from 0 to 26916, the EPSG code for a UTM Zone 16N projection. PostGIS will require this projection information to perform spatial queries on the data. Once you have changed this number, click “Import”.
With your table created, we can now move to the fun part—code! For formatting clarity, I have only included screenshots of the code below, and will issue a reminder that the real deal is posted on GitHub here. I’ll only briefly touch on the index.html file and style.css files. Within index.html are links to the jQuery, jQuery-ui, and Leaflet libraries. I am mainly using jQuery to facilitate easy AJAX calls and jQuery-ui to create autocomplete menus for one of the form input text boxes. Leaflet of course makes the map. There are two divs in the body, one for the map and one for a simple form. The most useful thing to point out here is the
name attributes of the text
input elements, which will become important for use in constructing the SQL queries to the database.
Style.css contains basic styles for placing the map and form side-by-side on the page, and bears no further mention.
Turning to main.js (above), I have defined three global variables. The first,
map, is for the Leaflet map. The second,
fields, is an array of field names corresponding to some of the many attribute fields in my fracsandsites table in the database; this is the attribute data I want to see in the pop-ups on the map (other fields may be added). The third variable,
autocomplete, is an empty array that will hold feature names retrieved from the database for use in the autocomplete list.
The screenshot above shows the first two functions defined after the global variables, with a
$(document).ready call to the
initialize function. This function sets the map height based on the browser’s window height, then creates a basic Leaflet map centered on Wisconsin with a simple Acetate tileset for the basemap. It then issues a call to the
getData function. Here’s where the fun really begins.
OK, let’s flip over and see what’s going on in getData.php…
If you’re not used to seeing PHP code, some things here may look a bit odd. The first two lines declare that what follows is php code for the interpreter and enable some feedback on any I/O errors that occur. PHP is very picky about requiring semicolons at the end of each statement that isn’t a control structure (open or closing curly brace), and a syntax error will cause the whole thing to fail silently despite line 2. Lines 5-9 assign the database credentials to variables, which are denoted with the dollar sign (unlike JS, there is no
var keyword equivalent). Make sure to change these to your own database credentials. On line 11, the
$conn variable is assigned a
pg_connect object, which connects to the database using the parameters provided above. Note that in PHP, there is a difference between double and single quotes: both denote a string, but when using double quotes you can put variables directly into the string without concatenation and they will be recognized as variables by the interpreter, rather than as string literals. The following
if statement tests the integrity of the connection and quits with an error if it fails.
One important thing to note here is that for this to work, you must already have PHP installed and enable the php_pgsql extension by uncommenting it in your php.ini file, which is stored in your PHP directory (probably somewhere in Program Files if you’re on a PC). You can get PHP here.
Lines 18 and 19 retrieve the data sent over from the
$.ajax method in the JS.
$_GET array as just like the AJAX
data object, with the exact same keys and values (
table with the string value
fields with its array of string values). Line 18 assigns the first to a new PHP
$table variable and line 19 assigns the second to a
$fields is another array, to use it in a SQL query its values must be joined as comma-separated values in one string. The
foreach loop on line 23 does this, assigning each array index to the variable
$i and each value to the variable
$field. Within the loop, each variable is concatenated to the
$fieldstr variable (the
. is PHP’s concatenation operator), preceded by
l. because the SQL statement will assign the alias
l to the table name (why will become clear later).
After all fields have been concatenated, a final piece is added to the
ST_AsGeoJSON(ST_Transform(l.geom,4326)). This is the first bit of code we’ve seen that is specifically meant for PostGIS. We want to extract the geometry for each feature in the table in a form that’s usable to Leaflet, and that form is GeoJSON. Fortunately for us—and what makes PostGIS so easy to use for this purpose—PostGIS has a native method to translate geometry objects stored in the database into GeoJSON-formatted strings.
ST_AsGeoJSON can simply take the geometry column name as its parameter, but in order for the data to work on a Leaflet map, it has to be transformed into the WGS84 coordinate reference system (unprojected lat/long coordinates). For this purpose, PostGIS gives us
ST_Transform, which takes the geometry column name and the SRID of the CRS into which we want to transform it (In this case, the familiar-to-web-mappers 4326).
At this point, we now have all of the components of our first SQL query (line 31). If you were to print (or
echo in PHP parlance) the whole thing without the variables, you would see
$sql = "SELECT l.gid, l.createdby, l.featname, l.feattype, l.status, l.acres, ST_AsGeoJSON(ST_Transform(l.geom,4326)) FROM fracsandsites l";
And, in fact, if you copied everything inside the quotes into the SQL editor in pgAdminIII, you would get a solid response of those attributes from all features in the table. Go ahead and do it. DO IT NOW!
For now, I’m going to skip the next few lines (we’ll come back to them later) and wrap up my PHP with this:
Line 45 tests for a response from the database, but also sends the query to the server using the
pg_query method and assigns the response to the variable
while loop on lines 51-56 retrieves each table row from the
$response object (note: this is emphatically not an array; hence the use of the
pg_fetch_row method) and echoes each attribute value, with the attribute values separated by comma-spaces and the rows separated by semicolons. As previously mentioned, PHP’s
echo command “prints” data, in this case by sending it back to the browser in the XMLHttpRequest response object.
At this point we can go back to the browser and look at what we have. If you’re using Firebug, by default it will log all AJAX calls in the console, and you can see the response once it’s received. You should be able to see something like this:
mapData callback function:
Lines 39-44 remove any existing layers from the Leaflet map, which isn’t really necessary at this stage but will become useful later when we implement dynamic queries using the HTML input form. For now, skip down to Line 47 and notice that we are starting to build ourselves a GeoJSON object from scratch. This is really the easiest way to get this feature data into Leaflet. If you need to be reminded of the exact formatting, open any GeoJSON file in a text editor, or start making one in geojson.io. Once we have a shell of a GeoJSON with an empty
features array, the next step is to go ahead and split up the rows of data using the trailing comma-space and semicolon used in getData.php to designate the end of each row. Since these are also hanging onto the end of the last row, once the data is split into an array we need to pop off the last value of the array, which is an empty string. Now, if you console.log the
dataArray, you should see:
Now, for each row, we need to correctly format the data as a GeoJSON feature:
Each value of the
dataArray is split by the comma-spaces into its own array of attribute values and geometry. We create the GeoJSON feature object. The geometry is in the last value in the feature array (
d), which we access using the length of the
fields array since that array is one value shorter than
d and therefore its length matches the last index of
properties is assigned an empty object, which is subsequently filled with attribute names and values by the loop on lines 69-71. The
if statement on lines 74-76 tests whether the feature name is in the
autocomplete array, and if not, adds it to the
autocomplete array. Finally, the new feature is pushed into the GeoJSON
features array. Lines 82-84 activate the autocomplete list on the text input for the feature name in the query form. If you were to print the GeoJSON to the console and examine it in the DOM tab, you should see:
Now that we have our GeoJSON put together, we can go ahead and use
L.geoJson to stick it on the map.
I won’t go through all of this because it should be familiar code to anyone who has created GeoJSON overlays with Leaflet before. If you’re unfamiliar, I recommend starting with the Using GeoJSON with Leaflet tutorial.
This gets us through bringing the data from the database table to the initial map view. But what’s exciting about this approach is how dynamic and user-interactive you can make it. To give you just a small taste of what’s possible, I’ve included the simplest of web forms with which a user can build a query. If you’re at all familiar with SQL queries through database software, ArcMap, etc. (and you should be if you’ve gotten this far in this tutorial), you know how powerful and flexible they can be. When you’re designing your own apps, think deeply about how to harness this power through interface components that the most novice of users can understand. As a developer, you gain power through giving it to users.
As previously mentioned, the
form element in the index.html file contains two text inputs with unique
name attributes. The first of these is designated for distance (in kilometers), and the second is for the name of an anchor feature. We will use these values to perform a simple buffer operation in PostGIS, finding all features within the specified distance of the anchor feature. Ready to go? OK.
In index.html, the value of the form’s
action attribute is
submitQuery function in main.js. Here is that function:
We use jQuery’s
serializeArray method to get the values from the form inputs. This returns an array of objects, each of which contains the name and value of one input. Then, instead of creating the data object inline with the AJAX
data key, we create it as a variable so we can add the serialized key-value pairs to it. This is done through the
forEach loop, which takes each object in the
formdata array and assigns the
name value as a
data key and the
value value as a
data value. Get it? Good. (If not, just console.log the
data object after the loop).
data object put together, it’s time to issue a new
$.ajax call to getData.php. Let’s flip back over and take another look at that. Everything is the same except now we have a few more $_GET parameters to deal with and a different query task. Hence the
if statement on lines 34-40:
if statement tests for the presence of the
featname parameter in the list of parameters sent through AJAX. If it exists, that parameter’s value gets assigned to the
$featname variable and the
distance parameter value, multiplied by 1000 to convert kilometers to meters, gets assigned to the
Now for the hard part. Remember our simple SQL statement in which we gave the table and all of its attributes an alias (
l) for no apparent reason? Well, the reason is that we now have to concatenate SQL code for a table join onto it. Whenever you do a join in PostgreSQL, each table on either “side” of the join needs its own alias. Since the initial table reference is on the left side of the
JOIN operator, I assigned the original table the alias
l, for left, and the joined table
r, for right. Obvious, huh? Well, maybe not. In any case, the principle is that although both sides of the join reference the same table, Postgres will look at them like they are different tables. This is a
LEFT JOIN, meaning that the output will come from the table on the left, and the table on the right is used for comparison.
There are two parts to the comparison here: the
ON clause and the
WHERE clause. The
ST_DWithin statement following
ON specifies that output from the left table will be rows (features) within the user-given distance of rows (features) from the right table; since our table is stored in a UTM projection, the distance units will be meters (if it were stored as another CRS, say WGS84, we would have to use
ST_Transform on each table’s geometry for it to work). The
WHERE clause narrows the right-hand comparison to a single feature: the one named by the user in the input form. Translating to English, you could read this as, “Give me the specified attribute values and geometry for all of the features in the left table within my specified distance of the feature I named in the right table.” Or something like that.
OK, that’s the biggest headache of the whole demo, and it’s over. The features that get returned from this query now go back to the
mapData function in main.js. The
map.eachLayer loop that removes existing layers from the map now has a purpose: get rid of the original features so only the returned features are shown. The new features are plunked into a new homemade GeoJSON and onto the map through
L.geoJson. Here’s an example using a query for all sites within 10 km of the Chippewa Sands Company Processing Plant:
That’s it. There’s lots more you should learn about data security (particularly with web forms), PDO Objects, error prevention and debugging, etc before going live with your first app. But if you’ve gotten through this entire tutorial, congratulations—you’re on your way to designing killer user-friendly database-centered web maps.
Update 3/31/2017: I have been getting a lot of comments on this blog post recently requesting help with some error or other a reader is experiencing while trying to implement this tutorial. While I’m flattered the tutorial is getting a lot of attention, I am also very busy with work and family and unfortunately don’t have time to work through users’ issues with the code. Thus, I will no longer be responding to comments on this post. Keep in mind that the parameters and properties used in the examples above are tailored to the example dataset, and many will need to be altered if you’re implementing your own app. Also check that the right PHP extensions are enabled and your database connection info and credentials check out. For further assistance, I highly recommend using StackOverflow, W3Schools, and the PostgreSQL, PostGIS, and PHP documentation.