SQL Server T-SQL批量查询：geometry多边形匹配geography类型点位

前置准备（性能优化必做） #

• 统一空间类型与SRID：当前多边形用geometry、点位用geography，类型不兼容无法直接做空间运算，建议统一转为SRID=4326（WGS84坐标系）的geography类型，确认多边形存储的是WGS84经纬度坐标的话，可直接新增持久化计算列：

  ALTER TABLE [dbo].[GeoPolygons] ADD [Geography] AS 
  geography::STGeomFromWKB([Geometry].STAsBinary(), 4326) PERSISTED;
  

  如果你的多边形存储用的不是WGS84坐标系，替换上面语句里的4326为实际对应的SRID即可
• 建立空间索引，大幅提升匹配效率：
  点位表空间索引：

  CREATE SPATIAL INDEX IX_GeoPoints_PointGeometry ON [dbo].[GeoPoints](point_geometry)
  USING GEOGRAPHY_GRID
  WITH (GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM));
  

  多边形表新增geography列的空间索引：

  CREATE SPATIAL INDEX IX_GeoPolygons_Geography ON [dbo].[GeoPolygons](Geography)
  USING GEOGRAPHY_GRID
  WITH (GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM));
  

全量批量匹配语句 #

直接调用SQL Server原生的STIntersects空间运算方法批量匹配，性能远高于逐行解析WKT的方案，数千级数据可秒级完成计算：

INSERT INTO [dbo].[Points2Polygons] (entity_id, point_id)
SELECT 
    p.entity_id,
    pt.point_id
FROM [dbo].[GeoPolygons] p
INNER JOIN [dbo].[GeoPoints] pt
ON p.Geography.STIntersects(pt.point_geometry) = 1
-- 内置去重逻辑，避免重复插入已有的匹配关系
WHERE NOT EXISTS (
    SELECT 1 FROM [dbo].[Points2Polygons] m 
    WHERE m.entity_id = p.entity_id AND m.point_id = pt.point_id
);

如果存在多边形重叠场景，单个点位会匹配到多个多边形，不需要多匹配结果的话可加ROW_NUMBER()窗口函数取排序第一的归属结果即可

增量更新方案 #

每日更新方案 #

直接将上述全量匹配语句配置到SQL Server代理的每日定时作业中即可，语句自带去重逻辑，不会重复插入已有匹配关系。

实时更新方案 #

分别给两张源表新增插入触发器，新增数据时自动计算匹配关系：

1. 点位表新增数据触发匹配：

CREATE TRIGGER TRG_GeoPoints_InsertMatch
ON [dbo].[GeoPoints]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[Points2Polygons] (entity_id, point_id)
    SELECT p.entity_id, i.point_id
    FROM inserted i
    INNER JOIN [dbo].[GeoPolygons] p ON p.Geography.STIntersects(i.point_geometry) = 1
    WHERE NOT EXISTS (
        SELECT 1 FROM [dbo].[Points2Polygons] m 
        WHERE m.entity_id = p.entity_id AND m.point_id = i.point_id
    );
END

2. 多边形表新增数据触发匹配：

CREATE TRIGGER TRG_GeoPolygons_InsertMatch
ON [dbo].[GeoPolygons]
AFTER INSERT
AS
BEGIN
    INSERT INTO [dbo].[Points2Polygons] (entity_id, point_id)
    SELECT i.entity_id, pt.point_id
    FROM inserted i
    INNER JOIN [dbo].[GeoPoints] pt ON i.Geography.STIntersects(pt.point_geometry) = 1
    WHERE NOT EXISTS (
        SELECT 1 FROM [dbo].[Points2Polygons] m 
        WHERE m.entity_id = i.entity_id AND m.point_id = pt.point_id
    );
END

如果存在源数据修改、删除的需求，可对应新增UPDATE/DELETE触发器维护匹配关系即可

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