Adding GeoJSON and MVT to the PostGIS HTTP API

img

Whenever four devices near me begin chirping due to Twitter interactions, I immediately wonder what I did wrong. In this case I was mentioned in the same breath as the NYC Planning folks, who do really impressive work. I’m counting that as a win.

They have a markdown page on Github discussing a GeoJSON API they are tinkering with, and although there are architectural similarities to my trusty old dirt-simple-postgis-http-api, they have some really good ideas all their own.

So I stole them.

The Dirt Simple API can return GeoJSON features via ST_AsGeoJSON in the query, but assembling that with attributes into a complete GeoJSON feature class is a pain in the ass. I tinkered with doing this via some unwieldy SQL, but I didn’t like the performance. The NYC Planning folks turned me on to dbgeo, which will take a Postgres query result and handle the ick of translating it to GeoJSON. As everything at this point is JavaScript, it makes sense that it would be a much more performant operation, and it is.

A little code and it’s off and running.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
let db = pgp(config.db.postgis);
db
    .query(formatSQL(request))
    .then(function(data) {
        dbgeo.parse(data, {
            outputFormat: 'geojson',
            precision: 6
        }, function(error, result) {
            reply(result);
        });
    })
    .catch(function(err) {
        reply({
            'error': 'error running query',
            'error_details': err
        });
    });

Another thing I’ve wanted to do is serve Mapbox Vector Tiles (as zipped protobufs) created on the fly from PostGIS via the Dirt Simple API. I made a stand-alone test project for that, didn’t like the performance, recreated it entirely (moving from Mapnik vector tile creation to geojson-vt), and still didn’t like the performance. It was the GeoJSON bit that killed it, and again dbgeo comes to the rescue.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
let db = pgp(config.db.postgis);
db
    .query(formatSQL(request))
    .then(function(data) {
        dbgeo.parse(data, {
            outputFormat: 'geojson',
            precision: 6
        }, function(error, result) {
            var tileindex = geojsonVt(result);
            var tile = tileindex.getTile(request.params.z, request.params.x, request.params.y);
            // pass in an object mapping layername -> tile object
            var buff = vtpbf.fromGeojsonVt({[request.params.table]: tile});
            zlib.gzip(buff, function(err, pbf) {
                reply(pbf)
                    .header('Content-Type', 'application/x-protobuf')
                    .header('Content-Encoding', 'gzip');
            });
        });
    })
    .catch(function(err) {
        reply({
            'error': 'error running query',
            'error_details': err
        });
    });

Let’s look at performance.

From our tax parcels (334,400 polygons), I’m going to grab Z/X/Y 14/4512/6475, which yields 2,747 polygons with 31,291 vertices (this ain’t Kansas). That’s a good hunk of polygons.

I’ll measure performance via curl locally to drop network considerations. Something like this, taken 5 times and averaged:

1
2
curl -o /dev/null -X GET -w %{time_connect}:%{time_starttransfer}:%{time_total} \
'http://localhost:8123/mvt/v1/tax_parcels/14/4512/6475?columns=pid'

GeoJSON comes in averaging an impressive 175ms. That’s smoking fast for that much data.

MVT has to do everything the GeoJSON route does, plus convert to MVT, then convert to protobuf, then zip. I was expecting that to suck. It didn’t. The payload being smaller more than made up for it. Average time: 158ms.

The key to serving geometry is to eject as much precision as fast as you can. I do it when converting the results to GeoJSON via dbgeo, but I also do it to the WKB coming from Postgres.

1
.field(`ST_Simplify(ST_Transform(${request.query.geom_column}, 4326), 0.000001) as geom`)

Edit: had a funky simplification value in there.

That makes a big difference. Average request time goes up 37% without early simplification. Six decimal places at our latitude is ~0.3 feet, which is more precise than our data dreams of being.

The dirt-simple-postgis-http-api project has been updated with these two new services, as well as general dependency upgrades and tweaks. Enjoy, and a big thanks to NYC Planning for the ideas and inspiration.