Add population data to EMS files

[nreese]

Reading over https://www.esri.com/arcgis-blog/products/product/mapping/mapping-coronavirus-responsibly/ and one point for accurate choropleth maps is to normalize values with population data so values can be accurately compared across regions.

The first step to allowing this in Kibana would be to include population data in EMS. Then, Kibana could allow for a math expression in the join metric to do the normalization.

[jsanz]

thanks for reporting, we should explore if we can use wikidata population property or we should add this information from other datasets like UNData (even they don't use any kind of identifier on their downloaded datasets 😞).

we'll take a look 👍

[jsanz]

@nickpeihl I was taking a look at this today and saw that we use a static dataset from NaturalEarth. This source has already a population estimate along with the year of that estimate and even also information about the last (official?) census.

To get the data in GeoJSON format, simplified, and with the same property names that we are currently using we could use mapshaper:

NE_SCALE=50
NE_DATASET=ne_${NE_SCALE}m_admin_0_countries
NE_URL="https://www.naturalearthdata.com/download/${NE_SCALE}m/cultural/${NE_DATASET}.zip"

curl -L -o /tmp/countries.zip $NE_URL

unzip /tmp/countries.zip -d /tmp

npx mapshaper /tmp/ne_50m_admin_0_countries.shp \
-simplify visvalingam "99%" -clean \
-filter-fields "ISO_A2,ISO_A3,WIKIDATAID,POP_EST,POP_YEAR,NAME_EN" \
-each "name=NAME_EN, delete NAME_EN, iso2=ISO_A2, delete ISO_A2, iso3=ISO_A3, delete ISO_A3, wikidata=WIKIDATAID, delete WIKIDATAID, pop=POP_EST, delete POP_EST, pop_year=POP_YEAR, delete POP_YEAR,$ISO" \
-o countries.alt.geojson rfc7946

Unfortunately, the dataset in its current version has few records without ISO codes (issue at NE repo).

For a dataset that is updated so sparsely, is it worth automating these fixes or should we simply manually update those few IDs and carry on?

[nickpeihl]

Region-specific metrics is definitely a feature I am interested in. And it would be a boon to have normalized choropleth mapping in Elastic Maps.

Here are things I think we need to consider.

1. This would probably involve adding a new field type to the manifest schema (ex. metric) so clients (i.e. Elastic Maps) can differentiate it from a field used for a terms join.
2. metric type fields would need to only be exposed in the vector manifests for EMS versions that support the new field type (ex. v7.8).
3. However, I think metric type fields could be added to all the existing GeoJSON files without needing to re-version those files since clients should only use fields defined in the vector manifest. Might need to test this in older releases though.
4. Data such as population is temporal and any metrics should reflect that in the field name (i.e. POP2018, POP2019, etc).
5. If we roll out identical metrics for multiple region levels (populations for countries and subdivisions), I think we should try to use the same data source as much as possible. Perhaps World Bank Data?
6. The World Countries layer could use an update from the Natural Earth dataset. We can manually add the missing ISO codes.
7. We may be able to easily query Wikidata for adding metrics fields for Wikidata sourced layers. But we may want to check them against the official record.

@jsanz Do you mind creating a PoC for adding Population (and maybe GDP) to the World Countries layer?

[nreese]

Can we name the field something other then metric. That term already has meaning in the Elastic stack for aggregation bucket values. I think it would be best to avoid confusion since you add metrics to EMS boundaries when you perform a term join. It would be confusing if the EMS boundaries already came with metrics.

[nickpeihl]

Can we name the field something other then metric. That term already has meaning in the Elastic stack for aggregation bucket values. I think it would be best to avoid confusion since you add metrics to EMS boundaries when you perform a term join. It would be confusing if the EMS boundaries already came with metrics.

Good idea. We'll iterate on something.

[jsanz]

@jsanz Do you mind creating a PoC for adding Population (and maybe GDP) to the World Countries layer?

yep, I was already on that boat for at least add population and GDP as the most obvious normalization fields. I will try the wikidata path and see how it looks compared to Natural Earth.

[jsanz]

@nickpeihl I got this Wikidata SPARQL query with results that we can join to our current world countries dataset.

It's not perfect though, some countries don't have the population or GDP because they only have one record in wikidata so they don't have records with the wikibase:PreferredRank qualifier. Not sure how to include the most recent entry in the population/GDP list without timing out so I was considering:

• Get all the records and then do the filtering locally
• Get the data (they are just a few) by their ID using UNION or similar
• Avoid getting the years and just get the preferred value (but still some missing values)

I kind of prefer the first for being able to automate but maybe you have better ideas.

query

SELECT ?id ?label_en ?iso_3166_1 ?pop (YEAR(?pop_date) AS ?pop_year) ?gdp (YEAR(?gdp_date) AS ?gdp_year) WHERE {
  {
    ?id wdt:P31 wd:Q3624078;
        wdt:P297 ?iso_3166_1.
    OPTIONAL {
        ?id rdfs:label ?label_en.
        FILTER((LANG(?label_en)) = "en")
    }
    OPTIONAL {
        ?id p:P1082 [
            ps:P1082 ?pop;
            pq:P585 ?pop_date;
            wikibase:rank wikibase:PreferredRank
        ].
    }
    OPTIONAL {
        ?id p:P2132 [
            ps:P2132 ?gdp;
            pq:P585 ?gdp_date;
            wikibase:rank wikibase:PreferredRank
        ].
    }
  }
}
ORDER BY DESC (?pop)

[kmartastic]

Consider Population + Area for normalization.
Population more important.

[jsanz]

While working on #179 I've checked if I could also add population and area measurements as discussed here.

This is the SPARQL query I have so far. I can do the join of the CSV generated by that query to include them in the resulting dataset but before moving forward I wanted to check @thomasneirynck @nreese if data normalization is still in the Elastic Maps application roadmap.

[kmartastic]

My 2 cents @jsanz.

I think making it easy to normalize would be a great enhancement for Elastic Maps as we look at supporting "ease of analysis" capabilities.

What are the possible user flows for normalization? Are we hoping to allow this through run-time-fields (calculated fields)?

[nreese]

What are the possible user flows for normalization? Are we hoping to allow this through run-time-fields (calculated fields)?

Not sure run-time-fields can be used as run-time-fields exist inside elasticsearch while joins for choropleth maps occur in the client. We could add the configuration to the metric configuration or add a special calculated fields section.