Merge Natural Earth SQLite country polygons using ogr2ogr and ST_Union

Question

I'm trying to create a map with the world continents (with a few modifications). As a source I use a sqlite file from Natural Earth. I already introduced my modification in the definition of the continents and now I would like to merge the countries of the same continent using the table "ne_110m_admin_0_countries" into a single shape and export the result to GeoJSON format.

I use the following command:

ogr2ogr -f GeoJSON -dialect sqlite -sql "select continent, st_union(geometry) as geom from ne_110m_admin_0_countries group by continent" continent.json natural_earch_vector.sqlite

I get no errors, but the result is a GeoJSON file containing:

{ "type": "FeatureCollection", "features": [ { "type": "Feature", "properties": {"continent": "Africa", "geom": null}, "geometry": null }, { "type": "Feature", "properties": {"continent": "Antarctica", "geom": null}, "geometry": null }, { "type": "Feature", "properties": {"continent": "Asia", "geom": null}, "geometry": null }, { "type": "Feature", "properties": {"continent": "Europe", "geom": null}, "geometry": null }, { "type": "Feature", "properties": {"continent": "North America", "geom": null}, "geometry": null }, { "type": "Feature", "properties": {"continent": "Oceania", "geom": null}, "geometry": null }, { "type": "Feature", "properties": {"continent": "Seven seas", "geom": null}, "geometry": null }, { "type": "Feature", "properties": {"continent": "South America", "geom": null}, "geometry": null }, ] }

Am I missing something? what is wrong in my command? Why I get the geom column inside the properties and I have an additional geometry feature?

It doesn't seem to work also if I use another output format instead of GeoJSON.

Answer 1

This is quite a nasty problem and hard to resolve without experience on different ways to store geodata into SQLite database. The problem is not in the output format but in the input format.

The natural_earth_vector.sqlite database is written as FDO. Read more info from the "Regular" SQLite databases" section in the GDAL SQLite driver manual page http://www.gdal.org/drv_sqlite.html and https://trac.osgeo.org/fdo/wiki/FDORfc16.

GDAL can read the plain FDO style geometries fine. However, you want to make a union of geometries with SQLite SQL dialect and ST_Union function. In this case the FDO geometry is passed to SpatiaLite engine. SpatiaLite is waiting for geometries which are written as SpatiaLite style BLOBs and when it gets FDO style blobs it does not find geometries from those. It does not throw an error either because it thinks that it did not receive geometries at all. ST_Union of several nothings is nothing and that's what SpatiaLite sends back.

What makes the problem nasty is that ogrinfo does not tell in any way for users that the database is of FDO variant. Easiest way to observe this is to open the sqlite database with spatialite-gui which recognizes the FDO metadata and creates virtual tables with fdo_ profix.

Because the data are now open in spatialite-gui one could in theory continue with it and utilize the automatically created virtual tables which are totally usable for the SpatiaLite functions. Unfortunately at least my version of spatialite-gui seems to have some bug and virtual table for ne_110m_admin_0_countries is missing.

What I would do is to convert the who FDO database into Spatialite database as

ogr2ogr -f sqlite -dsco spatialite=yes natural_earth_vector_spatialite.sqlite natural_earth_vector.sqlite -nlt promote_to_multi

Now you can run your query for the SpatiaLite db

ogr2ogr -f GeoJSON -dialect sqlite -sql "select continent, st_union(geometry) as geom from ne_110m_admin_0_countries group by continent" continent.json natural_earth_vector_spatialite.sqlite

and the resulting GeoJSON have geometries

"type": "FeatureCollection", "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } }, "features": [ { "type": "Feature", "properties": { "continent": "Africa" }, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 49.543518914595751, -12.469832858940554 ], [