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

[Raúl Marín Rodríguez]

> 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
in ST_ASMVT
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
ST_AsMVTGeom).
It's useful in some renderers to help stitching the polygons back
together (but not mandatory).

The result query could look something like this:
```SELECT
        id
        , ST_ASMVTGEOM(
                geometry,
                ST_MAKEENVELOPE(:min_x, :min_y, :max_x, :max_y),
                :tile_size, 0, true) geometry
FROM
(
        SELECT
                id
                , 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
carto.com