SQL Server Spatial Join问题：点与多边形空间匹配无结果排查

本人拥有采矿与勘探背景，现有一组带DHId、X和Y坐标的点数据，另有一张包含多边形（claim_number）的leasehold表。坐标采用UTM投影，需找出哪些点落在哪些多边形内。尝试以下SQL代码未返回结果，期望得到每个DHId对应的相交/落入的claim_number列表：

SELECT 
      pg.claim_number, 
      p.DHId 
FROM leasehold as pg
JOIN
(SELECT 
     DHId, 
     X, 
     Y,
     geometry::STPointFromText('POINT(',X,' ',Y,'), 0) AS [geom]
 FROM collar 
      WHERE X is not null AND 
      Y is not null AND 
      claim_number is null) AS p
ON pg.Shape.STIntersects(p.geom) = 1

• 点构造语法错误：STPointFromText要求传入完整合法的WKT字符串，你当前的写法拼接逻辑错误，导致生成的WKT格式无效。需要用字符串连接符把坐标和POINT模板拼接成正确格式。
• SRID不匹配：代码中指定的SRID为0，但UTM投影有对应的专属SRID（比如UTM 50N对应32650），必须保证点的SRID与leasehold表中Shape字段的SRID完全一致，否则空间相交判断会失效。
• 空间索引缺失（可选）：如果数据量较大，未创建空间索引会导致查询效率极低，建议给leasehold的Shape字段和构造的点geom字段添加空间索引。

修正后的SQL代码（以SQL Server为例）：

SELECT 
    pg.claim_number, 
    p.DHId 
FROM leasehold as pg
JOIN (
    SELECT 
        DHId, 
        X, 
        Y,
        -- 替换326XX为实际UTM投影对应的SRID，确保与Shape字段一致
        geometry::STPointFromText('POINT(' + CAST(X AS VARCHAR(50)) + ' ' + CAST(Y AS VARCHAR(50)) + ')', 326XX) AS geom
    FROM collar 
    WHERE X IS NOT NULL 
      AND Y IS NOT NULL 
      AND claim_number IS NULL
) AS p
ON pg.Shape.STIntersects(p.geom) = 1

补充说明：
如果使用PostGIS，语法需调整为：

SELECT 
    pg.claim_number, 
    p.DHId 
FROM leasehold as pg
JOIN (
    SELECT 
        DHId, 
        X, 
        Y,
        ST_SetSRID(ST_MakePoint(X, Y), 326XX) AS geom
    FROM collar 
    WHERE X IS NOT NULL 
      AND Y IS NOT NULL 
      AND claim_number IS NULL
) AS p
ON ST_Intersects(pg.Shape, p.geom);

内容的提问来源于stack exchange，提问作者Jajabaur