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.