PostGIS raw query returns empty set on ST_AsMVT

I am trying to generate dynamic mvt tiles using django. I used the sql query given in the documentation to generate the tiles. I changed the z,x,y as per my requirements.

WITH mvtgeom AS
(
  SELECT ST_AsMVTGeom(feat_polygon.geom, ST_TileEnvelope(19, 369963, 215620)) AS geom, u_id
  FROM feat_polygon
  WHERE ST_Intersects(feat_polygon.geom, ST_TileEnvelope(19, 369963, 215620))
)
SELECT ST_AsMVT(mvtgeom.*)
FROM mvtgeom;

This gives empty result. But if i only run the following query it returns the results:

  SELECT ST_AsMVTGeom(feat_polygon.geom, ST_TileEnvelope(19, 369963, 215620)) AS geom, u_id
  FROM feat_polygon

And if i try to run the following query it again returns the empty set.

WITH mvtgeom AS
(
  SELECT ST_AsMVTGeom(feat_polygon.geom, ST_TileEnvelope(19, 369963, 215620)) AS geom, u_id
  FROM feat_polygon
)
SELECT ST_AsMVT(mvtgeom.*)
FROM mvtgeom;

Solution

ST_AsMVTGeom assumes the input geometries to be projected in EPSG:3857; while it will happily return a scaled geometry for any coordinates within the (default or custom) bounds, they will not fit into the ST_TileEnvelope passed to ST_AsMVT.

Likewise, ST_TileEnvelope returns a Polygon projected in EPSG:3857; running an ST_Intersects check against geometries with any other CRS reference will fail.

Instead, wrap your geometries in ST_Transform:

WITH
  mvt_geom AS (
    SELECT
        u_id,
        ST_AsMVTGeom(
          ST_Transform(feat_polygon.geom, 3857),
          ST_TileEnvelope(19, 369963, 215620)
        ) AS geom
    FROM
        feat_polygon
    WHERE
        ST_Intersects(
          ST_Transform(feat_polygon.geom, 3857),
          ST_TileEnvelope(19, 369963, 215620)
        )
  )
SELECT
    ST_AsMVT(mvt_geom.*, feature_id_name => 'u_id')
FROM
    mvt_geom
;

Note that you may want to either create a functional index on the projected geometries, i.e.

CREATE INDEX ON feat_polygon USING GIST ((ST_Transform(geom, 3857));

or reproject and REINDEX the table, i.e.

ALTER TABLE feat_polygon
  ALTER COLUMN geom TYPE GEOMETRY(POLYGON, 3857)
    USING ST_Transform(geom, 3857)
;

to utilize the index in your filter.

Pedantic note: you may also want to move the main CTE into a subquery, while outsourcing the ST_TileEnvelope into a CTE and JOIN with feat_polygon to avoid multiple calls.

ST_AsMVT returns a BYTEA value that holds a protobuf (pbf) encoded Vector Tile: you should be able to serialize a binary protobuf response body using

content_type="application/octet-stream"

and pass it to whatever mapping framework that can decode the pbf according to the Vector Tile specs.