Cannot open Spatialite db from OpenStreetMap in QGIS

Question

I am new to QGIS, OpenStreetmap and Spatialite. I'm trying to get the (OSM) map data into a (SQLite) compact form I can eventually use for realtime reverse geocoding in a vehicle. Quantum is just for exploring the data, at this point.

I've downloaded the Saskatchewan highway layer from CloudMade (http://downloads.cloudmade.com/americas/northern_america/canada/saskatchewan) and converted it to Spatialite using

spatialite_osm_raw -o saskatchewan.highway.osm -d SK_Hi.sqlite

and

spatialite_osm_net -o saskatchewan.highway.osm -d SK_hi_net.sqlite -T Roads

Both processes run to completion, although there are lots of unresolved nodes while building the network. Neither map layer can be opened in QGis. In either case I am getting a "Failure exploring tables from: Z:/GIS/OpenStreetMap/SK/sk_hi_net.sqlite. no such column: type" when I try to connect to the database.

I am able to browse the db using the Quantum DB Manager, and I see that a number of the tables are empty. Specifically "geometry_columns_field_infos" all of the views_geometry tables and virts_geometry tables.

Is this a problem with the datasource, the toolchain, or my understanding (in increasing order of likelihood)?

Answer 1

I think you've chosen the wrong tools.

From the documentation:

spatialite_osm_map

...

The intended goal of this tool is the one to produce a DB-file suited to be immediately used with some appropriate GIS application (e.g. QGIS).

and

spatialite_osm_raw

...

The intended goal of this tool is not at all to produce a DB-file usable by GIS applications: it's instead the one to get a DB-file you can use in order to gather statistical infos, to support some post-processing task and so on.

The easiest way forward is to work through the spatialite_osm_map example, then try it with with your data set, then introduce QGIS.

You probably don't need the spatialite_osm_net part, unless you're trying to construct a routing system.

Answer 2

Try looking at the data with spatialite_gui. There were some issues that spatialite support in QGIS is a bit behind the capabilities the current version of spatialite is offering.

---

According to Bug report #6196, the spatialite database should be added with Add Vector Layer..., selecting ALL Formats, or SQLite [OGR] driver. Then choose the layers you want, e.g. ln_highway, and set the CRS manually to EPSG:4326.

Alternatively, you can use GDAL with

ogr2ogr -f SQLite <filename>.sqlite <filename>.osm.pbf

and add the database the same way as above. The OGR driver produces only one layer for every geometry type.