Geo Data using the Cell Tower Dataset

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/getting-started/example-datasets/cell-towers.md)

## Goal[​](#goal "Direct link to Goal")

In this guide you will learn how to:

- Load the OpenCelliD data in ClickHouse
- Connect Apache Superset to ClickHouse
- Build a dashboard based on data available in the dataset

Here is a preview of the dashboard created in this guide:

## Get the dataset[​](#get-the-dataset "Direct link to Get the dataset")

This dataset is from [OpenCelliD](https://www.opencellid.org/) - The world's largest Open Database of Cell Towers.

As of 2021, it contains more than 40 million records about cell towers (GSM, LTE, UMTS, etc.) around the world with their geographical coordinates and metadata (country code, network, etc.).

OpenCelliD Project is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License, and we redistribute a snapshot of this dataset under the terms of the same license. The up-to-date version of the dataset is available to download after sign in.

- ClickHouse Cloud- Self-managed

### Load the sample data[​](#load-the-sample-data "Direct link to Load the sample data")

ClickHouse Cloud provides an easy-button for uploading this dataset from S3. Log in to your ClickHouse Cloud organization, or create a free trial at [ClickHouse.cloud](https://clickhouse.cloud).

Select your service, followed by `Data sources` -> `Predefined sample data`.

Choose the **Cell Towers** dataset from the **Sample data** tab, and **Load data**:

### Examine the schema of the cell\_towers table[​](#examine-the-schema-of-the-cell_towers-table "Direct link to Examine the schema of the cell_towers table")

```
DESCRIBE TABLE cell_towers
```

SQL console

If you need a SQL client connection, your ClickHouse Cloud service has an associated web based SQL console; expand **Connect to SQL console** below for details.

Connect to SQL console

From your ClickHouse Cloud services list, click on a service.

![Connect to SQL Console](/docs/assets/ideal-img/cloud-connect-to-sql-console.b5c58f3.2048.png)

This will redirect you to the SQL console.

![SQL Console](/docs/assets/ideal-img/createservice8.a78154b.2048.png)

This is the output of `DESCRIBE`. Down further in this guide the field type choices will be described.

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

1. Create a table:

```
CREATE TABLE cell_towers
(
    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
)
ENGINE = MergeTree ORDER BY (radio, mcc, net, created);
```

2. Import the dataset from a public S3 bucket (686 MB):

```
INSERT INTO cell_towers SELECT * FROM s3('https://datasets-documentation.s3.amazonaws.com/cell_towers/cell_towers.csv.xz', 'CSVWithNames')
```

## Run some example queries[​](#examples "Direct link to Run some example queries")

1. A number of cell towers by type:

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

5 rows in set. Elapsed: 0.011 sec. Processed 43.28 million rows, 43.28 MB (3.83 billion rows/s., 3.83 GB/s.)
```

2. Cell towers by [mobile country code (MCC)](https://en.wikipedia.org/wiki/Mobile_country_code):

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

10 rows in set. Elapsed: 0.019 sec. Processed 43.28 million rows, 86.55 MB (2.33 billion rows/s., 4.65 GB/s.)
```

Based on the above query and the [MCC list](https://en.wikipedia.org/wiki/Mobile_country_code), the countries with the most cell towers are: the USA, Germany, and Russia.

You may want to create a [Dictionary](/docs/sql-reference/statements/create/dictionary) in ClickHouse to decode these values.

## Use case: incorporate geo data[​](#use-case "Direct link to Use case: incorporate geo data")

Using the [`pointInPolygon`](/docs/sql-reference/functions/geo/coordinates#pointinpolygon) function.

1. Create a table where we will store polygons:

- ClickHouse Cloud- Self-managed

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

```
CREATE TEMPORARY TABLE
moscow (polygon Array(Tuple(Float64, Float64)));
```

2. This is a rough shape of Moscow (without "new Moscow"):