How to concatenate outputs of multiple aggregate queries dynamically?

Question

I'm building a Postgres query that needs to call an aggregate function on each entry in an array, then concat all of those aggregate values together.

Here's the query for one of the aggregates:

WITH mvtdata AS
(
  SELECT ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, name, description
  FROM feature
  WHERE feature_set = 'river'
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
)
SELECT ST_AsMVT(mvtdata.*, 'river')
FROM mvtdata;

I have a separate table that defines the different data layers. Each data layer needs to call the above query and concatenate its results.

Say I have a layer table with the following values (I could also use an array if easier):

|  name        |
|  'river'     |
|  'building'  |

I want to call the above query once for each entry in the table and concatenate the values. Something like:

WITH riverdata AS
(
  SELECT ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, name, description
  FROM feature
  WHERE feature_set = 'river'
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
),
buildingdata AS
(
  SELECT ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, name, description
  FROM feature
  WHERE feature_set = 'building'
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
)
SELECT (ST_AsMVT(riverdata.*, 'river') || ST_AsMVT(buildingdata.*, 'building')) mvt
FROM riverdata, buildingdata;

How can I write my query to use such a table (or array) to dynamically concatenate an arbitrary number of data layers? It is important to have a separate ST_AsMVT() call per layer.

Answer 1

You could simply put the key values into a IN clasue, so that you can get all desired records a single query. After that, get the records retrieved by the CTE and apply the ST_AsMVT function.

WITH mvtdata AS
(
  SELECT 
    ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, 
    name, description,feature_set
  FROM feature
  WHERE feature_set IN ('river','building')
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
)
SELECT 
  ST_AsMVT(mvtdata.*,feature_set)
FROM mvtdata

In case you prefer to work with arrays, replace the IN with ANY/SOME, e.g:

WHERE feature_set = ANY ('{river,building}')

If it still does not help you, it might be interesting to take a look at plpgsql:

CREATE OR REPLACE FUNCTION public.concat_mvt(arr_feature TEXT[]) 
RETURNS BYTEA LANGUAGE plpgsql 
AS $BODY$
DECLARE 
  i INTEGER;
  res BYTEA DEFAULT '';
  rec BYTEA;
BEGIN 
FOR i IN 1..array_length(arr_feature,1) LOOP
  WITH mvtdata AS (
  SELECT 
    ST_AsMVTGeom(geom, ST_TileEnvelope(12,513,412)) AS geom, 
    name, description,feature_set
  FROM feature
  WHERE feature_set = arr_feature[i]
    AND ST_Intersects(geom, ST_TileEnvelope(12,513,412))
  ) SELECT ST_AsMVT(mvtdata.*,arr_feature[i])
    FROM mvtdata INTO rec;
  res := res || rec;
END LOOP;
RETURN res;
END $BODY$;

.. and call the function like this

SELECT concat_mvt('{river,building}')