Forum #18 - Topic #123 - Message List
Hi,
I have a list of points, representing firestations. I am making a query so as to choose which is the nearest firestation to a point selected by the user (presuming it is where there is an accident). If the accident is a bit far from the firestation it gives me the nearest, but if the accident is very close to the firestation, I am getting a one which is further far.
I am providing some data to help my claim :
SELECT gid, astext(the_geom) as wkt, the_geom , length(the_geom) AS length
FROM shootingstar_sp
('streets',
(
select gid from streets where
source = ( select give_source((select near_accident ('POINT(634320.5 241427.5)','firestations') ),1000,200) )
limit 1
)
,
(
select gid from streets where
target = ( select give_target('POINT(634320.5 241427.5)',1000,200) )
limit 1
)
,
5000,
'length',
true,
true
);
This is the main query I am using. This can be better seen here : http://yancho.pastebin.com/f671701dd
I am using three custom functions give_source and give_target (which are practically the same), and near_accident. I am going to paste near_accident and give_source further down.
near_accident function first checks which of the firestations / hospitals (I can pass any of the two) falls within a region of 1000 meters (1 KM) and gives me the 3 nearest ones.
CREATE OR REPLACE FUNCTION near_accident(text, text)
RETURNS text AS
$BODY$
DECLARE
pojnt ALIAS FOR $1;
tejbil ALIAS FOR $2;
distances RECORD;
hospital RECORD;
nearest RECORD;
silect TEXT;
sorc INTEGER;
targit INTEGER;
BEGIN
select 'inf'::float8 as dist, null::text as gid INTO nearest;
raise notice 'Point is %', pojnt;
select give_target(pojnt,1000,200) INTO targit;
raise notice 'value of target : %',targit;
raise notice '-----------------------------------------------';
-- raise notice 'Entering the FOR IN %', timeofday() ;
silect := ' select astext(h.the_geom) as hospital_location from '|| tejbil ||' h where
(
h.the_geom && expand (pointfromtext('|| quote_literal(pojnt) ||'),100000) and
distance ( h.the_geom , pointfromtext('|| quote_literal(pojnt) ||') ) < 150000
)
order by distance (h.the_geom , pointfromtext('|| quote_literal(pojnt) ||')) ASC
limit 3 ';
FOR distances IN
EXECUTE silect
LOOP
raise notice '=====================================================';
raise notice 'point location of hospital : %', distances.hospital_location;
-- raise notice 'before give source : %', timeofday();
select give_source(distances.hospital_location,1000,200) INTO sorc;
raise notice 'value of sorc : %', sorc;
-- raise notice 'after give source : %', timeofday();
-- raise notice 'before give target : %', timeofday();
-- raise notice 'after give target : %', timeofday();
-- raise notice 'entering shooting star %', timeofday() ;
select gid, the_geom, length(the_geom) AS dist INTO hospital from shootingstar_sp
( 'streets',
(
select s.gid from streets s where
source = (sorc)
limit 1
)
,
(
select gid from streets where
target = ( targit)
limit 1
)
,
5000,
'rcost',
true,
true
);
raise notice 'Hospital Distance %', hospital.dist ;
IF hospital.dist < nearest.dist THEN
nearest.dist := hospital.dist;
raise notice 'value of nearest.dist is %', nearest.dist;
raise notice 'value of hospital.gid is %', hospital.gid;
raise notice 'value of nearest.gid is %', nearest.gid;
nearest.gid := distances.hospital_location;
raise notice 'value of 2ND nearest.gid is %', nearest.gid;
END IF;
-- raise notice 'after if %', timeofday() ;
-- raise notice 'before end of loop %', timeofday() ;
END LOOP;
-- raise notice 'after end of loop before returning %', timeofday() ;
RETURN nearest.gid;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION near_accident(text, text) OWNER TO yancho;
Which can be seen better here : http://yancho.pastebin.com/f21443d2a
And this is give_source which basically gives the source of the segment where the point lies :
CREATE OR REPLACE FUNCTION give_source(text, integer, integer)
RETURNS integer AS
$BODY$
DECLARE
pojnt ALIAS FOR $1;
box ALIAS FOR $2;
dist ALIAS FOR $3;
r_source INTEGER;
BEGIN
SELECT source INTO r_source
FROM streets s
WHERE
(
the_geom && expand (pointfromtext(pojnt),box) and
distance (s.the_geom , pointfromtext(pojnt)) < dist
)
ORDER BY distance (s.the_geom , pointfromtext(pojnt)) ASC
LIMIT 1;
RETURN r_source;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION give_source(text, integer, integer) OWNER TO yancho;
This can be better seen on : http://yancho.pastebin.com/f1db6b9cb
This is the result of the query I tried further up without showing the_geom :
gid | length -------+------------------ 46381 | 119.487000043323 18196 | 118.668466931316 21381 | 79.3749649748783 17475 | 253.725975566964 27494 | 74.5860393897621 27493 | 63.7091656025134 43129 | 185.128448448782 5869 | 516.719430428418 15251 | 211.399367734986 42199 | 116.250314289262 14316 | 32.6834707532192 1743 | 123.843233939053 4795 | 445.086042844799 26706 | 125.79054976706 23447 | 250.247125321842 39960 | 352.879997114279 27650 | 130.104149031247 18320 | 88.1763693815924 30714 | 103.273731618617 49295 | 103.241010673955 49209 | 519.309405228989 (21 rows)
This is a screenshot of the flaw : http://thesis.solutions-lab.net/wrong_firestations.jpg and this is a CSV file with the result of the query : http://thesis.solutions-lab.net/near_fire.csv
The green path is the route from the accident to the nearest firestation (the red/yellow star). As one can see there is one which is much nearer on the right @ location 634799,241982.
The red is me highlighting the part of the road which is a BIdirectional road and is a state road.
Is there anyone with an idea what I can do about this bug please?
Thanks and regards
Matthew
