[GIS] Fast Dynamic MVT generation

mbtilespostgistilestippecanoevector-tiles

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:

Loop over geometry in the database and dump it in a file as a GeoJSON feature (feature collection does not fit into memory)
Process output with tippecanoe and zoom up to 10
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

What do you recommend to speed up the tile delivery?
What is the best way to keep the tile generation flexible?

Best Answer

I can only hint at question one, but according to this arctile https://carto.com/blog/MVT-mapnik-vs-postgis/

"We may also consider removing the ST_Simplify and ST_SnapToGrid as they aim to facilitate raster rendering and it’s done by the MVT encoding mechanism."

Related Solutions

[GIS] How fast should I expect PostGIS to geocode well-formatted addresses

I've spent a lot of time experimenting with this, I think it's better to post separately since they are from different angle.

This is really a complex topic, see more details in my blog post about the geocoding server setup and the script I used., here is just some brief summaries:

A server with only 2 States data is always faster than a server loaded with all 50 states data.

I verified this with my home pc in different times and two different Amazon AWS server.

My AWS free tier server with 2 states data have only 1G RAM, but it have consistent 43 ~ 59 ms performance for data with 1000 records and 45,000 records.

I used exactly same setup procedure for a 8G RAM AWS server with all states loaded, exactly same script and data, and the performance dropped to 80 ~ 105 ms.

My theory is that when geocoder cannot match address in exactly, it started to broad the search range and ignore some part, like zipcode or city. That's why geocode document boast that it can recolonize address with wrong zip code, although it took 3000 ms.

With only 2 states data loaded, the server will take much less time in fruitless search or a match with very low score, because it can only search in 2 states.

I tried to limit this by setting the restrict_region parameter to the state multipolygons in geocode function, hoping that will avoid the fruitless search since I'm pretty sure most of addresses have correct state. Compare these two versions:

  select geocode('501 Fairmount DR , Annapolis, MD 20137',1); 
  select geocode('501 Fairmount DR , Annapolis, MD 20137', 1, the_geom) from tiger.state where statefp = '24';

The only difference made by the second version is that normally if I run same query immediately again it will much quicker because the related data was cached, but the second version disabled this effect.

So the restrict_region is not working as I wished, maybe it just was used to filter the multiple hit result, not to limit search ranges.

You can tune your postgre conf a little bit.

The usual suspect of install missing indexes, vacuum analyze didn't make any difference for me, because the downloading script have done the necessary maintenance already, unless you messed up with them.

However setting postgre conf according to this post did helped. My full scale server with 50 states was having 320 ms with default configuration for some worse shaped data, it improved to 185 ms with 2G shared_buffer, 5G cache, and went to 100 ms further with most settings tuned according to that post.

This is more relevant to postgis and their settings seemed to be similar.

The batch size of each commit didn't matter much for my case. The geocode documentation used a batch size 3. I experimented values from 1, 3, 5 till 10. I didn't find any significant difference with this. With smaller batch size you make more commits and updates, but I think the real bottle neck is not here. Actually I'm using batch size 1 now. Because there are always some unexpected ill formed address will cause exception, I will set the whole batch with error as ignored and proceed for remaining rows. With batch size 1 I don't need to process the table the second time to geocode the possible good records in the batch marked as ignored.

Of course this depend on how your batch script works. I'll post my script with more details later.

You can try to use normalize address to filter bad address if it suit your usage. I saw somebody mentioned this somewhere, but I was not sure how this works since the normalize function only works in format, it cannot really tell you which address is invalid.

Later I realized that if the address is in obviously bad shape and you want to skip them, this could help. For example I have lots of addresses missing street name or even street names. Normalize all address first will be relatively fast, then you can filter the obvious bad address for you then skip them. However this didn't suite my usage since an address without street number or even street name could still be mapped to the street or city, and that information is still useful for me.

And most of the addresses that cannot be geocoded in my case actually have all the fields, just there is no match in database. You cannot filter these addresses just by normalizing them.

EDIT For more details, see my blog post about the geocoding server setup and the script I used.

EDIT 2 I've finished geocoding 2 million addresses and did lots of clean up on addresses based on geocoding result. With better cleaned input, the next batch job is running much more faster. By clean I mean some addresses are obviously wrong and should be removed, or having unexpected content for geocoder to cause problem on geocoding. My theory is: Removing bad addresses can avoid messing up the cache, which improve the performance on good addresses significantly.

I separated the input based on state to make sure every job can have all the data needed for geocoding cached in RAM. However every bad address in the job make the geocoder to search in more states, which could mess up the cache.

Best Answer

Related Solutions

[GIS] How fast should I expect PostGIS to geocode well-formatted addresses

Related Question