如何将查询合并为单个 CTE 查询 [英] how to combine a query into a single CTE query

查看:86
本文介绍了如何将查询合并为单个 CTE 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在下面的代码一中,有一个查询可以找到经度和纬度方面的交点.在代码二中,它是显示特定信息,如typepropertiesgeometry等.我想要实现的是让代码二中的主要查询包含有关交点经度和纬度"的信息;以及该地区.换句话说,鉴于代码二中的 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆