Yancho

CREATE OR REPLACE FUNCTION near_hospital(text) RETURNS text AS $$

DECLARE
        pojnt ALIAS FOR $1;
-- pojnt will hold a point geometry where the emergency got reported
        distances RECORD;
        hospital RECORD;
        nearest RECORD;


BEGIN

        SELECT 'inf'::float8 AS dist, NULL::text AS gid INTO nearest;


-- In the FOR I am going to select the 3 most near hospitals there are within the region to the point using distance()
-- using && to try to minimize time and choosing a bigger expand box and selecting the distance from the accident
-- to the hospital which is less than 150km
        raise notice 'Entering the FOR IN %', timeofday() ;
       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

-- here i am getting all the results given and passing the location to the give_source function which basically
-- gets the nearest distance on a road .. using the same where clause of the select astext(h.the_geom) etc
-- give_target function is being given to see the nearest node where the accident happened
-- a shootingstar_sp query is fired to give the length
         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, 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
                      );

-- here i am checking about the distance .. if the current distance is smaller than the other distance the values
-- are changed and also the gid (which is being used as the new hospital location) is updated with the astext(the_geom)
-- of the new location .. Here I am using some raise notices to check the data is being changed well.

         raise notice 'before IF %', timeofday() ;

              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;
$$ LANGUAGE 'plpgsql';


PASTING THE RESULT OF :
EXPLAIN ANALYZE SELECT * FROM near_hospital ('POINT(640740 225416)');

-- Executing query:
EXPLAIN ANALYZE SELECT * FROM near_hospital ('POINT(640740 225416)');
NOTICE:  value of nearest.dist IS 585.966565014107
NOTICE:  value of hospital.gid IS 12712
NOTICE:  value of nearest.gid IS <NULL>
NOTICE:  value of 2ND nearest.gid IS POINT(640607.6248615 224673.124400562)
NOTICE:  value of nearest.dist IS 91.0322939509899
NOTICE:  value of hospital.gid IS 38600
NOTICE:  value of nearest.gid IS POINT(640607.6248615 224673.124400562)
NOTICE:  value of 2ND nearest.gid IS POINT(640952.4998615 224309.563400563)

Total query runtime: 113047 ms.
2 rows retrieved.

"Function Scan on near_hospital  (cost=0.00..0.01 rows=1 width=32) (actual time=112910.541..112910.543 rows=1 loops=1)"
"Total runtime: 112910.585 ms"