spatialite-tools: spatialite_osm

back to the OSM tools main page

spatialite_osm_overpass

Syntax:

usage: spatialite_osm_overpass ARGLIST
==============================================================
-h or --help                    print this help message
-d or --db-path     pathname    the SpatiaLite DB path
-minx or --bbox-minx  coord     BoundingBox - west longitude
-maxx or --bbox-maxx  coord     BoundingBox - east longitude
-miny or --bbox-miny  coord     BoundingBox - south latitude
-maxy or --bbox-maxy  coord     BoundingBox - north latitude

you can specify the following options as well
-o or --osm-service   URL       URL of OSM Overpass service:
                                http://overpass-api.de/api (default)
                                http://overpass.osm.rambler.ru/cgi
                                http://api.openstreetmap.fr/oapi
-mode or --mode       mode      one of: RAW / MAP (default) / ROAD / RAIL
-cs or --cache-size   num       DB cache size (how many pages)
-m or --in-memory               using IN-MEMORY database
-jo or --journal-off            unsafe [but faster] mode
-p or --preserve                skipping final cleanup (preserving OSM tables)

Technical details:

this tool will create and populate a SpatiaLite DB-file by directly querying a remote OSM server via the Overpass API web protocol.
spatialite_osm_overpass is fully based on libxml2; this library supports both XML parsing and client-server communications via its own built-in nanoHTTP client.
client-server communications are based on a vector tiled access strategy so to minimize both the latency time on the server side and the RAM amount required on the client side.
This practically means that even huge datasets can be safely downloaded using just minimal resources; anyway the required time is not always ensured to be optimal. Slow and safe is better than fast and risky.

accordingly to standard nanoHTTP configuration an eventual HTTP proxy can be easily by-passed by simply setting the HTTP_PROXY environment variable, e.g.:

setenv "http_proxy=http://proxy.example.org"
spatialite_osm_overpass ... arg-list ...

setenv "HTTP_PROXY=http://proxy.example.org:8080"
spatialite_osm_overpass ... arg-list ...

setenv "HTTP_PROXY=http://username:password@proxy.example.org:8080"
spatialite_osm_overpass ... arg-list ...

Example #1: mode RAW

$ spatialite_osm_overpass -d raw.sqlite -minx 11.15 -maxx 11.33 \
      -miny 43.74 -maxy 43.83 -mode RAW
SQLite version: 3.8.7.1
SpatiaLite version: 4.2.1-rc0

Download completed
inserted 260081 nodes
        481109 tags
inserted 22518 ways
        43735 tags
        209070 node-refs
inserted 1029 relations
        2565 tags
        20776 refs
$

the above shown command must be invoked from the shell:

-d firenze_raw.sqlite selects the SpatiaLite's DB-file to be created and populated.
you can eventually add any other DB-related optimization switch, if you think it will be useful.
Want to learn more ? read the DB optimization short note
the -minx, -maxx, -miny and -maxy arguments specify the overall Bounding Box to covered by the target DB-file.
In this specific example the Bounding Box covers the town of Florence.
-mode RAW specifies that just raw OSM data have to be considered ignoring any further post-processing step.
On successful completion of the current work session the target DB-file will simply contain OSM Nodes, Ways and Relations and related Tags.
there is no -o or --osm-service argument specified, and consequently http://overpass-api.de/api will be implicitly selected as the default Overpass API server.
Anyway you could eventually select anyone of the available Overpass API servers by explicitly specifying its URL:

hint:

SELECT n.node_id, n.version, n.timestamp, n.uid, n.user, n.changeset, n.geometry, t.k, t.v
FROM osm_nodes AS n
JOIN osm_node_tags AS t ON (n.node_id = t.node_id)
WHERE timestamp >= '2014-11-12' AND timestamp < '2014-11-13';


SELECT w.way_id, t.k, t.v, n.node_id, n.version, n.timestamp, n.uid, n.user, n.changeset
FROM osm_ways AS w
JOIN osm_way_tags AS t ON (w.way_id = t.way_id)
JOIN osm_way_refs AS wr ON (w.way_id = wr.way_id)
JOIN osm_nodes AS n ON (wr.node_id = n.node_id)
WHERE w.way_id IN (
   SELECT DISTINCT ws.way_id
   FROM osm_way_refs AS ws
   JOIN osm_nodes AS ns ON (ws.node_id = ns.node_id)
   WHERE ns.timestamp >= '2014-10-26T17:11' AND ns.timestamp < '2014-10-26T17:12'
)
ORDER BY w.way_id, wr.sub;

by executing SQL queries like the above two you could precisely check e.g. which editing activities occurred during a selected period of time; a really interesting (and may be useful) analysis fully based on OSM raw data.

Example #2: mode MAP

$ spatialite_osm_overpass -d map.sqlite -minx 11.15 -maxx 11.33 \
      -miny 43.74 -maxy 43.83 -p
SQLite version: 3.8.7.1
SpatiaLite version: 4.2.1-rc0

Download completed
inserted 260081 nodes
        481109 tags
inserted 22518 ways
        43735 tags
        209070 node-refs
inserted 1029 relations
        2565 tags
        20776 refs
inserted 5268 Point Features
inserted 13013 Linestring Features
inserted 7901 Polygon Features
inserted 80 MultiLinestring Features
inserted 258 MultiPolygon Features
$

the above shown command must be invoked from the shell:

-d firenze_map.sqlite selects the SpatiaLite's DB-file to be created and populated.
the -minx, -maxx, -miny and -maxy arguments specify a Bounding Box covering the town of Florence.
there is no -mode argument specified; this implies by default MAP mode, i.e. raw OSM will be further processed in order to extract any possible well known map layer.
by explicitly specifying the -p option all OSM raw data will be preserved preventing the standard default action, i.e. performing a final DB cleanup during which all raw data will be suppressed.

this figure corresponds to the fully post-processed OSM map stored into the target DB.

hint:

Very frequently OSM datasets do actually contain many potentially useful informations, but unhappily the loose and unconstrained free-style data model supported by OSM forbids (or make really difficult) extracting all tags when a blind automatic approach is adopted (exactly as it happens in the spatialite_osm_overpass -mode MAP case).
Anyway squeezing any possible bit of information out from OSM datasets isn't at all difficult, if you really know the exact structure of the relevant tags locally adopted by the OSM mappers / contributors.
In this case you can always recover many interesting informations by just executing some SQL statement.

In the Florence area there are lots of informations supporting electric power lines, and they actually present a fairly consistent logical structure. An SQL statement like the following one will successfully recover a fully qualified layer.

CREATE TABLE power_lines AS
SELECT p.osm_id, t1.v AS name, t2.v AS type, t3.v AS source, t4.v AS operator, 
    t5.v AS wires, t6.v AS cables, t7.v AS voltage, t8.v as ref, p.geometry
FROM ln_power AS p
LEFT JOIN osm_way_tags AS t1 ON (p.osm_id = t1.way_id AND t1.k = 'name')
LEFT JOIN osm_way_tags AS t2 ON (p.osm_id = t2.way_id AND t2.k = 'power')
LEFT JOIN osm_way_tags AS t3 ON (p.osm_id = t3.way_id AND t3.k = 'source')
LEFT JOIN osm_way_tags AS t4 ON (p.osm_id = t4.way_id AND t4.k = 'operator')
LEFT JOIN osm_way_tags AS t5 ON (p.osm_id = t5.way_id AND t5.k = 'wires')
LEFT JOIN osm_way_tags AS t6 ON (p.osm_id = t6.way_id AND t6.k = 'cables')
LEFT JOIN osm_way_tags AS t7 ON (p.osm_id = t7.way_id AND t7.k = 'voltage')
LEFT JOIN osm_way_tags AS t8 ON (p.osm_id = t8.way_id AND t8.k = 'ref');

SELECT RecoverGeometryColumn('power_lines', 'geometry', 4326, 'LINESTRING', 'XY');

and finally this figure shows the enriched power_lines layer on QGIS

Example #3: mode ROAD

$ spatialite_osm_overpass -d tuscany_roads.sqlite -minx 9.65 -maxx 12.38 \
      -miny 42.22 -maxy 44.48 -mode ROAD
SQLite version: 3.8.7.1
SpatiaLite version: 4.2.1-rc0

Download completed
inserted 4181387 nodes
        92630 tags
inserted 325435 ways
        807154 tags
        4643605 node-refs
inserted 0 relations
        0 tags
        0 refs
inserted 495961 ROAD nodes
inserted 632744 ROAD arcs

Final DBMS cleanup
All done
$

the above shown command must be invoked from the shell:

-d tuscany_roads.sqlite selects the SpatiaLite's DB-file to be created and populated.
the -minx, -maxx, -miny and -maxy arguments specify a Bounding Box covering the whole Tuscany Region.
-mode ROAD specifies that just the Road Network has to be considered ignoring any other OSM data.
On successful completion the target DB-file will simply contain a road graph you could eventually use in a further step so to create a VirtualNetwork for routing purposes.
the alternative option -mode RAIL is exactly the same except for the intended target Network.
note: the -p option is not specified and consequently all OSM raw data will be definitively discarded during the final DB cleanup step.

hint:

You'll probably be interested into using the road network in order to create a VirtualNetwork fully supporting routing algorithms.
Anyway you cannot expect that an as is OSM road graph should always correspond to a genuine graph: some further refinement step is usually required.

SELECT * 
FROM road_arcs
WHERE node_from = node_to; 

DELETE FROM road_arcs
WHERE node_from = node_to;

First of all we'll check (and eventually delete) all self-closed arcs, i.e. arcs originating and terminating on the same identical node.
They don't contribute at all to the overall network connectivity and simply represent a disturbing noise factor.

SELECT * 
FROM road_arcs
WHERE ST_Length(geometry) = 0;

DELETE FROM road_arcs
WHERE ST_Length(geometry) = 0;

Second: we'll check (and eventually delete) all zero-length arcs because they simply correspond to some error or disturbing artifact.

SELECT DISTINCT type 
FROM road_arcs
ORDER BY type;

SELECT *
FROM road_arcs
WHERE type IN ('Proposed', 'abandoned', 'bridleway', 'citywalls', 'construction', 
    'cycleway', 'disused', 'elevator', 'emergency_bay', 'footway', 
    'footway;residential;steps', 'footway;steps;residential', 'ford',
    'grade4', 'path', 'pedestrian', 'pedistrian', 'platform', 'private',
    'proposed', 'raceway', 'rest_area', 'steps', 'track',
    'track;unclassified', 'trail', 'via_ferrata');

DELETE FROM road_arcs
WHERE type IN ('Proposed', 'abandoned', 'bridleway', 'citywalls', 'construction', 
    'cycleway', 'disused', 'elevator', 'emergency_bay', 'footway', 
    'footway;residential;steps', 'footway;steps;residential', 'ford',
    'grade4', 'path', 'pedestrian', 'pedistrian', 'platform', 'private',
    'proposed', 'raceway', 'rest_area', 'steps', 'track',
    'track;unclassified', 'trail', 'via_ferrata');

Third: we'll check (and eventually delete) all arcs declaring a functional classification clearly unfit to support motor vehicles transit.

ALTER TABLE road_arcs ADD COLUMN transit_time;

UPDATE road_arcs SET transit_time =
CASE 
    WHEN type = 'motorway' THEN ST_Length(geometry, 1) / (120.0 * 1000.0 / 3600.0) 
    WHEN type = 'trunk' THEN ST_Length(geometry, 1) / (90.0 * 1000.0 / 3600.0)
    WHEN type = 'primary' THEN ST_Length(geometry, 1) / (80.0 * 1000.0 / 3600.0)
    WHEN type = 'secondary' THEN ST_Length(geometry, 1) / (60.0 * 1000.0 / 3600.0)
    WHEN type = 'tertiary' THEN ST_Length(geometry, 1) / (50.0 * 1000.0 / 3600.0)
ELSE
    ST_Length(geometry, 1) / (30.0 * 1000.0 / 3600.0) 
END;

In order to get more realistic routing solutions we'll now add to the road_arcs table a further transit_time column.
This column represents the time (expressed in seconds) required to fully traverse the arc assuming a reasonable average speed depending on the functional role of the arc.
We'll arbitrarily assume the following average speeds:

	Motorway	120 Km/h
	Trunk	90 Km/h
	Primary	80 Km/h
	Secondary	60 Km/h
	Tertiary	50 Km/h
	any other	30 Km/h

Please note: in the UPDATE statement we'll use ST_Length() in its special form ST_Length(geometry, 1) because OSM geometries are base on latitudes and longitudes but all lengths are assumed to be measured in meters.

UPDATE road_arcs SET transit_time = 
    ST_Length(geometry, 1) / ((maxspeed - (maxspeed / 10)) * 1000.0 / 3600.0)
WHERE maxspeed IS NOT NULL;

And finally we'll refine the estimated time using the maxspeed value when available; we'll introduce a 10% penalization factor so to get a more realistic average speed.

$ spatialite_network -d tuscany_roads.sqlite -T road_arcs -f node_from \
      -t node_to -g geometry -n name -c transit_time \
      --oneway-fromto oneway_ft --oneway-tofrom oneway_tf \
      -o roads_data -v roads_net
SQLite version: 3.8.7.1
SpatiaLite version: 4.2.1-rc0
Step   I - checking for table and columns existence

spatialite-network

==================================================================
   SpatiaLite db: tuscany_roads.sqlite
validating table: road_arcs

columns layout
==================================================================
FromNode: node_from
  ToNode: node_to
    Cost: transit_time
    Name: name
Geometry: geometry

assuming arcs to be BIDIRECTIONAL
OneWay To->From: oneway_tf
OneWay From->To: oneway_ft

NETWORK-DATA table creation required: 'roads_data'

VirtualNetwork table creation required: 'roads_net'
Overwrite not allowed if table already exists
==================================================================

Step  II - checking value types consistency
Step III - checking topological consistency
Step  IV - final evaluation

Statistics
==================================================================
        # Arcs : 825633
        # Nodes: 381074
        Node max  incoming arcs: 15
        Node max outcoming arcs: 8
        # Nodes   cardinality=1: 92494 [terminal nodes]
        # Nodes   cardinality=2: 95226 [meaningless, pass-through]
==================================================================

OK: network passed validation
        you can apply this configuration to build a valid VirtualNetwork
OK: validation passed

OK: NETWORK-DATA table 'roads_data' successfully created
OK: table 'roads_data' successfully created
OK: table 'roads_net' successfully created
$

All right, we are now ready to successfully create a VirtualNetwork by invoking the spatialite_network tool.

SELECT *
FROM roads_net
WHERE NodeFrom = 243887459 AND NodeTo = 500883913;

SELECT *
FROM roads_net
WHERE NodeFrom = 243887459 AND NodeTo = 874514819;

SELECT *
FROM roads_net
WHERE NodeFrom = 874514819 AND NodeTo = 500883913;

And finally we can now execute few Routing queries in order to directly check the OSM-Tuscany roads network.

back to the OSM tools main page