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.

• marcp: Thanks @BradHards, that didn't work either. I get the exact same message. I'm beginning to think there may be a problem with the dataset. I'm running it right now on the Canada.osm from the same source. I'll let you know if that works better.

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.

• marcp: Yes I've poked around in there quite a bit, but I'm not sure what I'm looking for. The error message refers to a column 'type', but not where it should be or what it should contain.
• marcp: I've also tried it with the ogr2ogr but it hung. Using the add vector layer works. It seems to me that the spatialite_osm_map tool needs to be updated or the documentation changed.