1

I'm using PostGIS to serve Mapbox Vector Tiles on my NodeJS server. I'd like to speed up the process a bit by storing the x and y envelope for every zoom level. That way when a request is made it doesn't need to perform that extra step every time. Even with indexes, some of my queries are taking up to 6 seconds.

For simplicity, we can assume all of my stored geometries are Points.

Here is my current sql query:

with
mvtgeom as (
    select
        st_asmvtgeom(t.geometry, st_tileenvelope($1, $2, $3)::box2d) as geom,
        id,
    from my_geometries t
    where st_intersects(t.geometry, st_tileenvelope($1, $2, $3))
)
select st_asmvt(mvtgeom.*, 'my-layer', 4096) from mvtgeom

Instead I'd like to be able to say:

with
mvtgeom as (
    select
        st_asmvtgeom(t.geometry, st_tileenvelope($1, $2, $3)::box2d) as geom,
        id,
    from my_geometries t
    where -- zoom = $1, x = $2, and y = $3
)
select st_asmvt(mvtgeom.*, 'my-layer', 4096) from mvtgeom

Or maybe I'm not going about this right.

All of my geometries will be within the boundaries of the United States and its territories (Puerto Rico, Guam, Virgin Islands, etc.).

Is there a way to optimize this query to not even try and do the calculations for tiles not within these boundaries?

1
  • But x and y per geom changes for each z - so you'd need max z * 2 columns (z0_x - z<max>_x and z0_y - z<max>_y) with indexes each for this to make sense, and execute dynamic SQL to adress the correct z columns by name.
    – geozelot
    Feb 18, 2023 at 8:31

1 Answer 1

3

If your request is taking 6 seconds, maybe there is a deeper problem with your request that you should solve, I don't think adding a field to filter on will change much, intersecting with an envelop should be pretty quick as a filter, more time is probably done retrieving the result and transforming it in tile. Here is some ideas:

  • First be sure that your table is analyzed, and check that the request correctly use your index with EXPLAIN ANALYZE. That way you can check if it's really the filter that takes time.
  • If you still have request that takes multiple seconds, maybe it's because you have a lot of geometries, and that they are detailled. In high zoom level, you don't need lot of precision so you should either:
    • Filter geometries in high zoom level (for example put a size limit, field that you can precompute in your table).
    • Use simplified version of your geom in high zoom level.
  • To do this kind of things you can make an SQL function that will take x, y and z and return you tile. You can prepare different tables for your different zoom level (that's what we usually with raster data for example) where the small geometries are filtered and the rest is ST_Simplify. Then in your function you dynamically use the table that is the more fit for your Z level (you can make 2 or 3 tables, usually that's enough).
  • You can check an example of a SQL function to serve tile here, and you can also use the great tool there that is pg_tileserv, that can automatically serve through an http API any postgis table as well as SQL functions (that respect some format like the example). It's really easy to use, and even have an web interface to see what is served with a preview.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.