Análisis geoespacial con el conjunto de datos de torres de telefonía móvil

Aprenda a cargar el conjunto de datos de torres de telefonía móvil de OpenCelliD en ClickHouse Cloud, enriquecerlo con un diccionario, consultarlo con funciones geoespaciales y visualizarlo con ClickHouse Agents

En este tutorial verá cómo se puede usar ClickHouse con datos geográficos a partir del conjunto de datos de torres de telefonía móvil. Cargará el conjunto de datos en ClickHouse Cloud, examinará su esquema y ejecutará consultas de agregación sobre decenas de millones de filas. A continuación, enriquecerá los datos con un diccionario, usará la función pointInPolygon para contar las torres ubicadas en Alemania y recurrirá a ClickHouse Agents para visualizar el resultado en un mapa.

​

Requisitos previos

Para este tutorial, necesitará:

• Una cuenta de ClickHouse Cloud (300 USD en créditos gratuitos al registrarse)
• Un servicio de ClickHouse Cloud

1. Cargue los datos de muestra

   El conjunto de datos utilizado en este tutorial procede de OpenCelliD, la base de datos abierta de torres de telefonía móvil más grande del mundo. En 2021, contenía decenas de millones de registros de torres de telefonía móvil de todo el mundo, con sus coordenadas geográficas y metadata como el código de país y la red.

   1. En ClickHouse Cloud, en el menú de la izquierda, seleccione su servicio en el menú desplegable
   2. Seleccione Data sources
   3. Haga clic en la tarjeta Add sample data
   4. Seleccione el conjunto de datos Cell Towers (1.1 GB)
   5. Use default como base de datos de destino y haga clic en Import dataset

   Debería ver una entrada en Data upload history con el estado success

2. Examine el esquema

   1. Seleccione SQL console en el menú de la izquierda
   2. Haga clic en la pestaña + situada junto al icono de inicio para crear una nueva consulta
   3. En el editor SQL, escriba la siguiente consulta y haga clic en Run:

   DESCRIBE TABLE cell_towers
   

   Debería ver la siguiente tabla de resultados:

   ┌─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                                                                 │
   └───────────────┴───────────────────────────────────────────────────────────────────────┴
   

   Cada entrada de la tabla corresponde a una torre de telefonía móvil situada en una ubicación real de la Tierra, indicada por las coordenadas lon y lat.

3. Ejecute consultas básicas

   Ejecute la siguiente consulta para ver el número de torres de telefonía móvil por tipo:

   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 │
   └───────┴──────────┘
   

   A continuación, compruebe el número de torres de telefonía móvil por código de país móvil (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 │
   └─────┴─────────┘
   

   Puede ver que los países con más torres de telefonía móvil tienen los MCC 310, 262 y 250. Puede usar un diccionario para sustituir los valores numéricos de la columna mcc por los nombres de los países:

   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)
   

   Una vez creado el diccionario, puede usarlo para devolver la misma lista con el nombre del país junto al 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. Incorporar datos geográficos

   También puede que le interese saber cuántas torres de telefonía móvil hay dentro de un área geográfica determinada. ClickHouse ofrece numerosas funciones geoespaciales útiles para este caso de uso, como la función pointInPolygon.

   Supongamos que quiere saber cuántas antenas de telefonía móvil hay en Alemania. Cree una tabla llamada germany con una única columna de tipo polygon:

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

   A continuación, inserte en la tabla las coordenadas del contorno de Alemania (continental):

   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)]);
   

   Ahora puede comprobar cuántas antenas de telefonía móvil hay en la Alemania continental mediante la función pointInPolygon:

   Query

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

   Response

   2.62 million
   
5. Opcional: Visualice los datos con ClickHouse Agents

   A continuación, visualizaremos estos datos. Esta ejecución de ejemplo es opcional: ClickHouse Agents es una funcionalidad beta basada en modelos, por lo que su plan, la traza de herramientas y la salida pueden variar de una ejecución a otra. ClickHouse Agents le permite consultar y explorar fácilmente sus datos de ClickHouse mediante una conversación, sin tener que escribir usted mismo SQL ni lógica de orquestación. El agente interpreta su intención, planifica los pasos, llama a las herramientas que haya configurado y le devuelve los resultados.

   Haga clic en ClickHouse agents, debajo del nombre de su organización en la parte inferior del menú de la izquierda, para abrir ClickHouse Agents. Puede enviar mensajes al agente de ClickHouse a través del chat. Escriba “Visualize cell towers within Germany on a map using the default.cell_towers and default.germany tables” y pulse enviar. En una de las ejecuciones, el agente llevó a cabo los siguientes pasos:

   • Buscó el código móvil de país (Mobile Country Code) de Alemania en mcc_country_dict y obtuvo el MCC 262.
   • Contó las torres con mcc = 262 situadas dentro del polígono de germany mediante pointInPolygon, lo que dio como resultado 2.588.635 torres. Esta cifra es ligeramente inferior al recuento anterior porque el filtro adicional por mcc excluye las torres que, aun estando dentro del polígono, están registradas en otro país.
   • En lugar de representar 2,6 millones de puntos individuales, agrupó las torres en una cuadrícula de 0,1° por 0,1° redondeando lon y lat, lo que generó 4.821 celdas. Comprobó que la suma de los recuentos de las celdas daba exactamente 2.588.635, de modo que durante la agrupación no se perdió ninguna torre ni se contó ninguna dos veces.
   • Recuperó el polígono de germany y lo dibujó como contorno sobre la cuadrícula, con una escala de color logarítmica para que se aprecien tanto las celdas urbanas densas como las celdas rurales dispersas.

   Abra el archivo generado para ver el resultado. Los clústeres brillantes corresponden a las principales áreas metropolitanas: Berlín, Hamburgo, Múnich y Fráncfort.

​

Próximos pasos

En este tutorial cargó el conjunto de datos de torres de telefonía móvil de OpenCelliD en ClickHouse Cloud, examinó su esquema y ejecutó consultas de agregación sobre decenas de millones de filas. Creó un diccionario para enriquecer la columna numérica mcc con los nombres de los países, almacenó el contorno de Alemania como un polígono y utilizó pointInPolygon para contar las torres situadas en su interior. Por último, utilizó ClickHouse Agents para convertir esa pregunta en un mapa de densidad sin tener que escribir usted mismo el código de agregación ni el de visualización.

A partir de aquí, puede:

• Hacer preguntas de seguimiento al agente. Pruebe a preguntar cómo varía la densidad entre tipos de radio como LTE y GSM, o qué ciudades tienen más torres NR. Guarde los prompts que reutilice en la biblioteca de prompts o cree un agente especializado en el Agent Builder.
• Comparar países. Inserte polígonos de otros países y ejecute la misma consulta pointInPolygon para cada uno de ellos. El diccionario que creó le permite etiquetar los resultados con los nombres de los países.
• Agrupar torres con H3 o geohash. El agente utilizó una cuadrícula de redondeo simple. ClickHouse también ofrece funciones H3 y geohash, que generan celdas jerárquicas idóneas para mapas con zoom.
• Explorar otros conjuntos de datos. El conjunto de datos de taxis de Nueva York también contiene coordenadas de recogida y destino, y el índice de conjuntos de datos de ejemplo incluye muchos más.