MySQL如何基于Feature Collection查询多边形在指定区域内的数据行

核心思路 #

要处理Feature Collection类型的GeoJSON搜索，关键是先将集合中的所有要素几何展开，再逐一与搜索区域进行空间包含判断。

具体实现步骤 #

• 解析要素集合中的所有几何
  使用JSON_TABLE将GeoJSON数组展开为多行数据，提取每个要素的几何部分：

  SELECT 
      j.id,
      ST_GeomFromGeoJSON(f.geometry) AS geom
  FROM jobs j
  JOIN JSON_TABLE(
      j.geo_json->'$.features',
      '$[*]' COLUMNS(geometry JSON PATH '$.geometry')
  ) f;
  

• 结合ST_Contains筛选目标行
  将搜索区域的GeoJSON转为几何对象，与每个要素几何做包含判断，通过DISTINCT避免重复返回同一行：

  SELECT DISTINCT j.*
  FROM jobs j
  JOIN JSON_TABLE(
      j.geo_json->'$.features',
      '$[*]' COLUMNS(geometry JSON PATH '$.geometry')
  ) f
  WHERE ST_Contains(
      ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[116.397,39.908],[116.407,39.908],[116.407,39.918],[116.397,39.918],[116.397,39.908]]]}'), -- 替换为你的搜索区域GeoJSON
      ST_GeomFromGeoJSON(f.geometry)
  );
  

• 性能优化方案
  若数据量较大，建议预合并要素几何并创建空间索引：

  1. 新增几何类型列存储合并后的要素：

     ALTER TABLE jobs ADD COLUMN merged_geom GEOMETRY;
     

  2. 批量更新合并几何：

     UPDATE jobs j
     SET merged_geom = (
         SELECT ST_Union(ST_GeomFromGeoJSON(f.geometry))
         FROM JSON_TABLE(
             j.geo_json->'$.features',
             '$[*]' COLUMNS(geometry JSON PATH '$.geometry')
         ) f
     );
     

  3. 创建空间索引：

     CREATE SPATIAL INDEX idx_merged_geom ON jobs(merged_geom);
     

  4. 优化后的查询语句：

     SELECT * FROM jobs
     WHERE ST_Contains(
         ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[...]]]}'), -- 搜索区域GeoJSON
         merged_geom
     );
     

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