Skip to main content
2 of 2
deleted 22 characters in body; edited title
PolyGeo
  • 65.4k
  • 29
  • 114
  • 345

Calculating shortest path between each lat/lon point in one table and each lan/lon point in another in PostgreSQL?

I have two tables, both of them have only 2 columns. The first one contain only latitude and longitude coordinates of my source points and the other one only latitude and longitude coordinates of my target points. For each source point I have to find the closest (along the streets) target point (and tell what's the distance). I have already developed a function give_me_cost, that finds the closest node and performs the routing:

create or replace function nearest_node (double precision, double precision) returns 

integer as
$$
SELECT id::integer as source_id FROM roads_vertices_pgr
                        ORDER BY the_geom <-> ST_GeometryFromText('POINT('
                        ||$1|| ' ' ||$2||')',4326) LIMIT 1;
$$ language sql;

CREATE OR REPLACE FUNCTION give_me_cost(double precision, double precision, double precision, double precision)
  RETURNS decimal(8,8) AS
$BODY$
with subset as (
SELECT seq, id1 AS node, id2 AS edge, cost
  FROM pgr_dijkstra(
    'SELECT gid as id, source, target, st_length(geom) as cost FROM roads',
    nearest_node($1,$2),nearest_node($3,$4), FALSE, False
  ) as di
)
select sum(cost)::decimal (8,8) from subset

$BODY$
  LANGUAGE sql VOLATILE;

Now I have to use it in some kind of loop, but don't know how. The most preferable solution would be the function, where I can input my source table and my target table and get an output of a table with sources, the closest targets and the distances between them.

Piotr
  • 23
  • 1
  • 3