基地局データセットによる地理空間分析

DESCRIBE TABLE cell_towers

SELECT radio, count() AS c FROM cell_towers GROUP BY radio ORDER BY c DESC;
SELECT mcc, count() AS c FROM cell_towers GROUP BY mcc ORDER BY c DESC LIMIT 10;

CREATE DICTIONARY mcc_country_dict (MCC UInt16, Country String)
PRIMARY KEY MCC
SOURCE(HTTP(url 'https://raw.githubusercontent.com/ClickHouse/examples/refs/heads/main/datasets/mcc-countries.csv' format 'CSVWithNames'))
LAYOUT(HASHED()) LIFETIME(MIN 0 MAX 0);

SELECT mcc, dictGet('mcc_country_dict', 'Country', mcc) AS country, count() AS cnt
FROM cell_towers GROUP BY mcc ORDER BY cnt DESC LIMIT 5;

CREATE TABLE germany (polygon Array(Tuple(Float64, Float64))) ORDER BY polygon;
SELECT formatReadableQuantity(count()) FROM cell_towers
WHERE pointInPolygon((lon, lat), (SELECT * FROM germany));