2

I am trying to migrate from serving static MVT to generated on the backend by PostGIS with ST_AsMVT(). Or find a way of minimizing the time of building MVT from the database.

I have a dataset of political, historical maps, and I am serving them as prerendered Mapbox Vector Tiles. Dataset itself is not massive. It's 2300 entities for 200 years ~ 1Gb of data. As a result, distant zoom tile (/1/0/0) has ~ 600 geometries. They are filtered on client side with filter option by year in MVT style. We want to introduce an ability for the end-user to upload their maps.

Pipeline to produce an MVT tileset that we are using right now:

  1. Loop over geometry in the database and dump it in a file as a GeoJSON feature (feature collection does not fit into memory)
  2. Process output with tippecanoe and zoom up to 10
  3. Serve file.mbtiles with mbtiles-server

It took around 30 minutes and full CPU load for the whole cycle.

As one way of optimization here - we could rebuild only the part that was changed and update tileset with tile-join


I've tried to serve MVT directly from PostGIS with ST_AsMVT(), Geometry is in SRID:4326 queries were executed with similar simplification level and against the same bounding box /1/0/0

Full queries and explanation are here

  • The straight-forward approach of MVT generation took 130-177 seconds
  • Simplification after transforming to SRID:3857 (WebMercator) took 30 seconds
  • Simplification before processing to SRID:3857 took 3 seconds
-- three seconds to execute
SELECT ST_AsMVT(a, 'stv')
FROM (
  SELECT
      api_spacetimevolume.id
      , api_spacetimevolume.start_date::INTEGER
      , api_spacetimevolume.end_date::INTEGER
      , api_spacetimevolume.references
      , ST_AsMVTGeom(
          ST_SnapToGrid(
            ST_Transform(
              ST_Simplify(
                  api_spacetimevolume.territory
                , 0.44 -- Dynamic based on zoom level
              )
              , 3857
            )
            , 1
          )
          , TileBBox(1, 0, 0)
      ) as territory
      , api_spacetimevolume.entity_id
      , api_territorialentity.wikidata_id
      , api_territorialentity.color
      , api_territorialentity.admin_level
  FROM api_spacetimevolume
  JOIN api_territorialentity
  ON api_spacetimevolume.entity_id = api_territorialentity.id
  WHERE territory && TileBBox(1, 0, 0, 4326)
) as a
;

Even with 3 seconds per tile, it is obvious that it should be cached on disk or in the database. Temporary caching (nginx or redis) will not solve the problem completely.

In this article Michal Zimmerman is generating tiles in advance by PostGis and storing them on disk.


Well, the questions

  1. What do you recommend to speed up the tile delivery?
  2. What is the best way to keep the tile generation flexible?
  • Have you tried using GeoServer. There is an mbtiles plugin and GeoWebcache will do this out of the box -- ie, pre-generate the tiles, if you want, but also provide a built-in caching mechanism. The source would still be Postgres/Postgis, but this is likely to be a lot quicker than using ST_AsMVT on the fly. – John Powell Jul 2 at 15:19
  • Thanks, I'll have a look at GeoServer, we are using mbtiles-server at this moment. It was a convenient way when we picked it for almost a year ago. – MiklerGM Jul 3 at 9:31
  • Is mbtiles-server slow then? Geoserver won't necessarily be any quicker, it is just that it works out of the box and gives you a lot of other options to. – John Powell Jul 4 at 8:20
  • mbtiles-server is fast, but we need to process tiles before they will be served. So we are extracting everything from postgis to geojson file, then processing geojson with tippecanoe. – MiklerGM Jul 4 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.