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've installed Spatialite 2.4 in Ubuntu. I try to convert an osm file into a roads network using this tutorial and make searches on it, but the queries only produce "no column" errors.

$ spatialite_osm -o data/mosobl.osm -d test.sqlite -T roads -m
SQLite version: 3.6.22
SpatiaLite version: 2.4.0
using IN-MEMORY database
Loading OSM nodes ... wait please ...
Loading OSM nodes ... wait please ...
Loaded 3261086 OSM nodes
Verifying OSM ways ... wait please ...
Verified 108981 OSM ways
Disambiguating OSM nodes ... wait please ...
Found 112 duplicate OSM nodes - fixed !!!
Loading network ARCs ... wait please ...
Loaded 227809 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'
exporting IN_MEMORY database ... wait please ...
IN_MEMORY database succesfully exported
VACUUMing the DB ... wait please ...
All done: OSM graph was succesfully loaded

Checking the table structure:

$ spatialite test.sqlite 
SpatiaLite version ..: 2.4.0    Supported Extensions:
- 'VirtualShape'    [direct Shapefile access]
- 'VirtualDbf'      [direct DBF access]
- 'VirtualText'     [direct CSV/TXT access]
- 'VirtualNetwork'  [Dijkstra shortest path]
- 'RTree'       [Spatial Index - R*Tree]
- 'MbrCache'        [Spatial Index - MBR cache]
- 'VirtualFDO'      [FDO-OGR interoperability]
- 'SpatiaLite'      [Spatial SQL - OGC]
...

spatialite> .schema roads
CREATE TABLE "roads" (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
osm_id INTEGER NOT NULL,
class TEXT NOT NULL,
node_from INTEGER NOT NULL,
node_to INTEGER NOT NULL,
name TEXT NOT NULL,
oneway_fromto INTEGER NOT NULL,
oneway_tofrom INTEGER NOT NULL,
length DOUBLE NOT NULL,
cost DOUBLE NOT NULL, "geometry" LINESTRING);
CREATE TRIGGER "ggi_roads_geometry" BEFORE INSERT ON "roads"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'roads.geometry violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT type FROM geometry_columns
WHERE f_table_name = 'roads' AND f_geometry_column = 'geometry'
AND GeometryConstraints(NEW."geometry", type, srid, 'XY') = 1) IS NULL;
END;
CREATE TRIGGER "ggu_roads_geometry" BEFORE UPDATE ON "roads"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'roads.geometry violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT type FROM geometry_columns
WHERE f_table_name = 'roads' AND f_geometry_column = 'geometry'
AND GeometryConstraints(NEW."geometry", type, srid, 'XY') = 1) IS NULL;
END;

Verifying the structure:

    $ spatialite_network -d test.sqlite -T roads -f node_from -t node_to -g geometry -c cost -o roads_net
SQLite version: 3.6.22
SpatiaLite version: 2.4.0
Step   I - checking for table and columns existence

spatialite-network

==================================================================
   SpatiaLite db: test.sqlite
validating table: roads

columns layout
==================================================================
FromNode: node_from
  ToNode: node_to
    Cost: cost
    Name: *unused*
Geometry: geometry

assuming arcs to be BIDIRECTIONAL

NETWORK-DATA table creation required: 'roads_net'
Overwrite not allowed if table already exists
==================================================================

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

Statistics
==================================================================
    # Arcs : 455042
    # Nodes: 178470
    Node max  incoming arcs: 7
    Node max outcoming arcs: 7
    # Nodes   cardinality=1: 40228 [terminal nodes]
    # Nodes   cardinality=2: 16388 [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_net' successfully created
OK: table 'roads_net' successfully created

Now I log into spatialite and try to get some routing. Should it work from the original roads table?

spatialite> select * from roads where node_from=4870889 and node_to=4870985;
(nothing)

No. Or from roads_net?

spatialite> select * from roads_net where node_from=4870889 and node_to=4870985;
SQL error: no such column: node_from

Apparently, I can only get a single way with this search: take node_from and node_to and put them in the query to roads.

What should I do to run a route query?

PS: Here's what tables I have in the db. The tutorial mentions roads_net_data which is absent here.

spatialite> .tables
geom_cols_ref_sys       roads                   views_geometry_columns
geometry_columns        roads_net               virts_geometry_columns
geometry_columns_auth   spatial_ref_sys       
share|improve this question
add comment

1 Answer 1

up vote 4 down vote accepted

Have you tried something like [1]:

spatialite test.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'.

And the correct routing query seems to be [2]:

SELECT *
FROM Roads_net
WHERE NodeFrom = 1 AND NodeTo = 512;

[1] http://blog.mikeasoft.com/2010/09/24/local-map-rendering-and-route-finding-with-libchamplain-spatialite-and-open-street-map/

[2] http://www.gaia-gis.it/spatialite/spatialite-network-2.3.1.html

share|improve this answer
    
Great! I'd never have found this! –  culebrón May 29 '11 at 19:41
    
It works. Still I can't find a way to switch it to A* algorithm. Update roads_net set Algorithm="A*"; doesn't work. –  culebrón May 29 '11 at 19:52
    
VirtualNetwork seems to only implement Dijkstra. Edit: Seems like starting from v.2.4.0-RC2, A* should be supported too. –  underdark May 29 '11 at 20:07
    
I can't find any other documentation on A* support than the GUI screenshot in gaia-gis.it/spatialite-2.4.0/RC2-Addendum.pdf –  underdark May 29 '11 at 20:13
    
Ok, thanks a lot anyway! –  culebrón May 29 '11 at 22:07
add comment

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.