Search code examples
sqliteshortest-pathspatialite

How to get the long/lat information from a NodeID and vice versa

In the SpatiaLite website they explain how to use .osm data to create a SpatiaLite database, that contains a single table, where each row is an arc of the road graph. Then, they explain the procedure of calculating the shortest path from A to B in their website:

  1. Create the VirtualNetwork from a .osm file using a tool they provide
  2. Execute the following query: select * from VirtualNetwork where NodeFrom=267209305 and NoteTo=267209702 to get the shortest path
  3. Below the result you are supposed to get from the query above. It represents every arc on the shortest path.

1

Questions:

  1. How to get the Latitude/Longitude from every NodeID that is on the shortest path. Otherwise, I will not be able to interpret what those NodeIDs mean.

  2. How to get the NodeIDs from the Latitude/Longitude of NodeFrom and NodeTo. Otherwise, I will not be able to execute the shortest path query in the first place.

Solution

  • Following the example from https://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/dijkstra.html as you say, we can find the route between two nodes (154348 and 130324) executing this query:

    select *
from tuscany_net
where nodefrom = 154348
and nodeto = 130324

    Question 1

    To find the point (latitude/longitudes) of the nodes in the route, you can query the tuscany_nodes table filtering it with the results of tuscany_net as follows:

    select node_id, st_y(geometry) latitude, st_x(geometry) longitude
from tuscany_nodes
where node_id in (
    select nodeto
    from tuscany_net
    where nodefrom = 154348
    and nodeto = 130324
)

    You will get a result set like this:

    node_id    latitude     longitude
130324    43.843969    10.981256
130337    43.843960    10.981382
130352    43.844300    10.981580
130414    43.845558    10.982525
130420    43.845541    10.982572
...

    Alternatively, as the documentation says, the first row of this result-set summarizes the whole path, and contains the corresponding geometry that represents the route and can be used to draw it or to find all the points in the route.

    If you want to see the WKT representation of the route you can query:

    select st_astext(Geometry)
from tuscany_net
where nodefrom = 154348
and nodeto = 130324 limit 1

    You will get something like this that can be used to extract the latitudes/longitudes of the points on the route:

    LINESTRING(11.138376 42.739078, 11.137961 42.738531,
 11.13765 42.738001, 11.137428 42.737463, 11.136459 42.734198,
 11.136129 42.733111, 11.135814 42.732221, 11.135666 42.732069,
 11.135485 42.731948, 11.135242 42.731884, 11.134913 42.731891,
 ... )

    Question 2

    To get the node_id for a given latitude/longitude you will need to perform a query on the nodes table (tuscany_nodes on the example). As the nodes table contains points (geometries) representing the nodes, the best approach will be to perform a spatial query to get the from and to nodes. For example, you can use the st_distance function to get the nodes that are closer than a given distance to your target point (here you can find the SQL functions reference list for SpatiaLite 4.2.0):

    select node_id
from tuscany_nodes
where st_distance(Geometry, makepoint(9.69561, 44.44792), 1) < 10