spatialite_osm_overpass
Not logged in

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:
  1. this tool will create and populate a SpatiaLite DB-file by directly querying a remote OSM server via the Overpass API web protocol.
  2. 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.
  3. 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.
  4. 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:
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:
firenze-map.png
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');
firenze-power.png
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:
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:
      Motorway120 Km/h
Trunk90 Km/h
Primary80 Km/h
Secondary60 Km/h
Tertiary50 Km/h
any other30 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