Create a new version of paste: f2f986b8c

PowerISO 5.0 Multi...13 sec ago
Untitled32 sec ago
Untitled8 sec ago
Untitled27 sec ago
Untitled11 sec ago
Untitled21 sec ago
Watch Live Brumbie...1 min ago
Control C202E27 sec ago

New Paste

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"

Optional Paste Settings

Syntax Highlighting:

Paste Expiration:

Paste Exposure:

Paste Name / Title:

Hello Guest
Sign Up or Login

You are currently not logged in, this means you can not edit or delete anything you paste. Sign Up or Login

Pastebin.com Tools & Applications

Windows

Firefox

Chrome

iOS

WebOS

Android

Mac

Opera

Click.to

UNIX

WinPhone