Skip to main content
In this tutorial you’ll explore how ClickHouse can be used for geo data using the cell tower dataset. You’ll load the dataset into ClickHouse Cloud, examine its schema and run aggregation queries over tens of millions of rows. You’ll then enrich the data with a dictionary, use the pointInPolygon function to count the towers inside Germany, and use ClickHouse Agents to visualize the result on a map.

​
Prerequisites

For this tutorial, you’ll need:
1

Load the sample data

The dataset used in this tutorial is from , the world’s largest Open Database of Cell Towers. As of 2021, it contains tens of millions of records about cell towers around the world with their geographical coordinates and metadata such as country code and network.
  1. In ClickHouse Cloud, from the left hand menu, select your service from the dropdown
  2. Select Data sources
  3. Click the Add sample data card
  4. Select the Cell Towers (1.1 GB) dataset
  5. Use default as the destination database and click Import dataset
You should see an entry under the Data upload history with a status of success
2

Examine the schema

  1. Select SQL console from the the left hand menu
  2. Click the + tab next to the home icon to create a new query
  3. In the SQL editor type the following query, then click Run:
You should see the following result table:
Each entry in the table corresponds to a cell tower in an actual location on earth, given by the coordinates lon and lat.
3

Run basic queries

Run the following query to view the number of cell towers by type:
Next, check the number of cell towers by Mobile Country Code (MCC):
You can see that the countries with the most cell towers have MCCs: 310, 262 and 250. You can use a dictionary to replace the numeric mcc column values with country names:
With the dictionary created, you can now use it to return the same list with the country name alongside the MCC:
Query
Response
4

Incorporate Geo data

You might also be interested in knowing how many cell towers are within a specific geographical area. ClickHouse has many useful geo functions for this use case, such as the pointInPolygon function.Let’s imagine you’re interested in seeing how many cell towers are located within Germany. Create a table called germany which contains a single column of type polygon:
Now insert the co-ordinates for the outline of (mainland) Germany into the table:
You can now check how many cell towers are located within mainland Germany using the pointInPolygon function:
Query
Response
5

Optional: Visualize the data with ClickHouse Agents

Next we’d like to visualize this data. This is an optional example run: ClickHouse Agents is model-driven beta functionality, so its plan, tool trace and output can differ between runs. ClickHouse Agents lets you easily query and explore your ClickHouse data through conversation, without writing SQL or orchestration logic yourself. The agent interprets your intent, plans steps, calls the tools you’ve configured, and returns the results to you.Click ClickHouse agents underneath your organization name in the bottom of the left hand menu to open ClickHouse Agents. You can message the ClickHouse agent through the chat. Type “Visualize cell towers within Germany on a map using the default.cell_towers and default.germany tables” and hit send. On one run, the agent carried out the following steps:
  • It looked up Germany’s Mobile Country Code in mcc_country_dict and found MCC 262.
  • It counted the towers with mcc = 262 that fall inside the polygon in germany using pointInPolygon, which gave 2,588,635 towers. This is slightly fewer than the count above because the extra mcc filter excludes towers inside the polygon that are registered to another country.
  • Rather than plotting 2.6 million individual points, it grouped the towers into a 0.1° by 0.1° grid by rounding lon and lat, which produced 4,821 cells. It checked that the cell counts sum back to exactly 2,588,635, so nothing was lost or double counted in the binning.
  • It retrieved the polygon from germany and drew it as an outline over the grid, using a logarithmic color scale so that dense urban cells and sparse rural cells are both visible.
Open the generated file to see the result. The bright clusters are the major metropolitan areas: Berlin, Hamburg, Munich and Frankfurt.

​
Next steps

In this tutorial you loaded the OpenCelliD cell tower dataset into ClickHouse Cloud, examined its schema, and ran aggregation queries over tens of millions of rows. You created a dictionary to enrich the numeric mcc column with country names, stored the outline of Germany as a polygon, and used pointInPolygon to count the towers inside it. Finally, you used ClickHouse Agents to turn that question into a density map without writing the aggregation or plotting code yourself. From here you could:
  • Ask the agent follow-up questions. Try asking how density differs between radio types such as LTE and GSM, or which cities have the most NR towers. Save prompts you reuse in the prompt library, or build a specialised agent in the Agent Builder.
  • Compare countries. Insert polygons for other countries and run the same pointInPolygon query against each one. The dictionary you created lets you label the results with country names.
  • Bucket towers with H3 or geohash. The agent used a simple rounding grid. ClickHouse also provides H3 and geohash functions, which produce hierarchical cells that suit zoomable maps.
  • Explore other datasets. The NYC taxi dataset also contains pickup and dropoff coordinates, and the sample datasets index lists many more.
Last modified on September 22, 2026