Connecting PostGIS to Leaflet using PHP

For a few years now, I’ve been building wikimaps that rely on a PostgreSQL/PostGIS database to store geographic data and Leaflet to display that data on a map. These two technologies have increasingly become the industry standard open-source front- and back-end web mapping tools, used together by such behemoths as Openstreetmap and CartoDB. While you can use a go-between such as Geoserver Web Feature Service (WFS) to connect the two, the most simple, flexible, and reliable way I’ve found to connect data to map is through a little PHP script that essentially formats the queries and lets PostGIS and JavaScript do all the heavy lifting (note that my opinion on this has changed since I wrote my series of tutorials on web mapping services two years ago).

It occurred to me recently that I should share my basic technique, and I did so for UW-Madison Cartography students in a short presentation as part of our Cart Lab Education Series. This blog post is essentially a transcription of that tutorial. It assumes you have already installed Postgresql with the PostGIS extension and pgAdminIII GUI (I highly recommend installing all three through the Stack Builder), and possess a working understanding of SQL queries, HTML, JavaScript, and Leaflet.js. I will gently introduce some PHP; this shouldn’t be too painful if you already have a bit of background in JS.

Let’s get started, shall we?

I have provided the tutorial sample code on GitHub. A colleague just introduced me to the wonders of Adobe Brackets, so let’s use it to take a look at the directory tree first:

Directory Tree

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”.

PostGIS Shapefile and DBF Loader

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.

html snippit

Style.css contains basic styles for placing the map and form side-by-side on the page, and bears no further mention.

main.js snippet

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.

The jQuery.ajax method is a very simple substitute for a whole lot of ugly XMLHttpRequest native code. It can take data as a string of parameters in URI scheme or as a JavaScript object; I’m using the latter because it is neater. You can include any parameters, but the important part is to think about what you need out of the DOM to create the SQL query that’s going to grab your data. I’m designating the table name and the fields here, although you could also hard-code both in the PHP if you don’t need them to be dynamic.

OK, let’s flip over and see what’s going on in getData.php…

php snippet

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 is a special designated variable in PHP that is an array of parameters and associated values submitted to the server with a GET header (there is also one for the POST header). In PHP, an array is analogous to both an object and an array in JavaScript; it’s just that the latter form uses zero-based sequential integers as keys. In this case, we can think of the $_GET array as just like the AJAX data object, with the exact same keys and values (table with the string value "fracsandsites" and 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 variable.

Since $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 $fieldstr: 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!

sql editor output

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:

PHP snippet

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 $response. The 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:

Response in the console

Now all we have to do is process this data through a bit of JavaScript and stick it on the map. Easy-peasy. I’ll start with the first part of the mapData callback function:

js snippet

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 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:

dataArray in console

Now, for each row, we need to correctly format the data as a GeoJSON feature:

js snippet

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 d. 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:

the geojson in the DOM tab

Now that we have our GeoJSON put together, we can go ahead and use L.geoJson to stick it on the map.

js snippet

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 "javascript:submitQuery()". This calls the submitQuery function in main.js. Here is that function:

js snippet

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).

With the 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:

php snippet

The 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 $distance variable.

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:

screenshot of query results

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.