Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

cheers,

Marc

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
    
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 Feb 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. –  marcp Feb 28 '13 at 15:43

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.

share|improve this answer
    
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. –  marcp Feb 22 '13 at 15:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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