问题描述
我正在构建一个 Postgres 查询,该查询需要对数组中的每个条目调用聚合函数,然后将所有这些聚合值连接在一起.
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.
假设我有一个带有以下值的 layer
表(如果更容易,我也可以使用数组):
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;
如何编写查询以使用此类表(或数组)来动态连接任意数量的数据层?每层有一个单独的 ST_AsMVT()
调用很重要.
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.
推荐答案
您可以简单地将键值放入 IN
clasue 中,这样您就可以通过单个查询获得所有所需的记录.之后,获取CTE
检索到的记录,并应用ST_AsMVT
函数.
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
替换为 ANY/SOME
,例如:
In case you prefer to work with arrays, replace the IN
with ANY/SOME
, e.g:
WHERE feature_set = ANY ('{river,building}')
如果它仍然没有帮助你,看看plpgsql
可能会很有趣:
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}')
这篇关于如何动态连接多个聚合查询的输出?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!