[postgis-users] PostGIS mapbox vector tiles and custom tile grid

Raúl Marín Rodríguez rmrodriguez at carto.com
Tue Apr 2 00:22:14 PDT 2019

> How do you think I can optimize it?

It's highly likely that you want to inline the CTE and, you can remove
one of the intersections (&& or ST_Intersects); ST_Intersects already
calls && implicitly so you could choose to only use that, but I'd recommend
using && instead; then you can either discard NULLs or pass it to ST_AsMVT
and it will get discarded automatically.

Also, AFAIK ST_FORCE2D shouldn't be necessary so I'd only call it in you
see something odd.

Another thing to discard is ``ORDER BY`. Since order isn't guaranteed
output, if you are going to pass this result to it I'd remove the `ORDER BY`.

Finally, make sure you don't need the extra buffer (4th parameter of
It's useful in some renderers to help stitching the polygons back
together (but not mandatory).

The result query could look something like this:
        , ST_ASMVTGEOM(
                ST_MAKEENVELOPE(:min_x, :min_y, :max_x, :max_y),
                :tile_size, 0, true) geometry
                , original_feature_id
                , layer_id
                , geometry
        FROM {0}.{1}
) _subquery
WHERE layer_id = :layer_id
        AND geometry && ST_MAKEENVELOPE(:min_x, :min_y, :max_x, :max_y)
) q

Raúl Marín Rodríguez

More information about the postgis-users mailing list