在地理信息世界，找到一個(gè)小的數(shù)據(jù)集是很容易的，例如excel文件的客戶端位置，一個(gè)geojson帶有數(shù)千個(gè)星巴克咖啡店的坐標(biāo)，這些案例足夠處理，甚至瀏覽器都能夠加載、處理和可視化.
但是當(dāng)我們討論到核心的地理信息處理挑戰(zhàn)時(shí), 我們面對(duì)海量處理問題. 例如, raster 數(shù)據(jù)來自衛(wèi)星影像 (地質(zhì)持續(xù)監(jiān)控), 一個(gè)大的地籍shapefiles 帶有數(shù)百萬個(gè)小的polygons, 或者 .csv files 通過 IoT 物聯(lián)網(wǎng)生成(他們是實(shí)際的 GPS 追蹤，有大量的掛載數(shù)據(jù)).
有大量的 CSVs 在 S3 云存儲(chǔ)中，保存類 GPS數(shù)據(jù). 以NYC Taxi 數(shù)據(jù)作為例子, 包含了所有NYC 出租車的數(shù)據(jù). 這是大數(shù)據(jù)界的基準(zhǔn)測(cè)試數(shù)據(jù); 你應(yīng)該看看Mark Litwintschik寫的帖子，質(zhì)量很好.
因此，通常情況下, 當(dāng)你分析地理信息數(shù)據(jù)時(shí)，你應(yīng)該停止使用PostgreSQL 和 PostGIS. 用SQL查詢地理信息的數(shù)據(jù)不能與其他工具很好匹配. 另一方面,? PostgreSQL對(duì)大數(shù)據(jù)量的處理確實(shí)不佳. 讓我們稍微解釋一下… 你能夠在PostgreSQL中加載大量的數(shù)據(jù)，同時(shí) 建立BRIN 索引, 你能夠選擇一小部分?jǐn)?shù)據(jù). 但是當(dāng)你聚合大量數(shù)據(jù)時(shí). PostgreSQL不是最佳選擇，這就是為什么產(chǎn)生了 citusdb, greenplum or Amazon redshift 這樣的方案，使用列式存儲(chǔ)，并行處理，下推操作來提升性能. 因此我們使用支持 PostGIS的PostgreSQL ，另一方面，我們使用速度足夠快，但部分支持地理空間信息處理的分布式數(shù)據(jù)庫來支持大數(shù)據(jù)量處理 (mapd, clickhouse, vertica or druiddb) .
讓我們回到NYC Taxi 數(shù)據(jù)集上，當(dāng)你想要知道某特定區(qū)域的位點(diǎn)的平均數(shù)量, PostgreSQL 使用 “ages”. 讓我們?cè)斀庠赑ostgreSQL(9.6)上的一些查詢:
[local] postgres@nyc-taxi-data=# select count(1) from trips;
? count
──────────
78424550
(1 row)
Time: 25347.000 ms
如果你想要找到在時(shí)代廣場(chǎng)方圓300公里內(nèi)有多少打車發(fā)生:
[local] postgres@nyc-taxi-data=# select count(1) from trips where st_dwithin(the_geom, 'SRID=4326;POINT(-73.985195 40.75896)', 180*300/(6371*1000*pi()));? ? ? ? ? ? ? ?
count
────────
797801
(1 row)
Time: 76552.877 ms
(我們使用角來計(jì)算一個(gè)平方的距離，如果是300公里我們使用公式distance = arc*radious)
76 秒看起來耗時(shí)較長(zhǎng)，肯定有一些方法來優(yōu)化，調(diào)優(yōu)PostgreSQL 或者使用并行查詢, 但是我們不能獲得很大的性能提升. 因此，即時(shí)我們使用了32核的高端服務(wù)器，那么對(duì)于性能提升也是有限的，整個(gè)數(shù)據(jù)集放在內(nèi)存中，理論上我們可以達(dá)到76s/32 ~ 2.3s.
有一些其他的數(shù)據(jù)庫或框架更適合處理大數(shù)據(jù)量，例如 spark, presto, vertica, mapd 等. 但是當(dāng)測(cè)試完 Clickhouse的性能以后, 我們決定選擇Clickhouse作為大數(shù)據(jù)處理平臺(tái).
我們加載 NYC taxi 數(shù)據(jù)到Clickhouse中. Clickhouse 并沒有任何的geospatial 工具包, 但是你能夠使用基本的歐幾里何的數(shù)學(xué)公式.
:) select count(1) from a2 where (pow(pickup_longitude - -73.985195, 2) +? pow(pickup_latitude - 40.75896, 2)) < pow(180*300/(6371*1000*pi()), 2);
SELECT count(1)
FROM trips
WHERE (pow(pickup_longitude - -73.985195, 2) + pow(pickup_latitude - 40.75896, 2)) < pow((180 * 300) / ((6371 * 1000) * pi()), 2)
┌─count(1)─┐
│? 613491 │
└──────────┘
rows in set. Elapsed: 0.308 sec. Processed 45.45 million rows, 818.13 MB (147.63 million rows/s., 2.66 GB/s.)
308ms
我們正在處理45.5M 行 (基本執(zhí)行全表掃描) ，速度超快. 如果我們使用索引來查詢接近時(shí)代廣場(chǎng)的行 (如PostgreSQL ) ，速度將會(huì)更快. 好消息是，Clickhouse 支持索引supports . 壞消息是它不支持 points 或 2D 數(shù)據(jù)的索引. 有一些方法可以線性化 2D 空間到標(biāo)量, 因此傳統(tǒng)的數(shù)據(jù)庫索引可以工作. Hilbert 曲線和geohash是更為通用的方式.在我們得案例案例中我們決定使用 quadkey.
Quadkey是基本的方式在一個(gè)整型中來編碼瓦片坐標(biāo)，對(duì)于每個(gè)區(qū)域，我們使用比特位來編碼1/4方格，每個(gè)方格有每個(gè)位置. 因此，我們能夠以64位整數(shù)來編碼任意的經(jīng)緯坐標(biāo)對(duì)兒，精度為1.9 cm (瓦片大小為31). 我們能夠使用Hilbert 的geohash ，但是quadkey 更適合這種方式來可視化數(shù)據(jù)(使用quadtree).生成速度快，與通用數(shù)據(jù)庫索引無縫結(jié)合.
通過創(chuàng)建一個(gè)帶有quadkey 列的表，pickup_latitude和pickup_longitude 作為索引，我們能夠使用 quadkey來查詢，帶上特定的where條件:
CREATE table test (
? ? lat Nullable(Float64),
? ? lon Nullable(Float64),
? ? datetime Nullable(DateTime),
? ? value Nullable(Float64),
? ? date_index Date,
? ? quadkey UInt64
)
ENGINE = MergeTree(date_index, quadkey, 8192)
運(yùn)行此查詢, 使用quadkey 索引:
:) select count(1) from a2 where quadkey between 1013670044871163904 and 1013670049166131200? and (pow(pickup_longitude - -73.985195, 2) +? pow(pickup_latitude - 40.75896, 2)) < pow(180*300/(6371*1000*pi()), 2)
SELECT count(1)
FROM a2
WHERE ((quadkey >= 1013670044871163904) AND (quadkey <= 1013670049166131200)) AND ((pow(pickup_longitude - -73.985195, 2) + pow(pickup_latitude - 40.75896, 2)) < pow((180 * 300) / ((6371 * 1000) * pi()), 2))
┌─count(1)─┐
│? 613491 │
└──────────┘
1 rows in set. Elapsed: 0.032 sec. Processed 2.41 million rows, 56.07 MB (75.88 million rows/s., 1.77 GB/s.)
處理的數(shù)據(jù)量相同，但這次Clickhouse 處理了2.41M 行, 獲得了10x提升.
