landsat_centroid_poly.sql

landsat_centroid_poly.sql

CREATE OR REPLACE FUNCTION landsat(
    -- mandatory parameters
    xmin float,
    ymin float,
    xmax float,
    ymax float,
    epsg integer
)
RETURNS bytea AS $$
WITH bounds AS (
    SELECT ST_Transform(ST_Segmentize(
        ST_MakeEnvelope(xmin, ymin, xmax, ymax, epsg), xmax - xmin
        ), 4326) as bounds
), t AS (
    SELECT
        ST_AsMVTGeom(st_transform(geom, epsg), bounds) as polymvt,
        ST_AsMVTGeom(st_centroid(st_transform(geom, epsg), bounds)) as pointmvt,
        path,
        "row"
        FROM public.landsat_wrs, bounds
        WHERE ST_Intersects(geom, bounds)
)
SELECT
    ST_AsMVT(ROW(t.polymvt, path, "row"), 'polygon')
    ||
    ST_AsMVT(ROW(t.pointmvt), 'point')
FROM t;
$$
LANGUAGE SQL
IMMUTABLE -- Same inputs always give same outputs
STRICT -- Null input gets null output
PARALLEL SAFE;