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.sqliteI had previously installed Datasette and SpatiaLite using Homebrew:
brew install datasette spatialite-toolsDatabase 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_areasorder by area_sqkm descEvery country at 50m scale (a good balance between detail and overall size):
select AsGeoJSON(GeomFromWKB(GEOMETRY)), *from ne_50m_admin_0_countriesThis 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 fillfrom ne_10m_admin_0_countries
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, adminfrom ne_10m_admin_1_states_provinceswhere 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:
- Large scale data, 1:10m - most detailed
- Medium scale data, 1:50m
- Small scale data, 1:110m - least detailed
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_coastlineFull list of tables
ne_10m_admin_0_antarctic_claim_limit_linesne_10m_admin_0_antarctic_claimsne_10m_admin_0_boundary_lines_disputed_areasne_10m_admin_0_boundary_lines_landne_10m_admin_0_boundary_lines_map_unitsne_10m_admin_0_boundary_lines_maritime_indicatorne_10m_admin_0_boundary_lines_maritime_indicator_chnne_10m_admin_0_countriesne_10m_admin_0_countries_argne_10m_admin_0_countries_bdgne_10m_admin_0_countries_brane_10m_admin_0_countries_chnne_10m_admin_0_countries_deune_10m_admin_0_countries_egyne_10m_admin_0_countries_espne_10m_admin_0_countries_frane_10m_admin_0_countries_gbrne_10m_admin_0_countries_grcne_10m_admin_0_countries_idnne_10m_admin_0_countries_indne_10m_admin_0_countries_isrne_10m_admin_0_countries_itane_10m_admin_0_countries_jpnne_10m_admin_0_countries_korne_10m_admin_0_countries_lakesne_10m_admin_0_countries_marne_10m_admin_0_countries_nepne_10m_admin_0_countries_nldne_10m_admin_0_countries_pakne_10m_admin_0_countries_polne_10m_admin_0_countries_prtne_10m_admin_0_countries_psene_10m_admin_0_countries_rusne_10m_admin_0_countries_saune_10m_admin_0_countries_swene_10m_admin_0_countries_turne_10m_admin_0_countries_twnne_10m_admin_0_countries_ukrne_10m_admin_0_countries_usane_10m_admin_0_countries_vnmne_10m_admin_0_disputed_areasne_10m_admin_0_disputed_areas_scale_rank_minor_islandsne_10m_admin_0_label_pointsne_10m_admin_0_map_subunitsne_10m_admin_0_map_unitsne_10m_admin_0_namesne_10m_admin_0_pacific_groupingsne_10m_admin_0_scale_rankne_10m_admin_0_scale_rank_minor_islandsne_10m_admin_0_seamsne_10m_admin_0_sovereigntyne_10m_admin_1_label_pointsne_10m_admin_1_label_points_detailsne_10m_admin_1_seamsne_10m_admin_1_selne_10m_admin_1_states_provincesne_10m_admin_1_states_provinces_lakesne_10m_admin_1_states_provinces_linesne_10m_admin_1_states_provinces_scale_rankne_10m_admin_1_states_provinces_scale_rank_minor_islandsne_10m_admin_2_countiesne_10m_admin_2_counties_lakesne_10m_admin_2_counties_linesne_10m_admin_2_counties_scale_rankne_10m_admin_2_counties_scale_rank_minor_islandsne_10m_admin_2_counties_to_matchne_10m_admin_2_label_pointsne_10m_admin_2_label_points_detailsne_10m_airportsne_10m_antarctic_ice_shelves_linesne_10m_antarctic_ice_shelves_polysne_10m_coastlinene_10m_geographic_linesne_10m_geography_marine_polysne_10m_geography_regions_elevation_pointsne_10m_geography_regions_pointsne_10m_geography_regions_polysne_10m_glaciated_areasne_10m_lakesne_10m_lakes_australiane_10m_lakes_europene_10m_lakes_historicne_10m_lakes_north_americane_10m_lakes_pluvialne_10m_landne_10m_land_ocean_label_pointsne_10m_land_ocean_seamsne_10m_land_scale_rankne_10m_minor_islandsne_10m_minor_islands_coastlinene_10m_minor_islands_label_pointsne_10m_oceanne_10m_ocean_scale_rankne_10m_parks_and_protected_lands_areane_10m_parks_and_protected_lands_linene_10m_parks_and_protected_lands_pointne_10m_parks_and_protected_lands_scale_rankne_10m_playasne_10m_populated_placesne_10m_populated_places_simplene_10m_portsne_10m_railroadsne_10m_railroads_north_americane_10m_reefsne_10m_rivers_australiane_10m_rivers_europene_10m_rivers_lake_centerlinesne_10m_rivers_lake_centerlines_scale_rankne_10m_rivers_north_americane_10m_roadsne_10m_roads_north_americane_10m_time_zonesne_10m_urban_areasne_10m_urban_areas_landscanne_110m_admin_0_boundary_lines_landne_110m_admin_0_countriesne_110m_admin_0_countries_lakesne_110m_admin_0_map_unitsne_110m_admin_0_pacific_groupingsne_110m_admin_0_scale_rankne_110m_admin_0_sovereigntyne_110m_admin_0_tiny_countriesne_110m_admin_1_states_provincesne_110m_admin_1_states_provinces_lakesne_110m_admin_1_states_provinces_linesne_110m_admin_1_states_provinces_scale_rankne_110m_coastlinene_110m_geographic_linesne_110m_geography_marine_polysne_110m_geography_regions_elevation_pointsne_110m_geography_regions_pointsne_110m_geography_regions_polysne_110m_glaciated_areasne_110m_lakesne_110m_landne_110m_oceanne_110m_populated_placesne_110m_populated_places_simplene_110m_rivers_lake_centerlinesne_50m_admin_0_boundary_lines_disputed_areasne_50m_admin_0_boundary_lines_landne_50m_admin_0_boundary_lines_maritime_indicatorne_50m_admin_0_boundary_lines_maritime_indicator_chnne_50m_admin_0_boundary_map_unitsne_50m_admin_0_breakaway_disputed_areasne_50m_admin_0_breakaway_disputed_areas_scale_rankne_50m_admin_0_countriesne_50m_admin_0_countries_lakesne_50m_admin_0_map_subunitsne_50m_admin_0_map_unitsne_50m_admin_0_pacific_groupingsne_50m_admin_0_scale_rankne_50m_admin_0_sovereigntyne_50m_admin_0_tiny_countriesne_50m_admin_0_tiny_countries_scale_rankne_50m_admin_1_seamsne_50m_admin_1_states_provincesne_50m_admin_1_states_provinces_lakesne_50m_admin_1_states_provinces_linesne_50m_admin_1_states_provinces_scale_rankne_50m_airportsne_50m_antarctic_ice_shelves_linesne_50m_antarctic_ice_shelves_polysne_50m_coastlinene_50m_geographic_linesne_50m_geography_marine_polysne_50m_geography_regions_elevation_pointsne_50m_geography_regions_pointsne_50m_geography_regions_polysne_50m_glaciated_areasne_50m_lakesne_50m_lakes_historicne_50m_landne_50m_oceanne_50m_playasne_50m_populated_placesne_50m_populated_places_simplene_50m_portsne_50m_rivers_lake_centerlinesne_50m_rivers_lake_centerlines_scale_rankne_50m_urban_areas