I just downloaded the Natural Earth data in SQLite format from http://www.naturalearthdata.com/downloads/. I had assumed that this would be a SpatiaLite database, but it appears not to be! QGIS is failing to recognize it as a spatial database. OGR supposedly supports reading geometries even when stored in a plain SQLite database, but maybe QGIS doesn't use OGR for SpatiaLite?
The SQLite database does have geometry_columns and spatial_ref_sys tables. Is there some way to convert it to a full SpatiaLite database?
Best Answer
The sqlite file from NE is in FDO-OGR format, not the native spatialite geometry. If you're willing to do some manual labor, here's a way to convert to a spatialite db:
First make a new, empty spatialite database (I call it "nev.sqlite"), then in a separate terminal session open the original natural_earth_vector.sqlite with spatialite. (I used the newer version 4.1. Not sure if this will work with the older versions). Use the sqlite
attach
function to connect to your new nev.sqlite table, and create copies of the tables you want into the new database.So:
All the lines "created VirtualFDO..." indicate that Spatialite recognized the data as FDO formated, and created virtual tables for each with the GEOMETRY converted to spatialite format. I
attach
to my new "nev" database and create new tables for each layer I'm interested in with theCREATE TABLE ... AS SELECT * FROM ...
statements.Now I switch back over the the new spatialite database. And run
RecoverGeometryColumn()
on each table to get a proper spatialite database, with all metadata, etc. Note that the FDO format allows for mixed MULTI and SINGLE geometry types, so I first check which geometry types each table contains, and make sure that all features are the same. I useCastToMulti()
wherever necessary, like so:Geometries are mixed, so set everything MULTI, then do RecoverGeometryColumn():
And so on for each table you need. Now the tables are available in QGIS.