Postgresql Postgis SQL Complex Join(不一定与GIS相关)

Question

对不起，标题乏善可陈，但很难描述......

假设我有两个表(CAD 和 cad_polygon(...

CAD 和 cad_polygon共享彼此相关的同一列 (cad_pid(...

CAD 具有以下列：cad_pid、jrsdctn_id

同时，cad_polygon具有以下列：cad_pid、ogc_fid wkb_geometry

现在，我正在处理的以下查询(半天尝试(根据度数从长纬度坐标中选择一个宗地，从明显较小的多边形子集中，它找到它们与给定的长纬度坐标的距离(以米为单位(，然后仅显示质心在长纬度坐标 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_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向导可以提供帮助，那就太好了！

Answer 1

有点晚了，当然，关于JOIN的公认答案是绝对正确的，但这实际上与GIS密切相关，它的意识最终使您更轻松：

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

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

现在，PostGIS 还具有地理类型，它再次假定 EPSG：4326 坐标，如果与这些功能一起使用，它将隐式使用米作为单位，如果与默认ST_Distance参数一起使用，则将在 WGS84 椭球体上进行计算(更精确，但比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 来避免对每个已处理的行进行转换/转换，并使事情更加结构化。

我就是不能放手...

Answer 2

您可以使用 JOIN

SELECT SUBQUERY.cad_pid, SUBQUERY.ogc_fid, SUBQUERY.dist_meters,
SUBQUERY.wkb_geometry, SUBQUERY.jrsdctn_id 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, cad.jrsdctn_id
FROM cad_polygon
INNER JOIN cad on cad.cad_pid = cad_polygon.cad_pid
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;

或者最好在外部添加联接

SELECT SUBQUERY.cad_pid
, SUBQUERY.ogc_fid
, SUBQUERY.dist_meters,
SUBQUERY.wkb_geometry, cad.jrsdctn_id 
FROM (
SELECT cad_polygon.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
INNER JOIN cad on cad.cad_pid = SUBQUERY.cad_pid
WHERE SUBQUERY.dist_meters < 500;