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.

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)?

2 Answers
2

Have you looked 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.

EDIT

According to this ticket, 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.

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.
–
marcpFeb 25 '13 at 14:52

1

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.
–
marcpFeb 28 '13 at 15:43

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.

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.
–
marcpFeb 22 '13 at 15:00