# Dec 14th, 2025: \[EN\] Build a map to compare metrics by country or region with ES|QL

**URL:** https://discuss.elastic.co/t/dec-14th-2025-en-build-a-map-to-compare-metrics-by-country-or-region-with-es-ql/383243
**Category:** Advent Calendar
**Created:** [December 14, 2025, 8:00am UTC](https://discuss.elastic.co/t/dec-14th-2025-en-build-a-map-to-compare-metrics-by-country-or-region-with-es-ql/383243 "2025-12-14T08:00:14Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![Nathan\_Reese](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nathan_reese/32/84829_2.png) [@Nathan\_Reese](https://discuss.elastic.co/u/Nathan_Reese)
#### Post date: [December 14, 2025, 8:00am UTC](https://discuss.elastic.co/t/dec-14th-2025-en-build-a-map-to-compare-metrics-by-country-or-region-with-es-ql/383243/1 "2025-12-14T08:00:14Z")

</div>

![social-advent-2021-Day14](https://us1.discourse-cdn.com/elastic/original/3X/3/4/34f161c2a3c4f25f94fbd06bd3c5850d8f6df0d7.png)

Kibana map tutorial ["Build a map to compare metrics by country or region"](https://www.elastic.co/guide/en/kibana/8.19/maps-getting-started.html) creates a map using Elasticsearch’s DSL querying. Recent ES|QL functions **[LOOKUP JOIN](https://www.elastic.co/docs/reference/query-languages/esql/commands/lookup-join)** (8.18) and **[ST\_GEOTILE](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/spatial-functions#esql-st_geotile)** (9.2) allow the map in this tutorial to be re-created with ES|QL.

Below are the steps to re-create the tutorial map with ES|QL. Then, the steps go beyond what is possible in the original tutorial and normalizes the choropleth layer by country population.

# Prerequisites

- If you don’t already have Kibana, set it up with [our free trial](https://www.elastic.co/cloud/elasticsearch-service/signup?baymax=docs-body&elektra=docs).
- This tutorial requires the [web logs sample data set](https://www.elastic.co/guide/en/kibana/8.19/get-started.html).
- You must have the correct privileges for creating a map and creating an index.

# Step 1. Create a map

- Go to _Dashboards_.
- Click **Create dashboard**.
- Set the time range to _Last 7 days_.
- Click the **Add** button and select **New panel**. Finally click **Maps**

# Step 2. Add a choropleth layer

The first layer you’ll add is a choropleth layer to shade world countries by web log traffic. Darker shades will symbolize countries with more web log traffic, and lighter shades will symbolize countries with less traffic.

## Index world countries

1. Download [world countries GeoJSON](https://maps.elastic.co/#file/world_countries) file
2. In Kibana maps, click **Add layer**
3. Select **Upload file**
4. Select world countries GeoJSON file in file selector
5. Set **Index name** to **world\_countries**
6. Open **Advanced** section
7. Set **Index settings** to `{ "index.mode": "lookup" }`.  
  
 ![Screenshot of import settings](https://us1.discourse-cdn.com/elastic/original/3X/c/4/c4695ab449e388f365bf1094f7836950a217dc5b.jpeg)
8. Click **Import file**
9. When the file import finishes, click **cancel**

## Add the choropleth layer

1. Click **Add layer**
2. Select **ES|QL**
3. Set ES|QL statement to

```plaintext
FROM kibana_sample_data_logs 
| STATS count = COUNT() BY geo.dest 
| RENAME geo.dest AS iso2.keyword 
| LOOKUP JOIN world_countries ON iso2.keyword 
| RENAME iso2.keyword AS geo.dest 
| KEEP count, geometry, geo.dest

```

1. Click **Run query** in ES|QL editor
2. Unselect **Dynamically filter for data in the visible map area**  
  
 ![Screenshot of choropleth map without styling](https://us1.discourse-cdn.com/elastic/original/3X/d/1/d1892f0cd7d97cf411ad13e48dcfc4159d720e54.jpeg)
3. Click **Add and continue**
4. In **Layer settings** , set:
  1. **Name** to _Total Requests by Destination_.
  2. **Opacity** to _50%_.

5. In **Layer style**
  1. Set **Fill color** to **By value** by **count**. Select "grey to black" color gradient.
  2. Set **Border color** to "white". 4.

6. Click **Keep changes**. Your map should now look like  
  
 ![Screenshot of styled choropleth layer](https://us1.discourse-cdn.com/elastic/original/3X/7/d/7da9e5a0c7772362d5bc7221b6c3ba6eafaa2282.jpeg)

# Step 3. Add layers for the Elasticsearch data

To avoid overwhelming the user with too much data at once, you'll add two layers for the Elasticsearch data. The first layer will display individual documents when users zoom in on the map. The second layer will display aggregated data when users zoom the map out.

## Add a layer for individual documents

This layer displays web log documents as points.  
The layer is only visible when users zoom in.

1. Click **Add layer**
2. Select **ES|QL**
3. Set ES|QL statement to

```plaintext
FROM kibana_sample_data_logs 
| KEEP geo.coordinates, agent, bytes, clientip, 
       host, machine.os, request, response, timestamp 
| LIMIT 10000

```

1. Click **Run query** in ES|QL editor
2. Click **Add and continue**
3. In **Layer settings** , set:
  1. **Name** to _Actual Requests_.
  2. **Visibility** to the range [9, 24].

4. In **Layer style** , set:
  1. **Fill color** to `#2200FF` .
  2. **Border width** to _0_.

5. Click **Keep changes**. Your map should now look like  
  
 ![Screenshot 2025-11-05 at 9.43.41 AM](https://us1.discourse-cdn.com/elastic/original/3X/8/3/830c7b7b57dbb4bd818e04cb46ad59e26ff6755c.jpeg)

## Add a layer for aggregated data

You'll create a layer for aggregated data and make it visible only when the map is zoomed out. Darker colors will symbolize grids with more web log traffic, and lighter colors will symbolize grids with less traffic. Larger circles will symbolize grids with more total bytes transferred, and smaller circles will symbolize grids with less bytes transferred.

1. Click **Add layer**
2. Select **ES|QL**
3. Set ES|QL statement to

```auto
FROM kibana_sample_data_logs  
| EVAL geotile = ST_GEOTILE(geo.coordinates, 6) 
| STATS count = COUNT(geotile), 
        sumOfBytes = SUM(bytes), 
        centroid = ST_CENTROID_AGG(geo.coordinates) BY geotile

```

1. Click **Run query** in ES|QL editor
2. Click **Add and continue**
3. In **Layer settings** , set:
  1. **Name** to _Total Requests and Bytes_.
  2. **Visibility** to the range [0, 9].

4. In **Layer style** , set:
  1. **Fill color** to **By value** by _count_
  2. **Border width** to _0_.
  3. **Symbol size** to **By value** by _sumOfBytes_. Set the min size to 7 and the max size to 25.
  4. **Label** to **By value** by _count_

5. Click **Keep changes**. Your map should now look like  
  
 ![Screenshot 2025-11-05 at 10.27.26 AM](https://us1.discourse-cdn.com/elastic/original/3X/f/4/f4bf2a566a4f62560f357a6f0abfd188888943f8.jpeg)

# Step 4. Normalize choropleth layer by country population

The choropleth layer created in _step 2_ shades world countries by web log traffic. Comparing counts between countries is not a fair comparison since countries have varying populations. Large populations likely have more web traffic then small populations. Instead, you want to shade world countries by web log traffic adjusted for population.

ES|QL makes it possible to _normalize_ web log counts by taking into account the total population of each country. Instead of visualizing web log counts, we will visualize web log counts per 100,000 people. Now, we can compare normalized counts between countries.

## Index world country populations

1. Download [populations\_2024.csv](https://github.com/nreese/notes/raw/refs/heads/master/populations_2024.csv) file. This file is derived from [World Bank Group](https://data.worldbank.org/indicator/SP.POP.TOTL).
2. In Kibana, go to **File upload** using the [global search field](https://www.elastic.co/guide/en/kibana/8.19/introduction.html#kibana-navigation-search).
3. Select `populations_2024.csv` in file selector
4. Set **New index name** to **populations**
5. Click **Import**

## Add population field to world\_countries index

Use [enrich processor](https://www.elastic.co/guide/en/elasticsearch/reference/8.19/enrich-processor.html) to add populations to the world countries set.

1. Go to **Developer tools** using the navigation menu or [global search field](https://www.elastic.co/guide/en/kibana/8.19/introduction.html#kibana-navigation-search).

2. Create a **match** enrichment policy

3. To initialize the policy, run:

4. To create a ingest pipeline, run:

5. To add population data to world countries, run:

6. View **world\_countries** index in **Discover**. Each row now includes **population.population\_2024** column  
  

## Normalize count per population in ES|QL statement

1. Click **edit** button for **Total Requests by Destination** layer.
2. Replace ES|QL statement with

```plaintext
FROM kibana_sample_data_logs 
| STATS count = COUNT() BY geo.dest 
| RENAME geo.dest AS iso2.keyword 
| LOOKUP JOIN world_countries ON iso2.keyword 
| RENAME iso2.keyword AS geo.dest 
| KEEP count, geometry, geo.dest, population.population_2024 
| EVAL normalized_count = TO_DOUBLE(count) / population.population_2024 * 100000

```

1. Click **Run query** in ES|QL editor
2. In **Layer style** set **Fill color** to **By value** by **normalized\_count**.
3. Click **Keep changes**.

Your map should now look like

 ![Screenshot 2025-11-09 at 3.29.13 PM](https://us1.discourse-cdn.com/elastic/original/3X/f/2/f220664f0d0bda804d9298b993f0fd2e65f69996.jpeg)

> **Note:** You may have guessed why not importing the populations CSV with the lookup setting to perform a second `LOOKUP JOIN` to the map query. That would work! but mind that there is a performance penalty on each additional join.