如何将查询合并为单个CTE查询

Question

在下面的代码1中，有一个查询可以根据经度和纬度查找交点。在代码2中，它将显示特定的信息，如type、properties和geometry，等等

我想要实现的是让代码2中的主查询包含关于交点“经度和纬度”以及区域的信息。换句话说，考虑到代码2中的geom，我想将代码1集成到代码2中，以获得关于交点和面积的信息

如何将代码1集成到代码2中

编码1\u以查找交叉点的坐标：

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)        
                

代码2：

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')]

表格中某些数据的图像：

功能集合中的数据：

DB Fiddle

Answer 1

只需将查询放在code 2的FROM子句中，并将其与code 1连接，或者只在WHERE子句中匹配它们，例如

    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)