Faffing about with ST_AsMVT

After PostGIS 2.4 arrived with built-in MVT rendering, I wanted to move my MVT route in the Dirt Simple PostGIS HTTP API from geojson-vt/vt-pbf to ST_asMVT() directly in the database. And…I did.

The SQL required to produce MVT in the database is a little convoluted. Mine ended up looking like this:

ST_asMVT
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT ST_AsMVT(q, 'buildings', 4096, 'geom') FROM
(SELECT
      ST_AsMVTGeom(
      ST_Transform(geom, 3857),
      ST_MakeEnvelope(-9001224.450862356,4192418.1273853467,-8998778.46595723,4194864.1122   90474, 3857),
      4096,
      0,
      false
    ) geom
    FROM buildings
    WHERE (geom &&
      ST_Transform(ST_MakeEnvelope(-9001224.450862356,4192418.1273853467,-8998778.46595723,4194864.112290474, 3857), find_srid('', 'buildings', 'geom')))
) AS q

Hat tip to Rafa de la Torre and Michal Zimmermann for saving me a lot of headaches. In English, we’re getting the rows from the layer buildings whose geometry intersects with a XYZ tile bounding box, projecting that to MVT coordinate space, and then turning the recordset into a single vector tile which can be shipped out. There seems to have been a change in the parameter order of ST_AsMVT between testing and final release, with the examples I’ve seen having the rows (q in this case) go last. It should go first. I’m not bitter. Really.

The old method fetches data from Postgres as WKB and converts it to MVT in the client. It’s a striaght-forward SQL query.

geojson-vt
1
2
3
4
5
6
SELECT
  ST_Simplify(ST_Transform(geom, 4326), 0.000001) as geom
  FROM buildings
  WHERE (geom &&
  ST_Transform(ST_MakeEnvelope(-80.859375,35.209721645221386,-80.83740234375,35.22767235493586 , 4326),
  find_srid('', 'buildings', 'geom')))

Now for a quick performance benchmark. I’m using my Ryzen 1600 6 core 12 thread 32GB RAM monster with a SSD for both PostGIS (using this docker image) and Dirt. The test query looks like this:

1
time wget -S --header="accept-encoding: gzip" --header="cache-control: no-cache" -O /dev/null -p http://127.0.0.1:8123/mvt/v1/buildings/13/2255/3238\?limit\=10000

Zoom Level 14

Polygons: 1,100
Old geojson-vt: 156ms, 27.9KB
New ST_AsMVT: 143ms, 29.2KB

Zoom Level 13

Polygons: 7,852
Old geojson-vt: 411ms, 87.4KB
New ST_asMVT: 560ms, 113.4KB

The time is the average of five runs. Now for some irresponsible speculation.

In terms of speed, creating MVT’s in the database might be slightly slower than using the latest geojson-vt. v3.0 takes 2x less memory and is 20-100% faster than previous versions - it’s stupid fast. As there is an efficiency gain in terms of data movement using ST_asMVT, it’s possible that efficiency gain outweighs the MVT creation performance until you get into larger amounts of data.

The file size difference can be accounted for through differences in how the geometry is simplified. The old route simplifies the geometry no fewer than three times - once on the SQL query (reduces data transport size), by setting a GeoJSON precision (reduces the complexity of the data geojson-vt has to deal with), and then geojson-vt does it’s own thing. Despite the file size difference, it looks identical to me:

New Thing
Old Thing

The other test results I’ve seen (primary comparing Mapnik encoding) show a slight performance improvement when creating MVT’s on the database side. My results seem to show two trend lines that cross each other when you pass a certain amount of features. I’m also using polys while the other benchmarks I’ve seen use points. YMMV.

Dirt is now using ST_asMVT for the MVT route, but I have created an optional_routes folder with the old route (and for future experiments) should you want to use it. And you might:

  • You may want to CPU slam the client rather than the database.
  • The speeds are comparable and the file size is smaller.
  • You might not be on PostGIS 2.4 yet.
  • Getting ST_asMVT working isn’t necessarily as simple as installing PostGIS 2.4. On my Ubuntu 14.04 production server, ST_asMVT tells me libprotobuf isn’t up to snuff, takes its ball and goes home. I sense compiling in my future.

If you see that I’ve made a monumental screw up in my testing technique or SQL, please let me know on Twitter. Preferably in all caps with a GIF of a cat running into a screen door.