I am trying to create a function, which takes the nearest 3 hospitals
to a point making use of a PostGIS function), and then check each
hospital for the exact distance on roads (by making use of a pgRouting
function).
Below please find a copy of my function, and u can also find it
highlighted here : http://yancho.pastebin.com/f13cc045e
CREATE OR REPLACE FUNCTION near_hospital(text, integer, integer)
RETURNS integer AS
$BODY$
DECLARE
pojnt ALIAS FOR $1;
box ALIAS FOR $2;
dist ALIAS FOR $3;
distances RECORD;
nearest RECORD;
BEGIN
nearest.dist := 1000000000;
FOR distances IN
select astext(h.the_geom) as
hospital_location from hospitals h where
(
h.the_geom && expand (pointfromtext(pojnt),
100000) and
distance ( h.the_geom ,
pointfromtext(pojnt) ) < 150000
)
order by distance (h.the_geom ,
pointfromtext(pojnt)) ASC
limit 3;
LOOP
select INTO hospital gid, the_geom, length(the_geom) AS
dist from shootingstar_sp
( 'streets',
(
select s.gid from streets s, hospitals h
where
source = (
select
give_source(distances.hospital_location,100000,150000))
limit 1
)
,
(
select gid from streets where
target = (select give_target(pojnt,100000,150000))
limit 1
)
,
5000,
'length',
true,
true
);
IF hospital.dist < nearest.dist THEN
nearest.dist := hospital.dist;
nearest.gid := hospital.gid;
select INTO nearest name from hospital h
where h.gid = hospital.gid ;
END IF;
END LOOP;
RETURN nearest.gid;
END;
' language 'plpgsql';
The error being given by pgAdminIII is : unterminated dollar-quoted
string at or near "$BODY$" [then some garbled text] for $1;
Any help will be extremely appreciated!
Thanks and regards
Matthew