# Identify buildings in areas with a deficit of cell network antennas

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FhB2W9xXbzzo0kEuXMe3S%2Fintermediate%20banner.png?alt=media&#x26;token=4acd2cc7-c7e8-46c0-9669-6f6b73c030dd" alt="" width="175"><figcaption></figcaption></figure></div>

In this tutorial, we will learn to identify areas with a deficit of cell network antennas. We will identify busy areas, e.g., areas with a lot of human activity, to later verify if the number of antennas in these locations are enough to satisfy demand while providing a high quality service.

***

## You'll need...

This analysis will be based on three main sources:&#x20;

* [Overture Maps](https://carto.com/blog/overture-maps-data-now-on-the-cloud-use-it-with-carto): contains topographic data standardized across global administrative boundaries. We will use their *Buildings* dataset, made up of over 2.3 billion features.
* [CARTO Spatial Features](https://carto.com/blog/announcing-new-carto-spatial-features-data): provides derived variables across a wide range of themes including demographics, points of interest, and climatology data with global coverage. We will focus on the derived human activity index, a proxy for busy areas.
* [OpenCelliD](https://www.opencellid.org/#zoom=16\&lat=37.77889\&lon=-122.41942): it is an open database of cell towers located worldwide.

We will be running the analysis for the city of Madrid, but if you'd like to replicate it for another study areas, make sure to subscribe to the [Overture Maps](https://carto.com/spatial-data-catalog/dataset/ovr_building_f7cf4ce) and [Spatial Features](https://academy.carto.com/working-with-geospatial-data/geospatial-data-the-basics/what-is-location-data) datasets, which are available **globally** in our [Data Observatory](https://carto.com/data-observatory), and to update your cell towers data properly (OpenCelliD data can be downloaded from [here](https://www.opencellid.org/downloads.php)).

***

## Setting up your workflow\...

1. Sign in to CARTO at [app.carto.com](http://app.carto.com)
2. Head to the **Workflows** tab and click on **Create new workflow**
3. Choose the **CARTO Data Warehouse** connection or any connection to you Google BigQuery project.

Now, let’s dive into the step-by-step process of creating a workflow to pinpoint high-traffic areas that are lacking mobile phone antennas, and discover which buildings are the best candidates for antenna installation.

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FZjLVbD1CjmRr132DYnc4%2Foverture-workflow.png?alt=media&#x26;token=d30a6c2a-ec3b-489c-8cbc-475d1e251ee9" alt=""><figcaption><p>Workflow to identify buildings in areas with a deficit of mobile phone antennas.</p></figcaption></figure>

***

## Step 1: Load the data sources

Let's import the data into the canvas. First, we will load the Spatial Features dataset from the **Sources** left-side menu by selecting **Data Observatory >  CARTO > Spatial Features - Spain (H3 Resolution 8) \[v3]** and drag-and-drop it into the canvas. Make sure you are subscribed to this dataset (you can follow [this](https://docs.carto.com/carto-user-manual/data-observatory/subscribing-to-public-and-premium-datasets) tutorial to learn how).

Now, from the **Components** left-side menu, we will use the [Get Table by Name](https://docs.carto.com/carto-user-manual/workflows/components/input-output#get-table-by-name) component to load some data we've made publicly available in BigQuery.&#x20;

* First, we will load a sample of the Overture Maps's buildings data, which contains all the building geometries in Madrid, by typing `cartobq.docs.buildings_mad` as the **source table FQN.**&#x20;

{% hint style="info" %}
You can also subscribe to the Overture Maps' [Buildings - Global](https://carto.com/spatial-data-catalog/browser?dataset=ovr_building_f7cf4ce) dataset, publicly available in the CARTO [Data Observatory](https://carto.com/data-observatory), then drag-and-drop the full source into the canvas as we previously did for Spatial Features.
{% endhint %}

* Secondly, we will import the geometry of our Area of Interest (AOI), which will help focus our analysis only within Madrid. The **FQN** of this data is `cartobq.docs.madrid_districts`.

Now, we will import the cell towers data using the [Import from URL](https://docs.carto.com/carto-user-manual/workflows/components/input-output#import-from-url) component. We have filtered the OpenCelliD data to keep only the 4G mobile phone antennas we are interested in, and made the sample publicly accessible through a Google Cloud Storage bucket. Copy the following URL to import the source: <https://storage.googleapis.com/data_science_public/workflow_templates/cell_towers_madrid.csv>

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FQfUIkMlx9j6yyuEqHwcm%2Foverture-load.png?alt=media&#x26;token=0d75c7d3-710f-44df-940b-9da3e9e2754a" alt=""><figcaption><p>Load the data sources.</p></figcaption></figure>

***

## Step 2: Bring it all to H3

Before we begin with the analysis, we need to standardize all our data to a common geographical reference. This way, we can seamlessly integrate the data, allowing for consistent spatial analysis and ensuring that the results are reliable. We will use Spatial Indexes as our reference system: since the Spatial Features dataset is already in H3, we will convert the other sources to match this format. If you want to learn more about Spatial Indexes, take a look at out [Spatial Indexes 101 Report](https://go.carto.com/report-spatial-indexes-101)!

To **transform the telco data into H3**, we will count the number of cell towers within each H3 cell:

1. Extract the H3 associated to each cell tower location coordinates by connecting the cell tower data source to the [H3 from GeoPoint](https://docs.carto.com/carto-user-manual/workflows/components/spatial-indexes#h3-from-geopoint) component. Select  `geom` as the **points column** and `8` as the **resolution**.
2. Use the [Group by](https://docs.carto.com/carto-user-manual/workflows/components/aggregation#group_by) component to **group by** `h3` and **aggregate** the cell tower `id`'s using `COUNT`. &#x20;
3. Rename the resulting `id_count` column as `cell_towers` using the [Rename Column](https://docs.carto.com/carto-user-manual/workflows/components/data-preparation#rename_column) component.

Next, we will **enrich the Area of Interest with all the necessary data**:

1. Connect the AOI source to the [H3 Polyfill](https://docs.carto.com/carto-user-manual/workflows/components/spatial-indexes#h3-polyfill) component to generate a table with indices of all H3 cells of **resolution** `8` included within the AOI geo-column `geom`. Use the `Intersects` mode.
2. Then, [Join](https://docs.carto.com/carto-user-manual/workflows/components/joins#join) the polyfilled AOI with the Spatial Features data using the `h3` column as key for both sources. Select `Inner` as **join type** to keep only those H3 cells that are common to both tables. Then, eliminate the `h3_joined` column using the [Drop Columns](https://docs.carto.com/carto-user-manual/workflows/components/data-preparation#drop-columns) component.
3. Now, use another [Join](https://docs.carto.com/carto-user-manual/workflows/components/joins#join) to combine the resulting table with the aggregated cell tower counts. Again, use the `h3` columns as keys, but make sure to select the appropriate **join type**, as we want to fill in the H3 cells in Madrid with cell tower information. In this case, we have connected the AOI as the main table, so, we will perform a `Left` join.

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FuYdR34zfTn1PIjrFoqH8%2Foverture-h3.png?alt=media&#x26;token=56ccf885-e269-4357-b354-be5759eac6fc" alt=""><figcaption><p>Bring it all to H3.</p></figcaption></figure>

***

## Step 3: Evaluate building’s potential for new cell tower installations

The aim of the analysis is to identify busy areas, i.e., areas with a lot of human activity, to later verify if the number of antennas in these locations are enough to satisfy demand while providing a high quality service. To do this, we will:

1. **Select the variables of interest.** Since we are looking for areas with high human activity and low number of cell towers, we need to reverse the cell tower counts so that high values mean low counts. To do this, use the [Create Column](https://docs.carto.com/carto-user-manual/workflows/components/data-preparation#create-column) component to compute `cell_towers_inv`, a proxy for the lack of antennas, by typing the query below, then use the [Edit Schema](https://docs.carto.com/carto-user-manual/workflows/components/data-preparation#edit-schema) component to select the variables `h3`, `cell_towers_inv` and `human_activity_index`:

```sql
MAX(cell_towers) OVER() - COALESCE(cell_towers,0)
```

2. **Create a spatial score that combines high human mobility and lack of antennas information.** Use the [Composite Score Unsupervised](https://docs.carto.com/carto-user-manual/workflows/components/statistics#composite-score-unsupervised) component with the `CUSTOM_WEIGHTS` **scoring method** to combine both variables using the same weights through a weighted average. Select `STANDARD_SCALER` as the **scaling method** and a `LINEAR` **aggregation**. For more details about Composite Scores, take a look at our [step-by-step tutorial](https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/how-to-create-a-composite-score-with-your-spatial-data)!
3. **Compute the Getis Ord statistic to identify statistically significant spatial clusters** of high values (hot spots, lack of coverage) and low values (cold spots, sufficient coverage). Use the [Getis Ord](https://docs.carto.com/carto-user-manual/workflows/components/statistics#getis-ord) component with a `uniform` **kernel** of **size** `1`.
4. **Identify potential buildings to install new antennas** using the [Enrich Polygons](https://docs.carto.com/carto-user-manual/workflows/components/data-enrichment#enrich-polygons) component. Notice that we need to work with geometries here, so we will first get the boundaries of the Getis Ord H3 cells using the [H3 Boundary](https://docs.carto.com/carto-user-manual/workflows/components/spatial-indexes#h3-boundary) component. Enrich the data by aggregating the `gi` value with the `AVG` and the `p_value`, that represents the significance of the statistic, with the `MAX`.

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FAKB2AwlBdTUj8f3R871f%2Foverture-hotspot.png?alt=media&#x26;token=7f6f5db2-8b01-4595-b748-275a930a9260" alt=""><figcaption><p>Evaluate building's potential for new cell tower installations.</p></figcaption></figure>

***

## Step 4: Analyze the results in a map

To visualize the results correctly, we will use the [Create Vector Tileset](https://docs.carto.com/carto-user-manual/workflows/components/tileset-creation#create-vector-tileset) component to create a tileset, which allows to process and visualize very large spatial datasets stored in BigQuery. Use `10` and `16` as minimum and maximum zoom levels, respectively.

<figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FsBKB2fUw3S8cIYamYIBn%2Foverture-tileset.png?alt=media&#x26;token=a891feee-9391-49c5-9d3a-db0d708d1c34" alt=""><figcaption><p>Create a tileset for visualization.</p></figcaption></figure>

The following map allows to identify busy areas with a shortage of mobile phone antennas and determine the most suitable buildings for antenna placement.&#x20;

{% embed url="<https://clausa.app.carto.com/map/8b7754af-4810-471b-836c-d1b99742021c>" %}

We can see that the busy city center of Madrid is fully packed of cell towers, enough to satisfy demand. Also, locations with little human activity (like El Pardo park) have also enough network capacity to provide service. However, the outskirts of the city seem to be lacking antennas, based on the overall human activity and cell tower presence patterns in Madrid.