Finding a Route between 2 Addresses

Finding a route between 2 Addresses (not points) using pgGeocoder with a PostgreSQL based routing application such as pgDijkstra-go is quite straightforward since just a singe SQL statement can be written to Geocode the address and pass the resulting coordinates to the Routing application to be used as parameters for the route search.

The SQL statement can also be used as a TRIGGER so that the shortest path between a residential house and the nearest hospital or evacuation center can be computed on every INSERT or UPDATE on the table hosting data.

For the example below, the following Kyoto addresses are used:

• 京都市上京区竹屋町通り松屋町西入ル藁屋町５３５番地
• 向日市寺戸町七ノ坪30番地ジオ阪急洛西口ノースレジデンス１階

and the japan_v table contains the OSM road network that will be used as the graph to find the shortest path between the above addresses.

What the SQL Statement does is:

1. Geocode the From address
2. Convert the resulting x,y coordinates into a PostGIS Point.
3. Find the nearest node from the road network and assign it as the Source node.
4. Geocode the To address
5. Convert the resulting x,y coordinates into a PostGIS Point.
6. Find the nearest node from the road network and assign it as the Target node.
7. Pass the Source and Target nodes, as well as the road network, to the pg_dijkstra_go routing function to find the shortest path.

SQL Statement:

with 
  --
  -- Geocoding address and getting the source node 
  --
  source as (select source::integer as pt from japan_v 
    order by geom <-> (select ST_SETSRID(ST_POINT(a.x,a.y),4326) from 
    geocoder('京都市上京区竹屋町通り松屋町西入ル藁屋町５３５番地') a) limit 1),
  --
  -- Geocoding address and getting the target node 
  --
  target as (select source::integer as pt from japan_v
    order by geom <-> (select ST_SETSRID(ST_POINT(a.x,a.y),4326) from 
    geocoder('向日市寺戸町七ノ坪30番地ジオ阪急洛西口ノースレジデンス１階') a) limit 1)  
  --
  -- Finding the route between the two addresses
  --
select pg_dijkstra_go((select json_agg(graph)::text from 
  (select id,source,target,cost from japan_v) graph),
  (select pt from source),
  (select pt from target) 
);

Route Result:

Note:

pgRouting of course can also be used as the routing engine to get a route between 2 addresses.