An OL3 map with Node.js and PostGIS

This is the second post in my Node.js map series, if you missed the first one check it out here (not a prerequisite). In this post I decided to show some love to OpenLayers 3 and PostGIS, looking at the Node.js pg package and building much the same content as my Node.js Leaflet MongoDB post.

Once you have the PostgreSQL/PostGIS database hooked up you are in good shape and, for the most part there is not too much difference between the approaches used for MongoDB or PostgreSQL. The main item I needed to adjust was my way of thinking about the JavaScript content and how to structure this to best respond to the Node.js framework. The idea behind this simple introduction to Node.js and maps series is that Node.js is a great platform and can accommodate many different combinations – so, mix up the stack, use your favorite database – or try something new.

As mentioned in my other Node.js post, if anyone has suggestions on how to improve on my content, please reach out, I would love to hear from you.

Here is what I would like to achieve from my Node.js OL3 PostGIS application:

  • Build something similar to the Mapbox Toggling layers example
  • Add point line and polygon layers to an OpenLayers 3 map
  • Store layers as GeoJSON in PostGIS
  • Use the Node.js pg package to request JSON and GeoJSON
  • Use the Jade template to build HTML based on layers

In the example I will only have three layers but the design is intended to be scalable and should be able to accommodate more content without the need to build custom HTML.

 

Installation

If you use Windows or OS X you can get the latest version of Node.js from the Node.js website it should be a pretty straight forward install and comes with the Node Package Manager, NPM which is really useful for installing a wide range of packages. You will be using NPM a lot but don’t worry it’s quite simple to use.

For Debian based distributions like Ubuntu, Node.js is available using the package manager. On Ubuntu 14.04 the package for Node.js is nodejs instead of node, on Debian 7 nodejs is not available.

Open your terminal/command line and make a directory in which you will place your applications, I have made a directory called projects/nodejs. cd into the directory you have just made.

Next we are going to install the Express web framework – I found this framework really useful as it generates a collection of template files and folders that make sense and provide structure to our project.

These commands make the Express functionality available globally so we can access it from any test application projects we build in the future. The global installation of Node.js Packages have to be run as root, so must be run with sudo.

 

Creating a project

We are now ready to crate a project. If you are not already there, cd into the directory you made and think of a name for your new project, I am going with pg_node. To create your pg_node project execute the following command, where pg_node is whatever name you have chosen for your project.

I recently tried this on Trusty (14.04) with no result and discovered I needed to install the nodejs-legacy package. It appears to only applies to Trusty (14.04) so if this applies to you, run the following command

This is the magical moment Node.js and Express get to work generating a project template for you. You should see a series of lines that begin with ‘create’ returned in the console. Take a look at the contents of your parent directory, you will notice there is now a directory with the name of your project. Take a look inside your project folder, you should see the following:

  • app.js – application nerve center
  • bin directory – internal workings
  • package.json – handles dependencies
  • public directory – location for images, scripts and stylesheets
  • routes directory – kind of like a switchboard, handles and directs requests
  • views directory – where the HTML output is constructed

If you take a look in your views directory you will see a few files with a .jade extension, these are the jade template files and will be used to generate HTML. To learn more about the Jade template engine take a look at jade-lang.com. Jade is a little weird at first but if you know some HTML you should be up and running in no time – the key to Jade is indentation.

 

Add Dependencies

Open the package.json file in your editor of choice, you will notice that it is a basic JSON file with a short description of the app and a list of the dependencies. Since we will be working with PostgreSQL/PostGIS we need to add two dependencies. In your package.json file type in the pg and postgeo dependencies seen below – don’t forget the trailing comma on all dependency lines apart from the last. When working with JSON or GeoJSON there are some useful syntax checkers like http://jsonlint.com and http://geojsonlint.com, if you have any doubt about your JSON syntax, these are useful resources. Another handy JSON resource is the command line tool https://www.npmjs.org/package/jsonlint – it’ll point out where your syntax errors exist. The asterisks in the lines we have added instruct NPM to get the latest version of that dependency. pg is a driver that Node.js uses to talk to PostgreSQL.

Save the edits you made to the package.json file and cd into your project directory, mine is called pg_node. To install the dependencies we have added we need to tell NPM to review our package.json file and take the necessary steps to get our new dependencies plugged in. To do this, execute the following command from inside your project folder.

The cogs will start turning and in a few seconds your new dependencies should be officially hooked up. At this stage you can run your application in the browser – execute the following line then navigate to http://localhost:3000 into your browser. If you are using a server, adjust local host to reflect your remote IP.

you should see the words ‘Express’ and ‘Welcome to Express’ in your browser and the following in your console:

To exit out of your application,  hit Ctrl-C.

If you are interested in seeing where the text displayed in your browser came from, take a look at the routes/index.js file, you should see the following:

At the start of the tutorial I compared the functionality handled by content in the routing directory to a switchboard. The index.js file receives a request from the URL http://localhost:3000 it then calls the res.render function which takes two arguments, firstly,  ‘index’ which instructs the application to use the index.jade template file in the views directory and secondly, a JSON object with a name of title and a value of ‘Express’. This is the text that displays in the browser, but lets see how that’s done by looking at the views/index.jade file

This is what the content of a jade template file looks like, notice the indentation. The first line imports anything in the layout.jade file, this is a nice feature because it means we can get a template structure setup in the layout.jade file and use this for consistency across our site. The block content line is telling Jade where the content from the layout file needs to go. Next the title variable passed from routes/index.jade, which in this case is ‘Express’ is assigned to an HTML <h1> tag then under that, a paragraph <p> with the text ‘Welcome to’ and again our title parameter is passed in, so it will read ‘Welcome to Express’ – and that is the content displayed in your browser. If you like, make some changes to the routes/index.js and views/index.jade files to reflect your own content.

Up till now we have pretty much covered what you are likely to find in initial stages of most Node.js tutorials, the next steps will move us in the direction of our OL3, PostgreSQL mapping application.

 

PostgreSQL/PostGIS

I am not going to cover the details of the PostgreSQL and PostGIS install so lets jump directly to adding the data. Create a table to store your spatial data, I am going to call mine ‘geo_layers’, you can call it what you like. This table will store our point line and polygon layers. For this tutorial the table will have only three fields: id, name and geometry.

There are multiple ways to get spatial data into PostgreSQL, again I am not going into details here on how to do this. I have used the same Manhattan point line and polygon sample layers I did in my previous post and have provided the insert statements below to save you some time.

Now that we have spatial data in PostgreSQL/PostGIS lets get it wired up for us to use in our application.

 

The Node.js PostgreSQL connection

To setup our PostgreSQL connection, edit the routes/index.js file to include the following.

Getting the connection string sorted is probably one of the biggest challenges in this tutorial, I have included some useful resources below that might help you troubleshoot if you run into issues along the way

Continuing with our edits to the routes/index.js file, we now need to set up a router item that handles a request for our layers and sends these to our map.jade template where they will be available for us to use as we wish. This router item does not handle the request for GeoJSON, it simply get a list of the layer names from our geo_layers table.

Looking at the router item above: we start by defining the ‘/map’ url extension as our handler, the two lines that follow deal with the connection using our conString variable we setup earlier. Next comes our query, we want to return all the layer names from the geo_layers table. The final section should look familiar to you from what we went over earlier with the index.jade setup. We are declaring that we wish to use a map.jade template (the ‘.jade’ extension is not required in the handler). The next few lines setup the variables we will be sending into the map.jade template: the PostgreSQL query result containing our list of layers, a title, which was ‘Express’ in the example we looked at above and finally a lat and lng which we will use to center our map. Of course we could get the last three variables from PostgreSQL if we wanted to.

We can setup another router item that makes our layer list available to us – wait haven’t we just done this? Yes, if you recall, in the router item above we made a list of layers and some other variables available to our map.jade template but what if we need to access this list from elsewhere or in the JavaScript – This router item will allow us to get immediate access to our layer list using http://localhost:3000/layers. Very much the same as the handler above except we are not sending our variables to the map.jade template.

The final handler gets us our GeoJSON. This handler is very similar to the handler above except we want to base the response on an argument we append to the end of the URL which will be our layer name which happens to be the geometry type too. This handler will return the GeoJSON associated with the layer name.

Before I go on, a slight detour – the SQL in this handler requires PostgreSQL 9.2 or above and makes use of the built in JSON functions available with this release to compile the GeoJSON with properties, in our case just the name and id. I will use the properties to display the layer name when a feature is clicked on in the map – this really has nothing to do with Node.js and is all in the client side JavaScript but I wanted to demonstrate using Node.js to get a full set of  GeoJSON properties. If your version of PostgreSQL is less than 9.2 – not to worry, simply use only the ST_AsGeoJson() function, this will get you the GeoJSON content without properties – later in the JavaScript simply exclude the layer on click function or adapt the function to return something other than a property.

Back to the index.js file – much the same as the previous router item but slightly more convoluted SQL required to build up the GeoJSON content, properties included. The following link is a great resource and provides a lot more detail on the format and alternative options.

We use the names parameter [req.params.name] and plug it into the query variable ‘SELECT gid, lname FROM geo_layers WHERE lname = $1’

Lastly, I have included an HTTP status 404: NotFound message if we were to receive a layer parameter that does not exist in our database, we will see how this works shortly.

Execute the following command.

Go to http://localhost:3000/pg –  you should get a 404, that’s because, as mentioned above the http://localhost:3000/pg takes a layer name argument – try again with the following: http://localhost:3000/pg/points and you should get your points GeoJSON returned. Hopefully this is starting to make sense. Lets create our map.jade file so we can display our PostgreSQL data.

 

Show me the map

If you are still with me at this point – well done, lets press on and create a map.jade file in the views folder, if you like you can copy the index.jade file and rename it. Here’s where we will get our hands dirty with some Jade templating and build our map content.

The views/map.jade file is going to have a similar structure to the the Mapbox Toggling layers example with the exception of the Jade Template iteration and calls to the handler URLs to return our JSON and GeoJSON data we will also be using Openlayers3 rather than Leaflet. We could put everything in one file like the example but let’s rather get our style sheet and JavaScript references setup in the layout.jade file.

Take a look at Jade Template engine to learn more about how to setup your content and the format you need to use, notice the indentation. In the views/layout.jade file you will see I have setup the JavaScript and style sheet references for our application. There is not much point in doing this for our example as we only have a one page application but you might use the layout.jade file to setup your bootstrap page template – assuming other applications you build may have multiple pages – this will get you a consistent layout throughout your site.

Let’s edit our views/map.jade file, I will try to break down the content into logical parts, the first is the HTML content.

Again, notice the indentation, this is important when working with Jade. If your indentation is not correct, depending on the location of the indentation you might receive the following error: unexpected token “indent”, or simply return a blank screen. The first two lines are where the file will inherit the content from the layout.jade file. Next we add an HTML map div element – so the Jade Template Engine translates #map to <div id=’map’></div>. We then add HTML div elements for legend title and legend. Notice the line beginning ‘each…’ this is where the Jade magic kicks in – if you recall in the routes/index.js file, we are passing in a JSON object called ‘layers’ – the Jade template now has access to this ‘layers’ variable, so that enables us to loop through the layer names available to us based on the lname field of our PostgreSQL database table and output a checkbox and label for each item.

I have added some style content to the /public/stylesheets/style.css file needed to position the map and legend content correctly so be sure to get that setup before running your final output. The full source code is available on GitHub.

In the second part of our views/map.jade file we setup the OpenLayers3 content. This is pretty straight forward, we define an osm variable as our base map layer, the map variable and the map arguments. Notice in the view section we have added lat and lng arguments, these are being passed in from the routes/index.js file.

Next we handle the layer checkbox, this function simply takes the layer name as an argument and sends it to the addLayer() function, which we will look at next, then displays it if it is not already displayed. A nice feature about the Node.js approach is that because we are getting the list of layers from the same central database location, the list will be the same one used to generate both HTML and GeoJSON content, you are unlikely to run into the situation where you have hard coded an HTML checkbox with no matching spatial data.

One of the features I like about OpenLayers is that you only need to load a layer once then simply toggle visibility should you wish to add or remove that layer from the view rather than loading it in from source each time. In the addLayer() function we check an array called addLayer.cache, if the layer has already been added we simply return the layer to the request made from the toggle_layer() function. If the layer is not already in the addLayer.cache array we request the layer using the handle setup to respond to http://localhost:3000/pg/layername and add that layer to the addLayer.cache array and the map. I have set up an addStyle() function which we will get to shortly but this simply assigns a style to each layer.

It is important to note that OpenLayers handles the display of layers on a first come first server basis – so if the first layer you add (request this layer for the first time) is a point layer, you run the risk of subsequent layers being displayed on top of the points layer and possibly covering it. There are many ways to handle this conflict but I’m not going to cover these in this tutorial, the important thing to note is that using the addLayer() function below, the very first layer you display will be the first layer added to the map and be displayed at the bottom of the layer stack, subsequent requests for the layer will not alter its position they will simply toggle visibility – you can try this out shortly. You might wish to build a function that adds your layers to the map in a logical order then a separate function to handle their visibility.

The addStyle() function assigns an OpenLayers style to each layer based on the layer name, this could be a dynamic request to the database but in this tutorial I have simply set up a hard coded solution.

Finally, I have include an on map click event and a function that simply displays an alert with the layer name as content – the idea here is just to show a basic example of how to access the GeoJSON content from you database but, really, at this stage it is all client side JavaScript.

Note that if you are using a version of PostgreSQL less than 9.2 as mentioned earlier you will not have any property information available so either ignore these two items or adjust accordingly.

That’s it, you are now ready to run your application, if you are not already there, cd to your project directory and run.

Head over to your browser and display your OpenLayers 3 Node.js application http://localhost:3000/map.

 

Summary

Depending on what you have in mind for your application build, you can construct request handlers in the routes/index.js file to handle data requests and page displays. If you recall from our example, we setup a /pg handler that takes a layer name as an argument to return GeoJSON for a particular layer. We also setup a /layers handler that returned the JSON for all our layer names, of course, we could adjust this to include other content such as alias names or style information. By combining URL handlers and the power of the Jade template engine we have all the JSON we need at our disposal to use in JavaScript and the ability to generate considerable amounts of HTML on the fly.

The full source code is available on GitHub

 

 

  4 comments for “An OL3 map with Node.js and PostGIS

  1. Hicham
    July 12, 2016 at 11:16 am

    Great Tutorial, the jade part is the most difficult, I am used to work only with HTML and CSS, all this template thing is getting me confused. I believe that this article is old and that the links to the OL3 css and js are not available anymore I used v3.17.1 to test it. also it doesn’t recognize ol.source.Geojson() and I had to change it to ol.source.Vector()

    Thanks for the tutorial 😀

    • denelius
      July 16, 2016 at 1:10 pm

      Hi Hicham – thanks for the comment – I have updated the mongo leaflet example but, you are correct, I need to update the ol3 example – I hope to do this soon

    • denelius
      February 27, 2017 at 11:12 pm

      ol3 post updated

  2. gy
    July 21, 2017 at 8:40 am

    Thank you for your great tutorial! It helped me a lot!

    With the newest version of pg (for present 7.0.0), some methodes are deprecated (for example query.on). The codes break at the part of query. I made some modifications in the “index.js”. I posted it here, in case someone is interested:

    index.js:

    var express = require(‘express’);
    var router = express.Router();

    /* GET home page. */
    router.get(‘/’, function(req, res, next) {
    res.render(‘index’, { title: ‘PostgresMap’ });
    });

    //psql package imoport
    var pg = require(“pg”);

    var config = {
    host: ‘yourhost’, // server name or IP address;
    hostaddr:’your.ip’,
    port: 5432,
    database: ‘yourDB’,
    user: ‘yourUser’,
    password: ‘yourPW’,
    max: 10, // max number of clients in the pool
    idleTimeoutMillis: 30000 // how long a client is allowed to remain idle before being closed
    }

    router.get(‘/map’, function(req, res) {
    var pool = new pg.Pool(config);
    pool.connect(function(err, client, done) {
    if(err){
    console.log(“not able to get connection “+ err);
    res.status(400).send(err);
    }

    client.query(‘SELECT lname FROM geo_layers’, function(err1,result) {
    done(); // closing the connection;
    if(err1){
    console.log(err1);
    res.status(400).send(err1);
    }

    res.render(‘map’, {
    “layers”: (result.rows),
    title: ‘pgMap’,
    lat: 40.7795213,
    lng: -73.9641241
    })
    });
    })

    pool.end();
    });

    /* GET pg json data. */
    router.get(‘/pg/:name’, function (req, res)
    {
    if (req.params.name)
    {
    var pool = new pg.Pool(config);
    pool.connect(function(err, client, done)
    {
    if(err)
    {
    console.log(“not able to get connection “+ err);
    res.status(400).send(err);
    }

    var qJson=”SELECT row_to_json(fc) ”
    + “FROM ( SELECT ‘FeatureCollection’ As type, array_to_json(array_agg(f)) As features ”
    + “FROM (SELECT ‘Feature’ As type ”
    + “, ST_AsGeoJSON(lg.the_geom)::json As geometry ”
    + “, row_to_json(lp) As properties ”
    + “FROM geo_layers As lg ”
    + “INNER JOIN (SELECT gid, lname FROM geo_layers where lname = ‘” + req.params.name +”‘) As lp ”
    + “ON lg.gid = lp.gid ) As f ) As fc”;

    client.query(qJson, function(err1,result)
    {
    done(); // closing the connection;
    if(err1){
    console.log(err1);
    res.status(400).send(err1);
    }

    res.send(result.rows[0].row_to_json);
    res.end();
    })
    })
    pool.end();
    }
    else
    {
    res.status(404) // HTTP status 404: NotFound
    .send(‘Not found’);
    }
    });

    module.exports = router;

Leave a Reply

Your email address will not be published. Required fields are marked *