9

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?

9

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:

micha@Wheezy:~$ spatialite natural_earth_vector.sqlite 
SpatiaLite version ..: 3.0.0-beta   Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualXL'       [direct XLS access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.3.3-CAPI-1.7.4
SQLite version ......: 3.7.13

================ FDO-OGR Spatial Metadata detected ===============
.....
    created VirtualFDO table 'fdo_ne_110m_geography_regions_points'
    created VirtualFDO table 'fdo_ne_110m_geography_regions_polys'
    created VirtualFDO table 'fdo_ne_110m_glaciated_areas'
    created VirtualFDO table 'fdo_ne_110m_lakes'
    created VirtualFDO table 'fdo_ne_110m_land'
    created VirtualFDO table 'fdo_ne_110m_ocean'
    created VirtualFDO table 'fdo_ne_110m_rivers_lake_centerlines'
Accessing these fdo_XX tables you can take full advantage of
FDO-OGR auto-wrapping facility
This allows you to access any specific FDO-OGR Geometry as if it
where native SpatiaLite ones in a completely transparent way
==================================================================

Enter ".help" for instructions
spatialite> attach "nev.sqlite" AS nev;
spatialite> 
spatialite> CREATE TABLE nev.countries AS SELECT * from fdo_ne_10m_admin_0_countries;
spatialite> CREATE TABLE nev.populated_places AS SELECT * FROM fdo_ne_10m_populated_places;
spatialite> CREATE TABLE nev.railroads AS SELECT * FROM fdo_ne_10m_railroads;
spatialite> .q

*** FDO-OGR auto-wrapping shutdown done ***

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 the CREATE 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 use CastToMulti() wherever necessary, like so:

micha@Wheezy:~/GIS/World/naturalearthdata.com$ spatialite nev.sqlite
SpatiaLite version ..: 4.1.1    Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualXL'       [direct XLS access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.3.3-CAPI-1.7.4
SQLite version ......: 3.7.13
Enter ".help" for instructions
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
spatialite> .tables
SpatialIndex            geometry_columns_auth   spatialite_history    
countries               populated_places        sql_statements_log    
geom_cols_ref_sys       railroads               views_geometry_columns
geometry_columns        spatial_ref_sys         virts_geometry_columns
spatialite> 
spatialite> SELECT GeometryType(GEOMETRY) FROM countries;
POLYGON
POLYGON
MULTIPOLYGON
MULTIPOLYGON
POLYGON
MULTIPOLYGON
POLYGON
MULTIPOLYGON
MULTIPOLYGON
.....

Geometries are mixed, so set everything MULTI, then do RecoverGeometryColumn():

spatialite> UPDATE countries SET GEOMETRY=CastToMulti(GEOMETRY);
spatialite> SELECT RecoverGeometryColumn('countries','GEOMETRY',4326,'MULTIPOLYGON',2);
1
spatialite> 

And so on for each table you need. Now the tables are available in QGIS.

  • Thank you for the thorough answer. Do you know why only some of the tables are "imported" with FDO? I count 30 out of 128 tables that get the FDO treatment. PRAGMA on the original and fdo versions is the same, so I assume the only difference is in the geometry itself. But geometry_columns thinks all the geometries are WKB. – Lee Hachadoorian Nov 21 '13 at 17:28
  • Micha, I've picked this as the best answer. Looking further at the tables, I find it weird that the database is packaged with only some of the tables usable via VirtualFDO, and the others with geometries but not SpatiaLite compatible. Rather than try to convert this way, I think I would download the shapefiles and import those to SpatiaLite (which I have already done for PostGIS). But this was still a very informative answer, thank you for the time you took creating it. – Lee Hachadoorian Nov 27 '13 at 1:35
  • If you just want a spatialite database, and you have GDAL/OGR with spatialite support compiled in: ogr2ogr -f sqlite -dsco spatialite=yes splite.db nat_earth.db should work. – user10353 Dec 26 '13 at 16:16
3

You can add the data from the database with Add vector layer ... in QGIS 2.0.1.

But be patient, it is a lot of data.

The Qspatialite plugin is unfortunately not able to deal with the data, nor the Add Spatialite layer dialogue.

  • Andre, that is really useful information. I didn't realize QGIS could use OGR to add data from SQLite-not-SpatiaLite databases. – Lee Hachadoorian Nov 21 '13 at 16:51
  • I also do not understand why there are two ways to add the layers, and only one works ;-) – AndreJ Nov 21 '13 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.