One to Many and or Many to One Routes

[Stephen Woodbridge]

Hi,

Is there an efficient to run one to many or many to one routes in spatialite?

I know I can just iterate computing many routes, but with Dijkstra at least for one to many problem all the results should be included in the graph after it is solved.

If not, maybe something like this could be implemented:

select * from graph_net where FromNode=123 and ToNode in (12,23,34,etc);

Then maybe the output might be:

Algorithm|RouteNodeFrom|RouteNodeTo|ArcRowid|NodeFrom|NodeTo|Cost|Geometry|Name

so for the summary line RouteNodeFrom=NodeFrom and RouteNodeTo=NodeTo. and it would be easy to extract any individual route using where RouteNodeFrom=? and RouteNodeTo=?

[Stephen Woodbridge]

I tried to do a CROSS JOIN against the VirtualNetwork table but that did not work. Perhaps my SQL is not correct>

create table locs (origin_id int, destination_id int, origin_nodeid int, destination_nodeid int);
-- populate it with valid data

-- run cross join
select origin_id, destination_id, b.*
  from edges_net b cross join locs
 where NodeFrom=origin_nodeid and NodeTo=destination_nodeid;

This returns immediately with not results and no error messages.
Should this work?
Is this a limitation in spatialite or with working with virtual tables?
Is there another way to do this in SQL or do I need to do this as a loop from python?

[Stephen Woodbridge]

Looks like the order of the tables is important. This query works nicely:

select * from locs cross join edges_net where NodeFrom=origin_nodeid and NodeTo=destination_nodeid;

In this case we want each row of the locs table applied to the virtualnetwork table. Reversing the order does not make sense because the virtualnetwork table has no output unless it is given input.

So cool! I have one to many, many to one and many to many depending on how I setup  the locs table!