ISO 3166-2 Codes and the GeoLite2 databases

[nickpeihl]

The GeoLite2 IP databases used by the GeoIP Ingest plugin include country subdivision codes (ISO 3166-2) so they can be used with our county subdivision vector layers.

The ISO codes in the GeoIP database are split into two fields: the country code (ex. DE for Germany) and the one, two, or three digit subdivision code (ex. BE for Berlin).

An example response:

...
hits: [{
  "geo": {
     "timezone": "Europe/Berlin",
     "subdivision-name": "Land Berlin",
     "city-name": "Berlin",
     "country-code2": "DE",
     "postal-code": "10715",
     "subdivision-code": "BE",
     "country-name": "Germany",
     "continent-code": "EU",
     "location": {
       "lat": 52.486,
       "lon": 13.3271
     }
  }
  ...
}, ...]
...

Our GeoJSON files for the subdivisions include the ISO code as a single field (ex. DE-BE) as specified in the ISO 3166-2 standard.

{
  "type": "FeatureCollection",
  "features": [{ 
    "type": "Feature",
    "id": "Q64",
    "properties": {
      "iso_3166_2": "DE-BE",
      "label_en": "Berlin",
      "label_de": "Berlin"
    },
   "geometry": {...
    }, 
    ...
  ]
}

Thus the only current way to join IP addresses ingested by the GeoIP plugin to a country subdivision is to create a scripted field in Kibana that joins the country code and subdivision code separated by a dash (-).

Alternatively, we could add fields to the GeoJSON layers that split the full ISO 3166-2 code into country and subdivision codes. However, this is problematic because the subdivision codes listed in the GeoLite2 database are not unique to a single country. For example, the subdivision code 'C' exists in both Spain (ES-C as A Coruña) and in Ireland (IE-C as Connaught). So a user creating a subdivisions region map visualization for website visits from a single country would need to be sure to add a filter for only that country in order to avoid seeing erroneous information.

If we were able to join region maps on multiple fields then this might not be an issue. But that may be out of scope for Kibana.

My preference is to stick to keeping the full ISO code in the GeoJSON files and direct users to create scripted fields to join. Perhaps this would be a good blog post once more country subdivision vector layers are in production.

@elastic/kibana-gis

[thomasneirynck]

this is interesting.

some quick thoughts:

• I wouldn't dismiss the join-on-two field approach. It is out of scope for the current visualizations (no new features basically), but we could consider this for the GIS-app if we want to provide a full end-2-end solution. It would be a nice round-trip use-case for many of our users use-cases (server metrics basically)
• The way we currently publish data is by country. Users would really only be able to see details of a single country. The multi-layered nature of the GIS-app would provide a means to work-around this though.

A little bit more exotic, but what would the file-size be of a fully joined (ie. ALL the countires and ALL their subdivisions). The current leaflet-implementation does not do well with large datasets, but a GL-based viewer would (e.g. in the order of 10k shapes should be doable). Could we produce a data layer like this to support the GIS-app (basically, a layer with full ISO 3166-2 support)?

[nickpeihl]

So it appears the GeoIP Ingest plugin actually omits the subdivision ISO code as an option. One can get the region (subdivision) name only. This appears to be the relevant code showing the options.

I don't think it would be too difficult to add the subdivision ISO code since it's available in the GeoIP2-Java API as subdivision.getIsoCode(). But I wonder if there was a deliberate reason for omitting it in the Ingest plugin?

[nickpeihl]

Pull request for adding this functionality to the Ingest Geoip plugin in Elasticsearch.

[thomasneirynck]

Closed with elastic/elasticsearch#31669