Гео и IP-функции

ClickHouse содержит встроенные геопространственные функции для расчёта расстояний, пространственной индексации (H3, S2) и проверки принадлежности точки полигону. Отдельное семейство IP-функций обеспечивает конвертацию и геолокацию по IP-адресам через ip_trie словарь.

Эти функции позволяют решать задачи геоаналитики непосредственно в SQL-запросах, без внешних GIS-библиотек.

Расстояние: geoDistance

-- Расстояние между Москвой и Санкт-Петербургом (в метрах)
SELECT geoDistance(37.6173, 55.7558, 30.3141, 59.9343) AS distance_m;
-- Результат: ~634 370 метров (~634 км)

geoDistance(lon1, lat1, lon2, lat2) вычисляет расстояние в метрах между двумя точками на поверхности Земли по модели WGS-84 эллипсоида. Это более точный расчёт, чем greatCircleDistance (сферическая модель), особенно на больших расстояниях.

-- Ближайшие склады к точке доставки
SELECT
    warehouse_name,
    geoDistance(delivery_lon, delivery_lat, warehouse_lon, warehouse_lat) AS distance_m
FROM orders
CROSS JOIN warehouses
WHERE geoDistance(delivery_lon, delivery_lat, warehouse_lon, warehouse_lat) < 50000
ORDER BY distance_m ASC;

H3: Uber hexagonal grid

H3 — система пространственной индексации от Uber. Поверхность Земли покрывается шестиугольниками (гексагонами) на 16 уровнях разрешения (0-15). Каждый гексагон имеет уникальный 64-bit идентификатор.

-- Преобразовать координаты в H3 индекс (resolution 7)
SELECT geoToH3(55.7558, 37.6173, 7) AS h3_index;
-- h3_index: 87413b859ffffff (UInt64)

Уровни разрешения H3

-- Обратное преобразование: H3 -> координаты центра
SELECT h3ToGeo(h3_index) AS (lat, lon)
FROM geo_events;

Тепловая карта с H3

-- Агрегация поездок в гексагоны для тепловой карты
SELECT
    geoToH3(pickup_lat, pickup_lon, 7) AS h3_cell,
    count() AS trips,
    avg(fare) AS avg_fare
FROM taxi_rides
WHERE pickup_date = '2025-01-15'
GROUP BY h3_cell
ORDER BY trips DESC
LIMIT 20;

H3 идеален для heatmap-визуализаций: гексагоны одинаковой площади (в отличие от прямоугольных grid), равноудалённые соседи, иерархическая вложенность (resolution 7 содержит ~7 ячеек resolution 8).

S2: Google spherical cells

S2 — система пространственной индексации от Google. Поверхность Земли проецируется на куб, каждая грань делится рекурсивно на 4 квадрата. 64-bit cell ID кодирует положение и уровень.

-- Координаты -> S2 cell ID
SELECT geoToS2(37.6173, 55.7558) AS s2_cell_id;

-- S2 cell ID -> координаты
SELECT s2ToGeo(s2_cell_id) AS (lon, lat) FROM geo_data;

Когда H3, когда S2

• H3 — пространственная агрегация, heatmaps, анализ плотности. Гексагоны лучше для визуализации (нет corner-артефактов).
• S2 — пространственный поиск, range queries. Совместимость с Google BigQuery и Google Maps.
• Polygon — произвольные зоны (доставка, геозоны, административные границы).

Polygon: pointInPolygon

-- Проверка: точка внутри полигона (зона доставки)
SELECT pointInPolygon(
    (37.6173, 55.7558),  -- точка (lon, lat)
    [(37.5, 55.7), (37.7, 55.7), (37.7, 55.8), (37.5, 55.8), (37.5, 55.7)]  -- полигон (замкнутый)
) AS inside_zone;
-- Результат: 1 (точка внутри)

Полигон задаётся массивом кортежей (координаты вершин). Последняя точка должна совпадать с первой (замкнутый полигон).

-- Фильтрация событий по зоне доставки
SELECT user_id, event_time
FROM orders
WHERE pointInPolygon(
    (delivery_lon, delivery_lat),
    [(37.5, 55.7), (37.75, 55.7), (37.75, 55.82), (37.5, 55.82), (37.5, 55.7)]
);

IP-функции: конвертация и работа с адресами

-- Числовое представление -> строка
SELECT IPv4NumToString(3232235777);
-- Результат: '192.168.1.1'

-- Строка -> числовое представление
SELECT IPv4StringToNum('192.168.1.1');
-- Результат: 3232235777

-- IPv6 эквиваленты
SELECT IPv6NumToString(toFixedString(unhex('20010db8000000000000000000000001'), 16));
-- Результат: '2001:db8::1'

SELECT IPv6StringToNum('2001:db8::1');

Определение типа IP и маскирование

-- Проверка: IPv4 или IPv6
SELECT isIPv4String('192.168.1.1');  -- 1
SELECT isIPv4String('2001:db8::1');  -- 0

-- Маскирование (анонимизация)
SELECT IPv4NumToString(IPv4CIDRToRange(IPv4StringToNum('192.168.1.100'), 24).1) AS network;
-- Результат: '192.168.1.0' (начало /24 сети)

ip_trie словарь для GeoIP

ip_trie — специализированный layout словаря для определения геолокации по IP-адресу. Использует Trie-структуру с longest prefix match: для IP 192.168.1.100 находит наиболее специфичный CIDR-диапазон (например, 192.168.1.0/24 вместо 192.168.0.0/16).

Подробнее о словарях и layout-ах — в уроке “Словари: layout-ы и конфигурация” (Module 05).

-- Создание ip_trie словаря для геолокации
CREATE DICTIONARY ip_geo_dict (
    prefix String,           -- CIDR: '192.168.0.0/16'
    country_code String,
    country_name String,
    city String,
    latitude Float64,
    longitude Float64
)
PRIMARY KEY prefix
SOURCE(CLICKHOUSE(TABLE 'ip_ranges' DB 'default'))
LAYOUT(IP_TRIE())
LIFETIME(MIN 3600 MAX 7200);

Использование ip_trie для геолокации

-- Геолокация по IP-адресу
SELECT
    ip_address,
    dictGet('ip_geo_dict', 'country_name', tuple(IPv4StringToNum(ip_address))) AS country,
    dictGet('ip_geo_dict', 'city', tuple(IPv4StringToNum(ip_address))) AS city
FROM web_logs
LIMIT 10;

-- Топ стран по трафику
SELECT
    dictGet('ip_geo_dict', 'country_name', tuple(IPv4StringToNum(client_ip))) AS country,
    count() AS requests,
    uniq(session_id) AS sessions
FROM access_log
WHERE event_date = today()
GROUP BY country
ORDER BY requests DESC
LIMIT 20;

H3-агрегация с IP-геолокацией

-- Комбинация: IP -> координаты (ip_trie) -> H3 (пространственная агрегация)
SELECT
    geoToH3(
        dictGet('ip_geo_dict', 'latitude', tuple(IPv4StringToNum(client_ip))),
        dictGet('ip_geo_dict', 'longitude', tuple(IPv4StringToNum(client_ip))),
        5
    ) AS h3_cell,
    count() AS requests
FROM access_log
WHERE event_date = today()
GROUP BY h3_cell
ORDER BY requests DESC;

Ключевые выводы

1. geoDistance(lon1, lat1, lon2, lat2) — расстояние в метрах по модели WGS-84 эллипсоида. Порядок аргументов: долгота, широта.
2. H3 (geoToH3) — гексагональная сетка от Uber. 16 уровней разрешения (0-15). Идеален для heatmaps и пространственной агрегации. Порядок: (lat, lon, resolution).
3. S2 (geoToS2) — сферические ячейки от Google. Совместимость с BigQuery. Порядок: (lon, lat).
4. pointInPolygon — проверка принадлежности точки полигону. Полигон = замкнутый массив кортежей координат.
5. ip_trie — словарный layout для GeoIP. Longest prefix match по CIDR. dictGet с IPv4StringToNum для lookup (подробнее — Module 05, урок “Словари: layout-ы и конфигурация”).