After receiving a lot of help from this site I have finally created a set of functions that does the job I wanted. Thank you for that, but it seems there is one final issue, that of efficient memory usage. Here is the problem:
In postgreSQL 9.3, using postGIS 2.1 and pgRouting 2.0, I have created a function that calculates a route between 2 points using the pgrouting function pgr_trsp and returns a geometry (Linestring) value. Here is the code:
CREATE OR REPLACE FUNCTION fm_pgr2geom(edge1 integer, pos1 double precision, edge2 integer, pos2 double precision)
RETURNS geometry AS
$BODY$
--We have to do a routing query. And declare a cursor for it
DECLARE resc CURSOR FOR
SELECT * FROM pgr_trsp (
'SELECT * FROM th_2po_4pgr',
$1, $2, $3, $4, false, true);
doline geometry[];
temp_point geometry;
geom geometry;
temp_rec RECORD;
n integer;
BEGIN
--Append all the edges
FOR temp_rec IN SELECT * FROM pgr_trsp (
'SELECT * FROM th_2po_4pgr',
$1, $2, $3, $4, false, true) LOOP
doline := array_append(
doline, (SELECT map.geom_way FROM th_2po_4pgr map WHERE map.id = temp_rec.id2));
END LOOP;
--Remove 1st and last edge
n := array_length (doline, 1);
doline := doline [2:n-1];
--Find startpoint and append to doline
doline := array_prepend(
ST_LineInterpolatePoint((SELECT map.geom_way FROM th_2po_4pgr map WHERE map.id = $1),$2),doline);
--Append the endpoint
doline := array_append(
doline,ST_LineInterpolatePoint((SELECT map.geom_way FROM th_2po_4pgr map WHERE map.id = $3),$4));
geom := ST_MakeLine(doline);
RETURN geom;
EXCEPTION
WHEN SQLSTATE 'XX000' THEN RETURN NULL;
WHEN SQLSTATE '38001' THEN RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fm_pgr2geom(integer, double precision, integer, double precision)
OWNER TO postgres;
This function is used in another function to batch update a large table (800k+) with the geometry result. Here it is for reference:
CREATE OR REPLACE FUNCTION fm_seqrouting()
RETURNS integer AS
$BODY$
--Declarations
DECLARE
r record;
i integer;
BEGIN
--CODE to calculate routes and update table
i := 0;
FOR r IN
SELECT veh_id
,dt
,map_edge_id AS map_id1
,map_edge_pos AS map_pos1
,lead(map_edge_id) OVER w AS map_id2
,lead(map_edge_pos) OVER w AS map_pos2
FROM taxilocs
WINDOW w AS (ORDER BY veh_id, dt)
LOOP
UPDATE taxilocs
SET geom_route = fm_pgr2geom (r.map_id1,r.map_pos1,r.map_id2,r.map_pos2)
WHERE r.veh_id = taxilocs.veh_id AND r.dt=taxilocs.dt;
i := i + 1;
END LOOP;
RETURN i;
END;
$BODY$
LANGUAGE plpgsql;
The exceptions are absolutely necessary, because they handle some cases where some data is missing from the aforementioned table, or the routing path cannot be found. However, it seems they are causing the problem, which is that the update query crashes after some minutes. The message I receive after some minutes of execution is:
ERROR: out of memory
SQL state: 53200
Detail: Failed on request of size 640000.
So the question is: How can I effectively use or recode this function to update the table I want? Any ideas?
Thank you in advance!
