Natural Earth in SpatiaLite and Datasette

Natural Earth (website, Wikipedia) is a a public domain map dataset.

It's distributed in a bunch of different formats - one of them is a SQLite database file.

http://naciscdn.org/naturalearth/packages/natural_earth_vector.sqlite.zip - this is a 423MB file which decompresses to provide a 791MB packages/natural_earth_vector.sqlite file.

I opened it in Datasette like this:

datasette --load-extension spatialite \
  ~/Downloads/natural_earth_vector.sqlite/packages/natural_earth_vector.sqlite

I had previously installed Datasette and SpatiaLite using Homebrew:

brew install datasette spatialite-tools

Database format

The database contains 181 tables, for different layers at different scales. Those tables are listed below.

Each table has a bunch of columns and a GEOMETRY column. That geometry column contains data stored in WKB - Well-Known Binary format.

If you have SpatiaLite you can convert that column to GeoJSON like so:

AsGeoJSON(GeomFromWKB(GEOMETRY))

For example, here are the largest "urban areas" at 50m scale:

select
  AsGeoJSON(GeomFromWKB(GEOMETRY))
from
  ne_50m_urban_areas
order by area_sqkm desc

Every country at 50m scale (a good balance between detail and overall size):

select
  AsGeoJSON(GeomFromWKB(GEOMETRY)), *
from
  ne_50m_admin_0_countries

This query draws a coloured map of countries using the datasette-geojson-map and sqlite-colorbrewer plugins:

select
  ogc_fid,
  GeomFromWKB(GEOMETRY) as geometry,
  colorbrewer('Paired', 9, MAPCOLOR9 - 1) as fill
from
  ne_10m_admin_0_countries

Screenshot of a map showing different countries in random colours

The ne_10m_admin_1_states_provinces table is useful: it has subdivisions for a bunch of different countries. Here's the UK divided into counties:

select
  ogc_fid,
  GeomFromWKB(GEOMETRY) as geometry,
  featurecla,
  scalerank,
  adm1_code,
  diss_me,
  iso_3166_2,
  wikipedia,
  iso_a2,
  adm0_sr,
  name,
  name_alt,
  type,
  type_en,
  area_sqkm,
  latitude,
  longitude,
  admin
from
  ne_10m_admin_1_states_provinces
where
  admin = 'United Kingdom'

I tried this with select *, GeomFromWKB(GEOMETRY) as geometry first but it didn't work with datasette-geojson-map because the * picked up the original GEOMETRY column in the wrong format.

The scales are:

Exploring with Datasette plugins

With the datasette-leaflet-geojson plugin installed, any column that returns GeoJSON (from AsGeoJSON(GeomFromWKB(GEOMETRY))) will render as a little map, no matter what the column name.

If you install datasette-geojson-map you can seee a single map with all of the shapes on - you need to create a geometry column containing a SpatiaLite geometry, which you can do like this:

select
  ogc_fid, GeomFromWKB(GEOMETRY) as geometry, *
from
  ne_50m_coastline

Full list of tables

Created 2022-03-04T11:11:55-08:00, updated 2022-03-04T16:10:50-08:00 · History · Edit