如何将查询合并为单个 CTE 查询 [英] how to combine a query into a single CTE query
问题描述
在下面的代码一中,有一个查询可以找到经度和纬度方面的交点.在代码二中,它是显示特定信息,如type
、properties
和geometry
等.我想要实现的是让代码二中的主要查询包含有关交点经度和纬度"的信息;以及该地区.换句话说,鉴于代码二中的 geom
,我想整合代码一到代码二有交点和面积的信息
in code one below, there a query that finds the points of intersection inn terms of longitude and latitude. and in code two, it is to show specific info like type
, properties
and geometry
,..etc.
what i want to achieve is to have the main query in code two contains information about the point of intersection "longitude and latitude" and the area as well. in other words, given the geom
in code two, i would like to integrate
code one into code two to have information about the points of intersection and area
请帮助我找出如何将代码一集成到代码二中.
please help me to find out how can code one be integrated into code two.
code1_to 求交点坐标:
query ="""SELECT ST_X(ST_Transform(point,4326)) as lon, ST_Y(ST_Transform(point,4326)) as lat, ST_AsText(ST_Transform(point,4326)),ST_Area(
ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(ST_X(point),ST_Y(point),ST_X(point)+{width}, ST_Y(point)+{height}),25832),
ST_Transform(
ST_SetSRID(ST_GeomFromGeoJSON(
'{geometry}'),4326)
,25832)))
FROM {table}
WHERE
st_intersects(
ST_Transform(
ST_SetSRID(ST_GeomFromGeoJSON(
'{geometry}'),4326)
,25832),
st_setsrid(ST_MakeEnvelope(st_x(point),st_y(point),st_x(point)+{width},st_y(point)+{height}),25832))""".format(table=config['PostgreDB']['table_name_test'], width=config['Grid']['cell_width'], height=config['Grid']['cell_height'],geometry=geometry)
code2:
query = """ WITH data AS (
SELECT '{featuresCollection}'::json AS featuresCollection
)
SELECT gid,geom,type::text,properties::text,
array_to_string(array_agg(x_4326||' '||y_4326 ORDER BY gid),',') AS g4326,
array_to_string(array_agg(x_25832||' '||y_25832 ORDER BY gid),',') AS g25832
FROM (
SELECT
ROW_NUMBER() OVER () AS gid,
ST_AsText(ST_GeomFromGeoJSON(feature->>'geometry')) AS geom,
feature->>'type' AS type,
feature->>'properties' AS properties,
ST_X((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) x_4326,
ST_Y((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) y_4326,
ST_X((ST_DumpPoints((ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832)))).geom) x_25832,
ST_Y((ST_DumpPoints((ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832)))).geom) y_25832
FROM (SELECT json_array_elements(featuresCollection->'features') AS feature FROM data) AS f) j
GROUP BY gid,type::text,properties::text,geom
ORDER BY gid;""".format(featuresCollection=featuresCollection)
样本数据:
[(3338490, 5668960, Decimal('1.02'), Decimal('52.08'), '0101000020E864000077D23C26C5A81441A9BAEC5A4F9E5541'), (3338490, 5668950, Decimal('0.77'), Decimal('52.13'), '0101000020E864000047A52726C5A81441D4552EDB4C9E5541'), (3338490, 5668940, Decimal('0.36'), Decimal('52.19'), '0101000020E864000005781226C5A8144109F16F5B4A9E5541')]
表格中部分数据的图片:
featureCollection 中的数据
推荐答案
只需将查询放在 FROM
子句中的 code 2
中,然后将其与 连接code 1
或者只是在 WHERE
子句中匹配它们,例如
Just place the query in code 2
in the FROM
clause and either join it with code 1
or just match them in the WHERE
clause, e.g.
query = """
SELECT j.*,
ST_X(ST_Transform(point,4326)) As lonOfIntersection,
ST_Y(ST_Transform(point,4326)) AS latOfIntersection,
ST_AsText(ST_Transform(point,4326)) pointOfIntersectionEPSG4326,
ST_AsText(ST_Transform(point,25832)) pointOfIntersectionEPSG25832,
ST_Area(
ST_Intersection(
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width},
ST_Y(point)+{height}),
25832),j.geometry
)
) As areaOfCoverage
FROM {table}
JOIN (
WITH data AS (
SELECT '{featuresCollection}'::json AS featuresCollection
)
SELECT DISTINCT
geometryID,geomType,geomProperties,
array_to_string(array_agg(x_4326||' '||y_4326 ORDER BY geometryID),',') AS polygonsCoordinatesInEPSG4326,
array_to_string(array_agg(x_25832||' '||y_25832 ORDER BY geometryID),',') AS polygonsCoordinatesInEPSG258,
geometry
FROM (
SELECT
ROW_NUMBER() OVER () AS geometryID,
feature->>'type' AS geomType,
feature->>'properties' AS geomProperties,
ST_X((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) AS x_4326,
ST_Y((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) AS y_4326,
ST_X((ST_DumpPoints((ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832)))).geom) AS x_25832,
ST_Y((ST_DumpPoints((ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832)))).geom) AS y_25832,
ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(feature->>'geometry'),4326),25832) AS geometry
FROM (SELECT json_array_elements(featuresCollection->'features') AS feature
FROM data) AS f) j
GROUP BY geometryID,geometry,geomType,geomProperties) j ON
ST_Intersects(j.geometry,
ST_SetSRID(
ST_MakeEnvelope(
ST_X(point),
ST_Y(point),
ST_X(point)+{width},
ST_Y(point)+{height}),25832));
""".format(table=config['PostgreDB']['table_name_test'], width=config['Grid']['cell_width'], height=config['Grid']['cell_height'],featuresCollection=featuresCollection)
这篇关于如何将查询合并为单个 CTE 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!