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 31 Jan 19:53
report abuse | download | new post

  1. -- Function: shootingstar_sp_where(character varying, integer, integer, double precision, character varying, boolean, boolean)
  2.  
  3. -- Edited shootingstar_sp() function prepared by pgRouting
  4.  
  5. -- DROP FUNCTION shootingstar_sp_where(character varying, integer, integer, double precision, character varying, boolean, boolean);
  6.  
  7. CREATE OR REPLACE FUNCTION shootingstar_sp_where(character varying, integer, integer, double precision, character varying, BOOLEAN, BOOLEAN)
  8.   RETURNS SETOF geoms AS $_$
  9.  
  10. DECLARE
  11.         geom_table ALIAS FOR $1;
  12.         sourceid ALIAS FOR $2;
  13.         targetid ALIAS FOR $3;
  14.         delta ALIAS FOR $4;
  15.         cost_column ALIAS FOR $5;
  16.         dir ALIAS FOR $6;
  17.         rc ALIAS FOR $7;
  18.  
  19.         rec record;
  20.         r record;
  21.         path_result record;
  22.         v_id integer;
  23.         e_id integer;
  24.         geom geoms;
  25.        
  26.         srid integer;
  27.  
  28.         source_x float8;
  29.         source_y float8;
  30.         target_x float8;
  31.         target_y float8;
  32.        
  33.         ll_x float8;
  34.         ll_y float8;
  35.         ur_x float8;
  36.         ur_y float8;
  37.        
  38.         query text;
  39.  
  40.         id integer;
  41. BEGIN
  42.        
  43.         id :=0;
  44.         FOR rec IN EXECUTE
  45.             'select srid(the_geom) from ' ||
  46.             quote_ident(geom_table) || ' limit 1'
  47.         LOOP
  48.         END LOOP;
  49.         srid := rec.srid;
  50.        
  51.         FOR rec IN EXECUTE
  52.             'select x(startpoint(the_geom)) as source_x from ' ||
  53.             quote_ident(geom_table) || ' where gid = '||sourceid
  54.         LOOP
  55.         END LOOP;
  56.         source_x := rec.source_x;
  57.        
  58.         FOR rec IN EXECUTE
  59.             'select y(startpoint(the_geom)) as source_y from ' ||
  60.             quote_ident(geom_table) || ' where gid = ' ||sourceid
  61.         LOOP
  62.         END LOOP;
  63.  
  64.         source_y := rec.source_y;
  65.  
  66.         FOR rec IN EXECUTE
  67.             'select x(startpoint(the_geom)) as target_x from ' ||
  68.             quote_ident(geom_table) || ' where gid = ' ||targetid
  69.         LOOP
  70.         END LOOP;
  71.  
  72.         target_x := rec.target_x;
  73.        
  74.         FOR rec IN EXECUTE
  75.             'select y(startpoint(the_geom)) as target_y from ' ||
  76.             quote_ident(geom_table) || ' where gid = ' ||targetid
  77.         LOOP
  78.         END LOOP;
  79.         target_y := rec.target_y;
  80.  
  81.         FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
  82.            ' THEN '||source_x||' ELSE '||target_x||
  83.            ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
  84.            ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
  85.         LOOP
  86.         END LOOP;
  87.  
  88.         ll_x := rec.ll_x;
  89.         ur_x := rec.ur_x;
  90.  
  91.         FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
  92.             target_y||' THEN '||source_y||' ELSE '||
  93.             target_y||' END as ll_y, CASE WHEN '||
  94.             source_y||'>'||target_y||' THEN '||
  95.             source_y||' ELSE '||target_y||' END as ur_y'
  96.         LOOP
  97.         END LOOP;
  98.  
  99.         ll_y := rec.ll_y;
  100.         ur_y := rec.ur_y;
  101.  
  102.         query := 'SELECT gid,the_geom FROM ' ||
  103.           'shortest_path_shooting_star(''SELECT gid as id, source::integer, ' ||
  104.           'target::integer, '||cost_column||'::double precision as cost, ' ||
  105.           'x1::double precision, y1::double precision, x2::double ' ||
  106.           'precision, y2::double precision, rule::varchar, ' ||
  107.           'to_cost::double precision ';
  108.          
  109.         IF rc THEN query := query || ' , reverse_cost ';  
  110.         END IF;
  111.          
  112.         query := query || 'FROM ' || quote_ident(geom_table) || ' where status = 1 AND setSRID(''''BOX3D('||
  113.           ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
  114.           ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
  115.           quote_literal(sourceid) || ' , ' ||
  116.           quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),' ||
  117.           quote_ident(geom_table) || ' where edge_id = gid ';
  118.          
  119.         FOR path_result IN EXECUTE query
  120.         LOOP
  121.                  geom.gid      := path_result.gid;
  122.                  geom.the_geom := path_result.the_geom;
  123.                  id := id+1;
  124.                  geom.id       := id;
  125.                  
  126.                  RETURN NEXT geom;
  127.  
  128.         END LOOP;
  129.         RETURN;
  130. END;
  131. $_$
  132.   LANGUAGE 'plpgsql' VOLATILE STRICT;
  133. ALTER FUNCTION shootingstar_sp(character varying, integer, integer, double precision, character varying, BOOLEAN, BOOLEAN) OWNER TO yancho;

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