[Solved] Fast Dynamic MVT generation

MiklerGM Asks: Fast Dynamic MVT generation
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

Code:
-- 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?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.