Геопространственный анализ на примере набора данных о базовых станциях сотовой связи

Узнайте, как загрузить набор данных OpenCelliD о базовых станциях сотовой связи в ClickHouse Cloud, обогатить его с помощью словаря, выполнять к нему запросы с помощью геофункций и визуализировать данные с помощью ClickHouse Agents

В этом руководстве вы узнаете, как использовать ClickHouse для работы с геоданными на примере набора данных о базовых станциях сотовой связи. Вы загрузите набор данных в ClickHouse Cloud, изучите его схему и выполните агрегирующие запросы по десяткам миллионов строк. Затем вы обогатите данные с помощью словаря, подсчитаете число вышек на территории Германии с помощью функции pointInPolygon и визуализируете результат на карте с помощью ClickHouse Agents.

​

Предварительные требования

Для этого руководства вам понадобятся:

• Аккаунт ClickHouse Cloud (при регистрации начисляется $300 бесплатных кредитов)
• Сервис ClickHouse Cloud

1. Загрузите тестовые данные

   Набор данных, используемый в этом руководстве, взят из OpenCelliD — крупнейшей в мире открытой базы данных базовых станций сотовой связи. По состоянию на 2021 год в нём содержатся десятки миллионов записей о базовых станциях сотовой связи по всему миру — с географическими координатами и метаданными, такими как код страны и сеть.

   1. В ClickHouse Cloud в меню слева выберите свой сервис в выпадающем списке
   2. Выберите Data sources
   3. Нажмите на карточку Add sample data
   4. Выберите набор данных Cell Towers (1.1 GB)
   5. Укажите default в качестве базы данных назначения и нажмите Import dataset

   В разделе Data upload history должна появиться запись со статусом success

2. Изучите схему

   1. Выберите SQL console в меню слева
   2. Нажмите на вкладку + рядом со значком домашней страницы, чтобы создать новый запрос
   3. В редакторе SQL введите следующий запрос и нажмите Run:

   DESCRIBE TABLE cell_towers
   

   Должна отобразиться следующая таблица результатов:

   ┌─name──────────┬─type──────────────────────────────────────────────────────────────────┬
   │ radio         │ Enum8('' = 0, 'CDMA' = 1, 'GSM' = 2, 'LTE' = 3, 'NR' = 4, 'UMTS' = 5) │
   │ mcc           │ UInt16                                                                │
   │ net           │ UInt16                                                                │
   │ area          │ UInt16                                                                │
   │ cell          │ UInt64                                                                │
   │ unit          │ Int16                                                                 │
   │ lon           │ Float64                                                               │
   │ lat           │ Float64                                                               │
   │ range         │ UInt32                                                                │
   │ samples       │ UInt32                                                                │
   │ changeable    │ UInt8                                                                 │
   │ created       │ DateTime                                                              │
   │ updated       │ DateTime                                                              │
   │ averageSignal │ UInt8                                                                 │
   └───────────────┴───────────────────────────────────────────────────────────────────────┴
   

   Каждая запись в таблице соответствует вышке сотовой связи, реально существующей на Земле; её местоположение задаётся координатами lon и lat.

3. Выполните простые запросы

   Выполните следующий запрос, чтобы узнать количество базовых станций сотовой связи в разбивке по типам:

   SELECT radio, count() AS c FROM cell_towers GROUP BY radio ORDER BY c DESC
   

   ┌─radio─┬────────c─┐
   │ UMTS  │ 20686487 │
   │ LTE   │ 12101148 │
   │ GSM   │  9931304 │
   │ CDMA  │   556344 │
   │ NR    │      867 │
   └───────┴──────────┘
   

   Далее посчитайте количество базовых станций сотовой связи в разбивке по мобильному коду страны (MCC):

   SELECT mcc, count() FROM cell_towers GROUP BY mcc ORDER BY count() DESC LIMIT 10
   

   ┌─mcc─┬─count()─┐
   │ 310 │ 5024650 │
   │ 262 │ 2622423 │
   │ 250 │ 1953176 │
   │ 208 │ 1891187 │
   │ 724 │ 1836150 │
   │ 404 │ 1729151 │
   │ 234 │ 1618924 │
   │ 510 │ 1353998 │
   │ 440 │ 1343355 │
   │ 311 │ 1332798 │
   └─────┴─────────┘
   

   Как видно, больше всего базовых станций сотовой связи в странах с кодами MCC 310, 262 и 250. Чтобы заменить числовые значения столбца mcc названиями стран, можно воспользоваться словарём:

   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)
   

   Теперь, когда словарь создан, с его помощью можно получить тот же список, дополнив MCC названием страны:

   Query

   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
   

   Response

   310	United States of America	5024650
   262	Germany	                    2622423
   250	Russia	                    1953176
   208	France	                    1891187
   724	Brazil	                    1836150
   
4. Добавьте геоданные

   Возможно, вам также будет интересно узнать, сколько базовых станций сотовой связи находится в пределах определённой географической области. Для решения такой задачи в ClickHouse есть множество полезных геофункций, например функция pointInPolygon.

   Предположим, вы хотите узнать, сколько базовых станций сотовой связи находится на территории Германии. Создайте таблицу germany с одним столбцом типа polygon:

   CREATE TABLE germany (polygon Array(Tuple(Float64, Float64)))
   ORDER BY polygon;
   

   Теперь вставьте в таблицу координаты контура Германии (без островов):

   INSERT INTO germany VALUES ([(10.454459535579133, 47.55573834324997), (10.890209019630618, 47.537202200721026), (10.970328786144592, 47.40002058348318), (11.272891854275713, 47.39785257163891), (11.637139099589831, 47.59425903862376),
   (12.20388194699433, 47.60680973608049), (12.162598450658038, 47.70112977791695), (12.256972280946059, 47.7430071703447), (12.255189497297522, 47.679294992270115), (12.440126465064338, 47.69517254727526),
   (12.499216429994306, 47.625017004631104), (12.781115045223203, 47.674141358936595), (12.80384823830559, 47.54988690165925), (13.04750486076074, 47.49208302661043), (13.080793954277624, 47.68702414364935),
   (12.905046866219948, 47.72364341860896), (13.003294582878766, 47.85031169133157), (12.758258591061178, 48.126325188057024), (13.329748843534333, 48.32352787700995), (13.508992162958009, 48.59060155142049),
   (13.727359451977179, 48.51300820806239), (13.839506769122124, 48.771604902544254), (13.628668330878838, 48.949206192112115), (13.402895552887514, 48.98734273487935), (13.029109282267598, 49.304348045426025),
   (12.655893345501568, 49.43454683436897), (12.521638213932476, 49.68676182809594), (12.40071737081007, 49.75384886863043), (12.547730388346793, 49.92034323954891), (12.201422342567128, 50.10850072273024),
   (12.10090036995615, 50.31802824350871), (12.184453107724778, 50.32232471771158), (12.289803626736045, 50.176930077860845), (12.334553826997592, 50.17174892257475), (12.512047935363967, 50.39725816952898),
   (12.819380927587815, 50.45979531680047), (12.912391784952945, 50.42372909162435), (12.937308320622549, 50.406328326990774), (12.947972824086605, 50.40427966057791), (13.370982951801807, 50.65054589707552),
   (13.464767584956121, 50.6018925959666), (13.526820353776372, 50.70501677580364), (13.855192645324394, 50.727109704649195), (14.387635007249855, 50.899087975929376), (14.258647344299561, 50.98753751504432),
   (14.301858340490071, 51.05504617034455), (14.508211954118451, 51.043154596807256), (14.59917754360879, 50.98711100167145), (14.564810750215997, 50.91835296052079), (14.650215415109358, 50.93153456746637),
   (14.618868455215079, 50.85771029809183), (14.793911832764934, 50.82014753807846), (15.041789401659628, 51.274035224546026), (14.949088456067443, 51.47126488866371), (14.729118627322862, 51.53143962304023),
   (14.7575478735356, 51.66150403168575), (14.590144372564794, 51.82100998951944), (14.759042582749032, 52.06476202734575), (14.681595447571908, 52.11665177535917), (14.715746889307525, 52.23588355048554),
   (14.576039609740235, 52.288366710755156), (14.534357221808364, 52.39500777668758), (14.63911057749118, 52.57299886300717), (14.12292694822014, 52.83765710679012), (14.143657774686574, 52.96136543636584),
   (14.348570677994417, 53.05471830950245), (14.450570288887775, 53.26224993248843), (14.30255731786599, 53.55340466159589), (14.283595832881417, 53.772302946323975), (13.822463699939874, 53.847704554621544),
   (13.914106243308538, 53.921857458754744), (13.743604331474785, 54.02871742866648), (13.808750796911795, 54.10045250547881), (13.7062583845169, 54.17118193160093), (13.489404453952432, 54.083845783755976),
   (13.142765860556096, 54.2536547023027), (13.178427336852167, 54.26927398011014), (13.132873048252634, 54.27941493335254), (13.138935061603831, 54.319290755963664), (13.393587180304166, 54.220971308479136),
   (13.350821423170203, 54.269835443950626), (13.57531796425269, 54.352998349893994), (13.723904396071077, 54.273407259737326), (13.766909407942876, 54.34183448887063), (13.57052592673017, 54.45765298249921),
   (13.661158812029612, 54.57578947060762), (13.425207789507908, 54.57776585760831), (13.377190829792994, 54.63485405846808), (13.429096601926688, 54.68488300844416), (13.250612882278745, 54.660459153766965),
   (13.160936899918056, 54.559083360308875), (13.28297271262062, 54.64558892578833), (13.245451284549176, 54.5578326488577), (13.368071411966127, 54.61469746081923), (13.446130971966852, 54.55160125899096),
   (13.511133452346996, 54.566114026241394), (13.557162122533953, 54.438539184735646), (13.345708024605699, 54.52048608046829), (13.368973991356654, 54.57891966081377), (13.304904763544073, 54.51411689467898),
   (13.144180806199302, 54.546965665732955), (13.270112698742082, 54.47988865533722), (13.149423426549959, 54.42813433982798), (13.260337438621946, 54.38045384347015), (13.129103578567367, 54.32114076573379),
   (13.015778021838287, 54.43939322591672), (12.809663608988387, 54.34360109191692), (12.668525237349854, 54.408133446301576), (12.42937122989838, 54.29356120888275), (12.436138349505256, 54.37908880570387),
   (12.693160014523073, 54.432254058577996), (12.926951675779264, 54.42796397405823), (12.514359690347476, 54.484348643375256), (12.100993421009605, 54.16928820758477), (11.682936883230639, 54.1532711821194),
   (11.552519675043015, 54.09268650258008), (11.626244382272489, 54.09015710806415), (11.452965579797706, 53.894822219270054), (11.179984660863454, 54.01516631277519), (10.898693904600066, 53.95665538983445),
   (10.760186530184285, 54.02460718607284), (11.09341311572274, 54.19866713068359), (11.07191809484425, 54.34224630552262), (11.125609497880077, 54.373613504659545), (11.118558230278154, 54.39442093987452),
   (10.706974464899702, 54.30503645926524), (10.321948337845583, 54.43574765223241), (10.174162085012256, 54.34574538594086), (10.199382745317507, 54.45556154343052), (10.134027446509492, 54.48433790277204),
   (9.85537006887364, 54.456283168433515), (10.027329336579669, 54.55311722278168), (10.036023455118425, 54.68841701763131), (9.909510390991784, 54.79991988959131), (9.845578708190885, 54.75603640555573),
   (9.591910925348998, 54.886970216702196), (9.343610061754987, 54.80024799285064), (8.94793864795031, 54.90256336560691), (8.638003412527496, 54.911251855288924), (8.42740949814987, 54.877399149451264),
   (8.352821841055174, 54.96785703207644), (8.46419569987171, 55.04571479500186), (8.415419699759411, 55.05866217082257), (8.297902985734254, 54.91065659132869), (8.278555583673324, 54.752208180463185),
   (8.340561373708454, 54.88057457195896), (8.414381016970424, 54.84724070929133), (8.61261435866578, 54.87846776077862), (8.686209753277524, 54.73034082227116), (8.892967769546999, 54.59677687545252),
   (8.807015453500412, 54.47015896026784), (8.983598917356176, 54.52404630129581), (9.018963414130326, 54.4747456609835), (8.660693777196116, 54.39516479791712), (8.6025729405888, 54.31421651837769),
   (8.841787401367526, 54.278455117509736), (8.80886763603263, 54.17103733680369), (8.985125080358216, 54.060986664003394), (8.804773173842932, 54.023438800360395), (8.914904897143344, 53.92489909541695),
   (9.275123379340528, 53.857269302336476), (8.61491387027894, 53.88211822337735), (8.483191313228588, 53.694068058094444), (8.556142860704483, 53.526062883695204), (8.28300703264847, 53.61001675866652),
   (8.226915998274762, 53.52008371229357), (8.315820116805014, 53.461673297584696), (8.249573034967625, 53.398362336752484), (8.071310815641652, 53.46743230775485), (8.170974825128098, 53.54047017575806),
   (8.00872870406647, 53.71213492358788), (7.296326719002934, 53.680098653130415), (7.090335704828988, 53.579652246054366), (7.129998987962949, 53.532359981627906), (7.034145450951826, 53.545670230296935),
   (7.012918474807805, 53.34466909190513), (7.265760915633791, 53.32719714862958), (7.191115728008469, 53.31760156675898), (7.217281326671923, 53.00698383493608), (7.087323855511556, 52.84992858723456),
   (7.055577137075261, 52.64336989040817), (6.75264448085818, 52.64813255966993), (6.766621548046032, 52.561512582439775), (6.680868309524897, 52.553336398755846), (6.753103465152833, 52.46379697395122),
   (6.991802701633446, 52.467452567881764), (7.065868436279345, 52.24125898091728), (6.694652945177324, 52.06980283263465), (6.830419114950246, 51.98620651731699), (6.732607205258375, 51.898707142520664),
   (6.401752481791675, 51.82729228551062), (6.11804667081725, 51.901669637044165), (6.166407642798504, 51.840834718447525), (5.945029570158567, 51.82365634356546), (6.212084663676308, 51.513378407087885),
   (6.226129918361096, 51.36051764687198), (6.072656924877208, 51.24258730406086), (6.175415266801167, 51.15847977362273), (5.867096631104687, 51.04667907857669), (6.093924751825909, 50.921325464498636),
   (5.974861423316099, 50.79797295258976), (6.266895404299532, 50.64152787241346), (6.196995433692337, 50.531031950664044), (6.351259021186934, 50.48826779162533), (6.407870066950068, 50.33513396126335),
   (6.175687402551659, 50.235415978964284), (6.112562492565019, 50.05922877348917), (6.322636569122437, 49.838032009569645), (6.529314320899232, 49.8093868550194), (6.357852033844324, 49.573820737836854),
   (6.367083526083889, 49.46951204834244), (6.550815842976817, 49.425379524945185), (6.738600535586272, 49.16367513140227), (6.938108243600595, 49.222433998330416), (7.052445915346766, 49.11275784459889),
   (7.293176904690426, 49.11490732950972), (7.445646679250899, 49.18415102974626), (7.631201346365117, 49.05488570651022), (8.232632820348442, 48.9665714470147), (7.839740454658283, 48.64127586299071),
   (7.744638258948555, 48.32781093512659), (7.577585310114046, 48.12065583849619), (7.621952269681572, 47.97280642807425), (7.511871830220855, 47.696037521514484), (7.604484107155884, 47.57779110477571),
   (7.693596586013655, 47.60053085751309), (7.667618493298903, 47.536221133898266), (8.582542232737921, 47.59610411291453), (8.60662329822668, 47.67212011105431), (8.405559327800802, 47.67451554525627),
   (8.56785207691189, 47.80848124891054), (8.893659067611907, 47.6521787220704), (8.989864116335923, 47.743465936104144), (9.21772790891589, 47.66612201881617), (9.03953653098614, 47.81748197511314),
   (9.680775632889208, 47.542264915293686), (9.80050512701007, 47.59598448329734), (9.97089855812203, 47.54568043790192), (10.091470374358323, 47.45907615923744), (10.09978048058474, 47.35473600336394),
   (10.23619637995904, 47.381860769459024), (10.172036399760088, 47.27904122237459), (10.232299537493702, 47.27047759546474), (10.436472139577631, 47.38064780435275), (10.454459535579133, 47.55573834324997)]);
   

   Теперь с помощью функции pointInPolygon можно узнать, сколько базовых станций сотовой связи находится на материковой части Германии:

   Query

   SELECT formatReadableQuantity(count()) FROM cell_towers
   WHERE pointInPolygon((lon, lat), (SELECT * FROM germany))
   

   Response

   2.62 million
   
5. Необязательно: визуализация данных с помощью ClickHouse Agents

   Далее визуализируем эти данные. Это необязательный пример: ClickHouse Agents — функциональность в статусе бета, работающая на основе модели, поэтому план, трассировка вызовов инструментов и результат могут отличаться от запуска к запуску. ClickHouse Agents позволяет легко выполнять запросы к данным ClickHouse и исследовать их в формате диалога — без необходимости самостоятельно писать SQL или логику оркестрации. Агент определяет ваше намерение, планирует шаги, вызывает настроенные вами инструменты и возвращает результаты.

   Чтобы открыть ClickHouse Agents, нажмите ClickHouse agents под названием вашей организации в нижней части левого меню. Отправлять сообщения ClickHouse Agent можно через чат. Введите «Visualize cell towers within Germany on a map using the default.cell_towers and default.germany tables» и отправьте сообщение. В одном из запусков агент выполнил следующие шаги:

   • Нашёл в mcc_country_dict мобильный код страны (MCC) для Германии — 262.
   • С помощью pointInPolygon подсчитал базовые станции с mcc = 262, попадающие внутрь полигона из germany, — получилось 2 588 635 станций. Это немного меньше, чем в подсчёте выше, поскольку дополнительный фильтр по mcc исключает станции внутри полигона, зарегистрированные в другой стране.
   • Вместо того чтобы отображать 2,6 миллиона отдельных точек, сгруппировал станции в сетку 0,1° на 0,1°, округлив lon и lat, — получилась 4 821 ячейка. Затем проверил, что сумма значений по ячейкам в точности равна 2 588 635, то есть при группировке ничего не потерялось и не было учтено дважды.
   • Получил полигон из germany и нарисовал его контур поверх сетки, используя логарифмическую цветовую шкалу, чтобы были видны как плотные городские, так и разреженные сельские ячейки.

   Откройте сгенерированный файл, чтобы увидеть результат. Яркие скопления — это крупнейшие агломерации: Берлин, Гамбург, Мюнхен и Франкфурт.

​

Дальнейшие шаги

В этом руководстве вы загрузили набор данных о базовых станциях сотовой связи OpenCelliD в ClickHouse Cloud, изучили его схему и выполнили агрегирующие запросы по десяткам миллионов строк. Вы создали словарь, чтобы дополнить числовой столбец mcc названиями стран, сохранили контур Германии в виде полигона и с помощью pointInPolygon подсчитали количество вышек внутри него. Наконец, с помощью ClickHouse Agents вы превратили этот вопрос в карту плотности, не написав самостоятельно ни кода агрегации, ни кода визуализации.

Что можно сделать дальше:

• Задать агенту уточняющие вопросы. Например, спросите, чем различается плотность вышек разных типов радиосвязи, таких как LTE и GSM, или в каких городах больше всего вышек NR. Сохраняйте часто используемые промпты в библиотеке промптов или создайте специализированного агента в Конструкторе агентов.
• Сравнить страны. Добавьте полигоны других стран и выполните для каждой из них тот же запрос с pointInPolygon. Созданный вами словарь позволит подписать результаты названиями стран.
• Сгруппировать вышки по ячейкам с помощью H3 или Geohash. Агент использовал простую сетку на основе округления. В ClickHouse также есть функции H3 и Geohash, которые формируют иерархические ячейки, хорошо подходящие для масштабируемых карт.
• Изучить другие датасеты. Датасет NYC taxi тоже содержит координаты посадки и высадки пассажиров, а в каталоге примеров датасетов вы найдёте множество других.