clickhouse 查询优化_ClickHouse最近点查询优化

方案一

select Lon,Lat from pntsnew order by greatCircleDistance(Lon,Lat,-120.419219,34.889755999999998) limit 1

方案二

CREATE TABLE pntsnew ENGINE = MergeTree() order by (geohash,Lon,Lat) AS select geohashEncode(Lon, Lat,4) geohash, Lon,Lat from pnts

CREATE TABLE geohashP ENGINE = MergeTree() order by (geohash,lon,lat) AS select geohash,tupleElement(geohashDecode(geohash ),1) lon,tupleElement(geohashDecode(geohash ),2) lat from pntsnew group by　geohash

select Lon,Lat from pntsnew where geohash in (select geohash from geohashP order by greatCircleDistance(lon,lat ,-120.419219,34.889755999999998) limit 1) order by greatCircleDistance(Lon,Lat ,-120.419219,34.889755999999998) limit 1

select geoToH3( toFloat64(Lon), toFloat64(Lat),3) geoh3,toFloat64(Lon) Lon, toFloat64(Lat) Lat,id from pnts group by geohash

CREATE TABLE pntsh3 ENGINE = MergeTree() order by (h3,Lon,Lat) AS select geoToH3(Lon, Lat,4) h3, Lon,Lat from pnts

方案三

CREATE TABLE pntsnew ENGINE = MergeTree() PARTITION BY (geohash) order by (geohash,Lon,Lat) AS select geohashEncode(Lon, Lat,3) geohash, Lon,Lat from pnts

CREATE TABLE geohashP ENGINE = MergeTree() order by (geohash,lon,lat) AS select geohash,tupleElement(geohashDecode(geohash ),1) lon,tupleElement(geohashDecode(geohash ),2) lat from pntsnew group by　geohash

select Lon,Lat from pntsnew where geohash in (select geohash from geohashP order by greatCircleDistance(lon,lat ,-120.419219,34.889755999999998) limit 1) order by greatCircleDistance(Lon,Lat ,-120.419219,34.889755999999998) limit 1