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 11:32 (modification of post by Yancho view diff)
report abuse | download | new post

  1. CREATE OR REPLACE FUNCTION near_hospital(text, integer, integer)
  2.   RETURNS integer AS
  3. $BODY$
  4.  
  5. DECLARE
  6.         pojnt ALIAS FOR $1;
  7.         box ALIAS FOR $2;
  8.         dist ALIAS FOR $3;
  9.  
  10.         distances RECORD;
  11.  
  12.         nearest RECORD;
  13.  
  14.  
  15. BEGIN
  16.        
  17.  
  18.        nearest.dist := 1000000000;
  19.  
  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.                SELECT INTO hospital gid, the_geom, length(the_geom) AS dist FROM shootingstar_sp
  32.                       ( 'streets',
  33.                            
  34.                             (
  35.                                 SELECT s.gid FROM streets s, hospitals h WHERE
  36.                                 source = (
  37.                                        SELECT give_source(distances.hospital_location,100000,150000))
  38.                                 LIMIT 1
  39.                             )
  40.  
  41.                             ,
  42.  
  43.                             (
  44.                                 SELECT gid FROM streets WHERE
  45.                                 target = (SELECT give_target(pojnt,100000,150000))
  46.                                 LIMIT 1
  47.                             )
  48.                            
  49.                             ,
  50.                            5000,
  51.                            'length',
  52.                            true,
  53.                            true
  54.                       );
  55.              
  56.  
  57.               IF hospital.dist < nearest.dist  THEN
  58.                   nearest.dist := hospital.dist;
  59.                   nearest.gid := hospital.gid;
  60.  
  61.                   SELECT INTO nearest name FROM hospital h
  62.                      WHERE h.gid = hospital.gid ;
  63.  
  64.               END IF;
  65.              
  66.        END LOOP;
  67.        
  68.        RETURN nearest.gid;
  69.      
  70. END;
  71.  
  72. ' language 'plpgsql';

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 so that I can delete my post