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
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:
SELECT ST_AsMVT(q, 'buildings', 4096, 'geom') FROM
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.
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:
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
Old geojson-vt: 156ms, 27.9KB
New ST_AsMVT: 143ms, 29.2KB
Zoom Level 13
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:
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.
ST_asMVTworking isn’t necessarily as simple as installing PostGIS 2.4. On my Ubuntu 14.04 production server,
libprotobufisn’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.