SQL Server Point to polygon问题：shapefile关联经纬度表匹配方案优化

核心逻辑说明 #

本次优化同时解决两个问题：原geom字段geometry/geography类型不兼容、游标循环性能低下问题，直接采用集合运算一次性完成全量匹配更新，代码复杂度大幅降低。

• 类型兼容处理：若shapefile的geom字段采用WGS84（SRID 4326）坐标系，直接通过方法转为同SRID的GEOGRAPHY类型即可，无需修改原表结构
• 游标替换：采用UPDATE JOIN语法直接关联两张表，通过空间匹配条件一次性完成更新，性能远高于逐行循环的游标方案

优化后代码 #

UPDATE a
SET a.sd_new = b.id
FROM [work_old].[dbo].[oh_jk] a
INNER JOIN [GIS].[dbo].[oh_2020_state_upper_2021-09-16_2031-06-30] b
    ON b.geom IS NOT NULL
    AND a.sd_new IS NULL
    AND a.RegistrationAddressLongitude <> ' '
    AND a.RegistrationAddressLatitude <> ' '
    -- 构造业务表坐标点的GEOGRAPHY实例
    AND geography::STGeomFromText(
        'POINT(' + a.RegistrationAddressLongitude + ' ' + a.RegistrationAddressLatitude + ')',
        4326
    ).STIntersects(
        -- 将空间表的geometry类型多边形转为同SRID的GEOGRAPHY实例
        geography::STGeomFromText(b.geom.MakeValid().STAsText(), 4326)
    ) = 1
GO

注意事项&性能优化 #

1. 若执行后出现匹配结果反向的问题（点在多边形内但未匹配到），是因为多边形顶点顺序不符合GEOGRAPHY类型的右手法则，在GEOGRAPHY构造语句后添加.ReorientObject()即可修复：

   geography::STGeomFromText(b.geom.MakeValid().STAsText(), 4326).ReorientObject()
   

2. 给业务表oh_jk的sd_new、RegistrationAddressLongitude、RegistrationAddressLatitude字段加过滤索引，可减少扫描的数据量
3. 给空间表的geom字段添加空间索引，可大幅提升STIntersects空间匹配的效率
4. 若shapefile采用的不是4326坐标系，先将geom字段的SRID转换为4326后再执行上述代码即可

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