ClickHouse空间分析运用

ClickHouse

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。

OLAP场景的关键特征

绝大多数是读请求

数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。

已添加到数据库的数据不能修改。

对于读取，从数据库中提取相当多的行，但只提取列的一小部分。

宽表，即每个表包含着大量的列

查询相对较少(通常每台服务器每秒查询数百次或更少)

对于简单查询，允许延迟大约50毫秒

列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)

处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)

事务不是必须的

对数据一致性要求低

每个查询有一个大表。除了他意以外，其他的都很小。

查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

很容易可以看出，OLAP场景与其他通常业务场景(例如,OLTP或K/V)有很大的不同， 因此想要使用OLTP或Key-Value数据库去高效的处理分析查询场景，并不是非常完美的适用方案。例如，使用OLAP数据库去处理分析请求通常要优于使用MongoDB或Redis去处理分析请求。

ClickHouse安装和启动

sudo apt-get install apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4
echo "deb https://repo.clickhouse.tech/deb/stable/ main/" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update
sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start
clickhouse-client

sudo apt-get update如果更新不了可以修改下源，然后修改source.list，clickhouse.list

image
image

新建表导入所需要的数据(120038310条经纬度)

image
image

create table pnts (Lon  Float64, Lat Float64) engine = MergeTree() order by (Lon, Lat);
time clickhouse-client --query="INSERT INTO pnts FORMAT CSVWithNames" < test_data.csv

ClickHouse空间分析运用

计算最大小经纬度

select min(Lon), max(Lon),min(Lat), max(Lat)FROM pnts

image

select geohashesInBox(-150.0565255, 24.5449115001 , -66.950609997, 65.1341342731, 4)

image

面筛选

SELECT count(1) FROM pnts WHERE -122.1603396012797162<=Lon and -122.1414371044578786>=Lon and 37.7842593304459271  <=Lat and 37.7965938218938646  >=Lat  and pointInPolygon((Lon, Lat), [( -122.158001836655117, 37.796593821893865 ), ( -122.151099864906271, 37.793632653369357 ), ( -122.146446600082044, 37.792029614769625 ), ( -122.141437104457879, 37.789224297220095 ), ( -122.143485431557536, 37.788534100045212 ), ( -122.14290655650764, 37.786641623920524 ), ( -122.143797133507491, 37.786196335420598 ), ( -122.147648879031848, 37.784437445845896 ), ( -122.14862851373168, 37.784259330445927 ), ( -122.15312592758093, 37.788734479870179 ), ( -122.154728966180656, 37.789736378995009 ), ( -122.156443326905375, 37.788801273145168 ), ( -122.160094692604758, 37.789335619345074 ), ( -122.15915958675491, 37.792185465744602 ), ( -122.160339601279716, 37.793788504344327 ), ( -122.159293173304889, 37.795369278519068 ), ( -122.159048264629931, 37.795525129494038 ), ( -122.159048264629931, 37.795525129494038 ), ( -122.158001836655117, 37.796593821893865 )]) = 1

image

缓冲区计算

SELECT count(1) from pnts WHERE greatCircleDistance(Lon, Lat, -122.158001836655117, 37.796593821893865 )<=2000

image

select geoToH3(Lon, Lat, 3),count(1) FROM pnts group by geoToH3(Lon, Lat, 3)

image

geohash聚合

SELECT geohashEncode(Lon, Lat, 5),count(1) FROM pnts group by geohashEncode(Lon, Lat, 5)

image
参考资料：

https://clickhouse.tech/docs/zh/

https://www.osgeo.cn/qgis-tutorial/docs/3/importing_spreadsheets_csv.html

https://mirror.tuna.tsinghua.edu.cn/help/clickhouse/

https://blog.csdn.net/BigData_Mining/article/details/87867979

http://cncc.bingj.com/cache.aspx?q=clickhouse+anzhuang&d=4519770289931770&mkt=zh-CN&setlang=zh-CN&w=J6QG46UMC2AHWpTGJHFimsC7lpDNMJO-

https://github.com/ClickHouse/ClickHouse/issues/9002

https://www.bookstack.cn/read/clickhouse-20.10-en/bccae583b76cdb17.md

https://blog.csdn.net/jimo_lonely/article/details/107498806

https://github.com/ClickHouse/ClickHouse/issues/17081