[GIS] Help importing osm data into spatialite

openstreetmapspatialitesqlite

I am trying to import OSM data into Spatialite.

The data is a query from overpass called features.osm

I'm using spatialite 3.0, and following examples, I have created a new database with:

$ spatialite features.sqlite

then I import the data using

$ spatialite_osm_net -o features.osm -d features.sqlite -T features

This results in the following output:

SQLite version: 3.7.9
SpatiaLite version: 3.0.0-beta


Loading OSM nodes ... wait please ...
    Loaded 11 OSM nodes

Verifying OSM ways ... wait please ...
    Verified 0 OSM ways

Disambiguating OSM nodes ... wait please ...
    No duplicate OSM nodes found - fine ...

Loading network ARCs ... wait please ...
    Loaded 0 network ARCs

Dropping temporary table 'osm_tmp_nodes' ... wait please ...
    Dropped table 'osm_tmp_nodes'

Dropping index 'from_to' ... wait please ...
    Dropped index 'from_to'

VACUUMing the DB ... wait please ...
    All done: OSM graph was successfully loaded

However when I open the database and view the contents of 'features' table, its empty. What am I missing here? Perhaps I'm trying to fit a square peg in a round hole?

What I want, is to be able to store osm data in the sqlite database and execute queries to retrieve nodes/ways/relations for a specific bounds (just like my original url request).

Best Answer

Use the spatialite_osm_raw tool for importing OSM files (raw format tables, strictly corresponding to XML

"Also this tool is intended to parse a whole OSM dataset, thus creating the corresponding SpatiaLite's DB-file; but in this case the generated DB-file will adopt a completely different data layout than in the previous scenario. In this case will be adopted a data layout closely mapping OSM objects, their data attributes and their mutual relationships; fully preserving the original OSM data model, and carefully avoiding to introduce any unpleasant information suppression. 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."

Example:

    $ spatialite_osm_raw -o my_country.osm.pbf -d my_country.sqlite
SQLite version: 3.7.11
SpatiaLite version: 3.1.0
inserted 3047972 nodes
        166634 tags
inserted 233481 ways
        423462 tags
        3461321 node-refs
inserted 6068 relations
        14815 tags
        56617 refs
$

You can view the imported data with spatlite_gui

enter image description here

https://www.gaia-gis.it/fossil/spatialite-tools/index

Online Examples:

https://www.gaia-gis.it/fossil/spatialite-tools/wiki?name=spatialite_osm_raw

Related Question