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. Any help appreciated.
