Skip to main content

Geo Data using the Cell Tower Dataset

Load the OpenCelliD data in databend

Tutorials

Goal

In this guide you will learn how to:

  • Load the OpenCelliD data in Databend
  • Build a dashboard based on data available in the dataset

Get the Dataset

This dataset is from OpenCelliD - 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.

Columns present in database

FieldDescription
RadioThe generation of broadband cellular network technology (e.g., LTE, GSM)
MCCMobile country code. This info is publicly shared by International Telecommunication Union
LAC/TAC/NIDLocation Area Code
CIDThis is a unique number used to identify each Base transceiver station or sector of BTS
LongitudeLongitude is a geographic coordinate that specifies the east-west position of a point on the Earth's surface
LatitudeLatitude is a geographic coordinate that specifies the north–south position of a point on the Earth's surface
RangeApproximate area within which the cell could be. (In meters)
SamplesNumber of measures processed to get a particular data point
Changeable=1The location is determined by processing samples
Changeable=0The location is directly obtained from the telecom firm
CreatedWhen a particular cell was first added to the database (UNIX timestamp)
UpdatedWhen a particular cell was last seen (UNIX timestamp)
AverageSignalTo get the positions of cells, OpenCelliD processes measurements from data contributors. Each measurement includes GPS location of device + Scanned cell identifier (MCC-MNC-LAC-CID) + other device properties (Signal strength). In this process, signal strength of the device is averaged. Most 'averageSignal' values are 0 because OpenCelliD simply didn’t receive signal strength values.
1

Load the sample data
Copy Link

Databend Cloud provides an easy-button for uploading this dataset from S3. Log in to your Databend Cloud organization, or create a free trial at Databend.cloud.

  1. Open the Loda Data menu from your Databend Cloud service and choose Load Data:
  2. Load sample data and select dataset Cell Towers in the Netherlands
  3. Select the target database, optionally provide a name for the current table, and then click the confirm button.
  4. After waiting for successful data loading, click 'Preview the table data' to verify if the data has been imported successfully.
2

Examine the schema of the cell_towers table
Copy Link

  1. Select "Data" from the left menu to access the Database Management.
  2. Choose the Data table created in the previous step to access its details.
  3. Click on "Table Definition" and "Columns" to view the schema definition of the table.
  4. Click "Data Preview" to check the imported data (providing a preview of up to the first 10,000 rows of data).

Build visualizations with Worksheet

Databend Cloud worksheets have built-in visualization capabilities that allow you to quickly turn queried data into visual charts.

Percentage of Cellular Network Technology Development

For example, we can examine the distribution of the development of broadband cellular network technologies (e.g. LTE, GSM) in the dataset, and use a pie chart to display the percentage of each development stage.

  1. Create a new Worksheet and query the previously created data tables using SQL statements.
SELECT 
radio,
COUNT(*) AS total
FROM cell_towers_3624
GROUP BY radio
  1. Switch to the Chart view and select Pie chart to automatically convert the data into a visualized chart.

Alt text