ClickHouse十秒亿点关联百万面

背景

    现在业务中往往有海量点和建筑物的关联，但是传统关系数据处理时间总不太理想。本文尝试使用ClickHouse导入面数据，并进行多面和多点的关联。

数据准备

create table extent (id UInt32,xmin  Float64, xmax Float64,ymin  Float64, ymax Float64,wkt String) engine = MergeTree() order by (id,xmin,xmax,ymin,ymax);

image

数据处理

    导入面数据(使用arcpy来处理),读取shp数据的id、最大最小经纬度、wkt(点数据使用之前导入的数据，https://juejin.cn/post/6903100159484395534)

image

time clickhouse-client --query="INSERT INTO extent FORMAT CSVWithNames" < AmericanPolygon.csv

image

    把wkt转为阵列为后面计算做准备(分割字符串，得到单个坐标组并转为float阵列)，目前只是使用单面和无洞的多边形面数据。

CREATE TABLE extents ENGINE = MergeTree() order by (id,xmin,xmax,ymin,ymax) AS    select   id,xmin,xmax,ymin,ymax,arrayMap(x -> tuple(arrayElement(arrayMap(x -> toFloat64(x),splitByChar(' ',trimLeft(x))),1),arrayElement(arrayMap(x -> toFloat64(x),splitByChar(' ',trimLeft(x))),2)),splitByChar(',', replaceAll(replaceAll(replaceOne( wkt, 'MULTIPOLYGON ', ''),')',''),'(',''))) geo from extent

    根据面的范围生成七级精度下计算最小包含指定的最小图形的geohash数组，用这些geohash来筛选相应点，点有了geohash再重新关联面，最后得到一个点面geohash值中间表。(原本要这里就出结果，但是不生成中间表，最后处理速度就非常慢)

image

CREATE TABLE extentpnt ENGINE = MergeTree() order by (id,Lon,Lat) AS    select b.Lon,b.Lat,a.id,a.geo from (  select Lon,Lat,geohashEncode(Lon, Lat,7) geohash FROM pnts where geohashEncode(Lon, Lat,7) in (     select arrayJoin(geohashesInBox(xmin, ymin, xmax , ymax,7)) from extents  group by arrayJoin(geohashesInBox(xmin, ymin, xmax , ymax,7))) ) b cross join (  select arrayJoin(geohashesInBox(xmin, ymin, xmax , ymax,7)) geohash,xmin,ymin,xmax,ymax,id,geo from extents  group by arrayJoin(geohashesInBox(xmin, ymin, xmax , ymax,7)),xmin,ymin,xmax,ymax,id,geo  ) a  where a.geohash=b.geohash and   b.Lon BETWEEN a.xmin AND a.xmax and b.Lat BETWEEN a.ymin and a.ymax 

image image

    点面相交得到最后结果

select Lon,Lat,id from extentpnt  where pointInPolygon((Lon,Lat),geo)=1  

image

结论

    ClickHouse是数据库黑马，目前在点面关联上有不俗的性能，但是空间分析过于稀少，空间数据存储支持不完善还待优化。

参考资料：

https://clickhouse.tech/docs/zh/sql-reference/functions/string-functions/

https://blog.csdn.net/qq_36951116/article/details/106260189