ClickHouse nearest point query optimization

Plan a

Violence full table scan

The select Lon, Lat from pntsnew order by greatCircleDistance (Lon, Lat, 120.419219, 34.889755999999998) limit 1Copy the code

Scheme 2

Partition the table using the GeoHash, search for the nearest GeoHash, and finally scan for the nearest point in the corresponding GeoHash table. The use of partitions results in considerable performance improvements

Partition table is partitioned according to geoHash, CREATE TABLE pnTSNew ENGINE = MergeTree() PARTITION BY (geohash) order BY (Geohash,Lon,Lat) AS select geohashEncode(Lon, Lat,3) geohash, Lon,Lat from pntsCopy the code

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 bygeohashCopy the code

-- Search for the latest 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 (lat, lon Limit 1, 120.419219, 34.889755999999998)Copy the code