Add top-level-countries DBF from Natural Earth

[nvkelso]

The following archive:

https://naciscdn.org/naturalearth/5.1.0/housekeeping/ne_admin_0_details.zip

Includes a new table:

ne_admin_0_details_top_level_countries.dbf

That table needs to be added to Postgres as source of truth for "country-ness". Currently it's a join against the admin-0 countries table, which doesn't include all the data we want. This new file does.

[tgrigsby-sc]

@nvkelso I'm looking at this - may need to meet with you to figure out how to wire this in

[nvkelso]

You could load it using a tool like: https://pgloader.readthedocs.io/en/latest/ref/dbf.html

Though see my comment below about loading 2 separate shapefiles instead which pattern matches better with what we already do.

Add it/them to the list of assets around here:

• https://github.com/tilezen/vector-datasource/blob/master/data/assets.yaml#L142-L146

You'll want to configure the extra TLC point-of-view around here:

• vector-datasource/queries/ne.jinja2

  Line 2 in 734e48d

  {% set ne_viewpoints = ['iso', 'ar', 'bd', 'br', 'cn', 'de', 'eg', 'es', 'fr', 'gb', 'gr', 'id', 'il', 'in', 'it', 'jp', 'ko', 'ma', 'nl', 'np', 'pk', 'pl', 'ps', 'pt', 'ru', 'sa', 'se', 'tr', 'tw', 'ua', 'us', 'vn'] %}

But that assumes fclass_tlc is present on all related SHPs, which might not be true yet? I'll see if I need to unblock you there.

Then for the lookup for the "country" min_zoom, that's in:

• vector-datasource/data/functions.sql

  Lines 996 to 1052 in 215fd7a

  	-- returns a JSONB object containing __ne_min_zoom and __ne_max_zoom set to the
  	-- label min and max zoom of any matching row from the Natural Earth countries,
  	-- map units and states/provinces themes.
  	CREATE OR REPLACE FUNCTION tz_get_ne_min_max_zoom(wikidata_id TEXT)
  	RETURNS JSONB AS $$
  	DECLARE
  	min_zoom REAL;
  	max_zoom REAL;
  	BEGIN
  	IF wikidata_id IS NULL THEN
  	RETURN '{}'::jsonb;
  	END IF;
  	-- first, try the countries table
  	SELECT
  	min_label, max_label INTO min_zoom, max_zoom
  	FROM ne_10m_admin_0_countries c
  	WHERE c.wikidataid = wikidata_id;
  	-- if that fails, try map_units (which contains some sub-country but super-
  	-- state level stuff such as England, Scotland and Wales).
  	IF NOT FOUND THEN
  	SELECT
  	min_label, max_label INTO min_zoom, max_zoom
  	FROM ne_10m_admin_0_map_units mu
  	WHERE mu.wikidataid = wikidata_id;
  	END IF;
  	-- try states and provinces
  	IF NOT FOUND THEN
  	SELECT
  	min_label, max_label INTO min_zoom, max_zoom
  	FROM ne_10m_admin_1_states_provinces sp
  	WHERE sp.wikidataid = wikidata_id;
  	END IF;
  	-- finally, try localities
  	-- There is no concept of max_zoom for ne_10m_populated_places
  	IF NOT FOUND THEN
  	SELECT
  	pp.min_zoom, NULL INTO min_zoom, max_zoom
  	FROM ne_10m_populated_places pp
  	WHERE pp.wikidataid = wikidata_id;
  	END IF;
  	-- return an empty JSONB rather than null, so that it can be safely
  	-- concatenated with whatever other JSONB rather than needing a check for
  	-- null.
  	IF NOT FOUND THEN
  	RETURN '{}'::jsonb;
  	END IF;
  	RETURN jsonb_build_object(
  	'__ne_min_zoom', min_zoom,
  	'__ne_max_zoom', max_zoom
  	);
  	END;
  	$$ LANGUAGE plpgsql STABLE;

Which spans across multiple tables today (across input Natural Earth themes across feature classes and kinds), which is problematic because the same Wikidata can be present in multiple tables, sometimes for the same conceptual feature and sometimes not (eg fuzzy versus strict matching) – take France for example.

So the reason we want to load this new table is we want to get only the curated set of strict matches.

There is an alternate way of doing this, which is to load 2 new shapefiles instead of 1 DBF, and then splitting the current function so admin-0 is treated separately from admin-1, localities & etc. For the new admin-0 it'd could be a coalesce across the ISO and TLC shapefiles instead (I'd first look in ISO, then in TLC). That should include 100% of what's in the source DBF (which is used as input for both these SHP).

• http://naciscdn.org/naturalearth/5.1.0/10m/cultural/ne_10m_admin_0_countries_iso.zip (smaller, most conservative list)
• http://naciscdn.org/naturalearth/5.1.0/10m/cultural/ne_10m_admin_0_countries_tlc.zip (larger list than ISO, but misses some ISO groupings like Palestine)

Bonus points for picking up the curated label_x and label_y properties out of those tables, as well as fclass_iso and fclass_tlc.

[tgrigsby-sc]

Looks like our whole shapefile bundle build expects shp files. Adding a dbf will break a few assumptions in the process so I'm going to go with the two shp's approach

[tgrigsby-sc]

Reducing scope here a little for simplicilty. Going to:

• use the iso and tlc shp files @nvkelso mentioned above.
• modify tz_get_min_and_max_zoom to do lookups in iso and tlc only if it's place=country or place=unrecognized, and then if it's not, look up in the admin1+ tables