时间戳

首页 > 解决方案 > Postgresql Postgis SQL 复杂连接（但不一定与 GIS 相关）

sql - Postgresql Postgis SQL 复杂连接（但不一定与 GIS 相关）

问题描述

很抱歉标题缺乏光泽，但很难描述......

假设我有两个表（cad 和 cad_polygon）...

cad 和 cad_polygon 共享同一列，相互关联（cad_pid）...

cad 有这些列：cad_pid、jrsdctn_id

而 cad_polygon 有这些列：cad_pid、ogc_fid、wkb_geometry

现在，我正在工作的以下查询（尝试了半天）从一个明显较小的多边形子集中根据度数从一个长纬度坐标中选择一个地块，它从给定的长纬度坐标中找到它们的距离（以米为单位） -ord，然后仅显示质心在长纬度坐标 500m 范围内的多边形。

SELECT SUBQUERY.cad_pid, SUBQUERY.ogc_fid, SUBQUERY.dist_meters,
    SUBQUERY.wkb_geometry FROM (
    SELECT cad_pid, ogc_fid,
    CAST(ST_Distance_Sphere(
        ST_Centroid(wkb_geometry),
            ST_GeomFromText(
                'POINT(00.0000 -00.0000)',
            900914)
        ) AS numeric
    ) AS dist_meters, wkb_geometry
    FROM cad_polygon
    WHERE ST_DWithin(
        ST_Centroid(wkb_geometry),
        ST_GeomFromText(
            'POINT(00.0000 -00.0000)',
        900914),
    0.01)
    ORDER BY dist_meters ASC
) AS SUBQUERY
WHERE SUBQUERY.dist_meters < 500;

我想添加到这个并使用这个查询吐出的列表，加入我的另一个表（cad），这样我就可以为每个结果行提供附加列“jrsdctn_id”......即：

样本数据为：cad：

cad_pid | jrsdctn_id
0001    | abc123
0002    | def456
0003    | dhk778
0004    | dsk730


cad_polygon:

cad_pid | ogc_fid | wkb_geometry
0001    | ht0101  | 67686687601010000200063D7987FF15ASD1518541DAW
0002    | hz4561  | 435453457601010000200063D7987FF15ASDFW4GF8DE4
0003    | yv0301  | 2626WD687601010000200063D7987FF15ASD1WE851D4D
0004    | vt9701  | D484DW4D8441D8W1C684V63D7987FF15ASD1D7DW4848D

预期成绩：

cad_pid | jtsdctn_id | ogc_fid | dist_meters | wkb_geometry
0002    | def456     | hz4561  | 192.769     | 43545...
0004    | dsk730     | vt9701  | 342.548     | D484D...

如果那里有一些 sql 向导可以提供帮助，那就太好了！

标签： sqlpostgresqljoinpostgispsql

解决方案

有点晚了，当然，公认的答案是绝对正确的JOIN，但这实际上与GIS密切相关，它的意识最终会让你的事情变得更容易：

您似乎正在使用自定义 CRS 或ogr2ogr （或任何 GDAL/OGR 函数）在 PostGIS 的spatial_ref_sys表中找不到匹配的 SRID/投影；但是，任何使用 LonLat 作为球面/球面代数输入的 PostGIS 函数都将始终假定您的坐标位于 EPSG:4326 (WGS84) 中。

如果您的 LonLat 与 WGS84 的不完全匹配，结果将会关闭！

现在，PostGIS 也有geography类型，它再次假设 EPSG:4326 坐标，如果与这些函数一起使用，它将隐式使用米作为单位，如果与默认ST_Distance参数一起使用，将在 WGS84 球体上计算（更精确但略微比 with 慢use_spheroid := false，后者将根据球体计算距离）。

考虑到这一点，您的查询可以表示为：

WITH
  pt AS (
    SELECT ST_Transform(ST_SetSRID(ST_MakePoint(0, 0), 900914), 4326)::geography AS geog
  ),

  ctr AS (
    SELECT *,
           ST_Transform(ST_Centroid(wkt_geometry), 4326)::geography AS geog
    FROM cad_polygon
  )

SELECT ctr.cad_pid,
       cad.jtsdctn_id,
       ctr.ogr_fid,
       ST_Distance(ctr.geog, pt.geog) AS distance_meter,
       ctr.wkt_geometry
FROM ctr
JOIN cad
  ON ctr.cad_pid = cad.cad_pid
WHERE ST_DWithin(ctr.geog, pt.geog, 500)
ORDER BY distance_meter ASC;

请注意使用 CTE 来避免对每个处理的行进行转换/转换，并使事情更有条理。

我就是放不下这...

推荐阅读