ClickHouse如何实现多边形内包含/重叠的多边形检索查询

ClickHouse原生支持你需要的多边形空间检索能力，相关能力基于内置地理空间函数实现，无需额外扩展组件。

核心匹配函数 #

你描述的包含、重叠两类检索需求，可直接通过以下两个内置函数实现：

• geoIntersects(geo1, geo2)：判定两个几何对象是否存在公共点，只要两个对象重叠或者一方完全包含另一方都会返回true，可覆盖你提到的两类匹配场景
• geoContains(outer_geo, inner_geo)：专门判定inner_geo是否完全落在outer_geo的范围内，如果仅需要完全包含的结果可单独使用该函数

单bounding box查询示例 #

假设你的存储表名为geo_data，存储多边形的字段名为geom，类型为Polygon，查询范围是左下角(100, 30)、右上角(120, 40)的矩形，查询语句如下：

SELECT * FROM geo_data
WHERE geoIntersects(
    toPolygon('[(100, 30), (120, 30), (120, 40), (100, 40), (100, 30)]'),
    geom
)

多bounding box数组查询示例 #

如果需要传入多个查询范围，匹配与任意一个范围存在包含/重叠关系的数据，可结合arrayExists函数实现：

SELECT * FROM geo_data
WHERE arrayExists(
    bbox -> geoIntersects(bbox, geom),
    -- 此处替换为你传入的bounding box数组
    [
        toPolygon('[(100, 30), (120, 30), (120, 40), (100, 40), (100, 30)]'),
        toPolygon('[(130, 20), (150, 20), (150, 50), (130, 50), (130, 20)]')
    ]
)

性能优化建议 #

• 数据量较大时可给空间字段添加布隆过滤器索引，降低扫描数据量，建索引语句如下：

  ALTER TABLE geo_data ADD INDEX geom_idx geom TYPE bloom_filter GRANULARITY 3;
  

• 如果存储的都是规则bounding box，可直接拆分为min_lon、min_lat、max_lon、max_lat四个浮点型字段存储，直接用数值运算做匹配，性能比空间函数高30%以上，匹配逻辑为：

  SELECT * FROM geo_data
  WHERE NOT (
      max_lon < <查询bbox的最小经度>
      OR min_lon > <查询bbox的最大经度>
      OR max_lat < <查询bbox的最小纬度>
      OR min_lat > <查询bbox的最大纬度>
  )
  

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