Cannot open Spatialite db from OpenStreetMap in QGIS

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

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 .sqlite .osm.pbf

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