pastebin - collaborative debugging

pastebin is a collaborative debugging tool allowing you to share and modify code snippets while chatting on IRC, IM or a message board.

This site is developed to XHTML and CSS2 W3C standards. If you see this paragraph, your browser does not support those standards and you need to upgrade. Visit WaSP for a variety of options.

yancho private pastebin - collaborative debugging tool What's a private pastebin?


Posted by Yancho on Thu 6 Dec 20:08 (modification of post by Yancho view diff)
report spam | download | new post

  1. CREATE OR REPLACE FUNCTION near_hospital(text) RETURNS text AS $$
  2.  
  3. DECLARE
  4.         pojnt ALIAS FOR $1;
  5. -- pojnt will hold a point geometry where the emergency got reported
  6.         distances RECORD;
  7.         hospital RECORD;
  8.         nearest RECORD;
  9.        
  10.  
  11. BEGIN
  12.        
  13.         SELECT 'inf'::float8 AS dist, NULL::text AS gid INTO nearest;
  14.        
  15.  
  16. -- In the FOR I am going to select the 3 most near hospitals there are within the region to the point using distance()
  17. -- using && to try to minimize time and choosing a bigger expand box and selecting the distance from the accident
  18. -- to the hospital which is less than 150km
  19.         raise notice 'Entering the FOR IN %', timeofday() ;
  20.        FOR distances IN
  21.        
  22.                               SELECT astext(h.the_geom) AS hospital_location FROM hospitals h WHERE
  23.                                  (
  24.                                  h.the_geom && expand (pointfromtext(pojnt),100000) AND
  25.                                  distance ( h.the_geom , pointfromtext(pojnt) ) < 150000
  26.                                  )
  27.                             ORDER BY distance (h.the_geom , pointfromtext(pojnt)) ASC
  28.                             LIMIT 3
  29.         LOOP
  30.  
  31. -- here i am getting all the results given and passing the location to the give_source function which basically
  32. -- gets the nearest distance on a road .. using the same where clause of the select astext(h.the_geom) etc
  33. -- give_target function is being given to see the nearest node where the accident happened
  34. -- a shootingstar_sp query is fired to give the length
  35.          raise notice 'entering shooting star %', timeofday() ;
  36.                SELECT gid, the_geom, length(the_geom) AS dist INTO hospital FROM shootingstar_sp
  37.                       ( 'streets',
  38.                            
  39.                             (
  40.                                 SELECT s.gid FROM streets s, hospitals h WHERE
  41.                                 source = (
  42.                                        SELECT give_source(distances.hospital_location,100000,150000))
  43.                                 LIMIT 1
  44.                             )
  45.  
  46.                             ,
  47.  
  48.                             (
  49.                                 SELECT gid FROM streets WHERE
  50.                                 target = (SELECT give_target(pojnt,100000,150000))
  51.                                 LIMIT 1
  52.                             )
  53.                            
  54.                             ,
  55.                            5000,
  56.                            'length',
  57.                            true,
  58.                            true
  59.                       );
  60.          
  61. -- here i am checking about the distance .. if the current distance is smaller than the other distance the values
  62. -- are changed and also the gid (which is being used as the new hospital location) is updated with the astext(the_geom)
  63. -- of the new location .. Here I am using some raise notices to check the data is being changed well.  
  64.  
  65.          raise notice 'before IF %', timeofday() ;  
  66.  
  67.               IF hospital.dist < nearest.dist  THEN
  68.                   nearest.dist := hospital.dist;
  69.         raise notice 'value of nearest.dist is %', nearest.dist;
  70.         raise notice 'value of hospital.gid is %', hospital.gid;
  71.         raise notice 'value of nearest.gid is %', nearest.gid;
  72.                   nearest.gid := distances.hospital_location;
  73.         raise notice 'value of 2ND nearest.gid is %', nearest.gid;
  74.  
  75.               END IF;
  76.          raise notice 'after if %', timeofday() ;
  77.          raise notice 'before end of loop %', timeofday() ;              
  78.  
  79.        END LOOP;
  80.         raise notice 'after end of loop before returning %', timeofday() ;          
  81.        RETURN nearest.gid;
  82.      
  83. END;
  84. $$ LANGUAGE 'plpgsql';
  85.  
  86.  
  87. PASTING THE RESULT OF :
  88. EXPLAIN ANALYZE SELECT * FROM near_hospital ('POINT(640740 225416)');
  89.  
  90. -- Executing query:
  91. EXPLAIN ANALYZE SELECT * FROM near_hospital ('POINT(640740 225416)');
  92. NOTICE:  value of nearest.dist IS 585.966565014107
  93. NOTICE:  value of hospital.gid IS 12712
  94. NOTICE:  value of nearest.gid IS <NULL>
  95. NOTICE:  value of 2ND nearest.gid IS POINT(640607.6248615 224673.124400562)
  96. NOTICE:  value of nearest.dist IS 91.0322939509899
  97. NOTICE:  value of hospital.gid IS 38600
  98. NOTICE:  value of nearest.gid IS POINT(640607.6248615 224673.124400562)
  99. NOTICE:  value of 2ND nearest.gid IS POINT(640952.4998615 224309.563400563)
  100.  
  101. Total query runtime: 113047 ms.
  102. 2 rows retrieved.
  103.  
  104. "Function Scan on near_hospital  (cost=0.00..0.01 rows=1 width=32) (actual time=112910.541..112910.543 rows=1 loops=1)"
  105. "Total runtime: 112910.585 ms"

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:

To highlight particular lines, prefix each line with @@


Remember me