root/tags/release-1.02/core/sql/routing_core_wrappers.sql

Revision 162, 36.1 KB (checked in by anton, 3 years ago)

1.02 release tag added
Line 
1--
2-- Copyright (c) 2005 Sylvain Pasche,
3--               2006-2007 Anton A. Patrushev, Orkney, Inc.
4--
5-- This program is free software; you can redistribute it and/or modify
6-- it under the terms of the GNU General Public License as published by
7-- the Free Software Foundation; either version 2 of the License, or
8-- (at your option) any later version.
9--
10-- This program is distributed in the hope that it will be useful,
11-- but WITHOUT ANY WARRANTY; without even the implied warranty of
12-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13-- GNU General Public License for more details.
14--
15-- You should have received a copy of the GNU General Public License
16-- along with this program; if not, write to the Free Software
17-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
18
19
20-- BEGIN;
21
22CREATE OR REPLACE FUNCTION text(boolean)
23       RETURNS text AS
24$$
25SELECT CASE WHEN $1 THEN 'true' ELSE 'false' END
26$$
27LANGUAGE 'sql';
28
29-----------------------------------------------------------------------
30-- For each vertex in the vertices table, set a point geometry which is
31--  the corresponding line start or line end point
32--
33-- Last changes: 14.02.2008
34-----------------------------------------------------------------------
35CREATE OR REPLACE FUNCTION add_vertices_geometry(geom_table varchar)
36       RETURNS VOID AS
37$$
38DECLARE
39        vertices_table varchar := quote_ident(geom_table) || '_vertices';
40BEGIN
41       
42        BEGIN
43                EXECUTE 'SELECT addGeometryColumn(''' ||
44                        quote_ident(vertices_table)  ||
45                        ''', ''the_geom'', -1, ''POINT'', 2)';
46        EXCEPTION
47                WHEN DUPLICATE_COLUMN THEN
48        END;
49
50        EXECUTE 'UPDATE ' || quote_ident(vertices_table) ||
51                ' SET the_geom = NULL';
52
53        EXECUTE 'UPDATE ' || quote_ident(vertices_table) ||
54                ' SET the_geom = startPoint(geometryn(m.the_geom, 1)) FROM ' ||
55                 quote_ident(geom_table) ||
56                ' m where geom_id = m.source';
57
58        EXECUTE 'UPDATE ' || quote_ident(vertices_table) ||
59                ' set the_geom = endPoint(geometryn(m.the_geom, 1)) FROM ' ||
60                quote_ident(geom_table) ||
61                ' m where geom_id = m.target_id AND ' ||
62                quote_ident(vertices_table) ||
63                '.the_geom IS NULL';
64
65        RETURN;
66END;
67$$
68LANGUAGE 'plpgsql' VOLATILE STRICT;
69
70-----------------------------------------------------------------------
71-- This function should not be used directly. Use assign_vertex_id instead
72--
73-- Inserts a point into a temporary vertices table, and return an id
74--  of a new point or an existing point. Tolerance is the minimal distance
75--  between existing points and the new point to create a new point.
76--
77-- Last changes: 14.02.2008
78-----------------------------------------------------------------------
79CREATE OR REPLACE FUNCTION point_to_id(point geometry,
80       tolerance double precision)
81       RETURNS INT AS
82$$
83DECLARE
84        row record;
85        point_id int;
86BEGIN
87        LOOP
88                -- TODO: use && and index       
89                SELECT INTO row id, the_geom FROM vertices_tmp WHERE
90                   distance(the_geom, point) < tolerance;
91
92                point_id := row.id;
93
94                IF NOT FOUND THEN
95                        INSERT INTO vertices_tmp (the_geom) VALUES (point);
96                ELSE
97                        EXIT;
98                END IF;
99        END LOOP;
100        RETURN point_id;
101END;
102$$
103LANGUAGE 'plpgsql' VOLATILE STRICT;
104
105
106-----------------------------------------------------------------------
107-- Fill the source and target_id column for all lines. All line ends
108--  with a distance less than tolerance, are assigned the same id
109-----------------------------------------------------------------------
110CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table varchar,
111       tolerance double precision,
112       geo_cname varchar,
113       gid_cname varchar)
114       RETURNS VARCHAR AS
115$$
116DECLARE
117      points record;
118      i record;
119      source_id int;
120      target_id int;
121      pre varchar;
122      post varchar;
123     
124      srid integer;
125     
126      countids integer;
127                                       
128      BEGIN
129                                       
130            BEGIN
131                DROP TABLE vertices_tmp;
132                EXCEPTION
133                        WHEN UNDEFINED_TABLE THEN
134                END;
135                                                                   
136                EXECUTE 'CREATE TABLE vertices_tmp (id serial)';       
137                                                                       
138                FOR i IN EXECUTE 'SELECT srid FROM geometry_columns WHERE f_table_name='''|| quote_ident(geom_table)||'''' LOOP
139                END LOOP;
140               
141                srid := i.srid;
142
143                FOR i IN EXECUTE 'SELECT count(*) as countids FROM '|| quote_ident(geom_table) LOOP
144                END LOOP;
145               
146                countids := i.countids;
147               
148                EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 2)';
149                                                                                                                         
150                CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom);
151               
152                pre = '';
153                post = '';
154               
155                FOR i in EXECUTE 'SELECT count(*) as t from ' || quote_ident(geom_table) || ' WHERE NumGeometries(' || quote_ident(geo_cname) || ') is not null'  loop
156                        IF (i.t > 0) THEN
157                            pre = 'geometryN(';
158                            post = ' , 1)';
159                        END IF;
160                END LOOP;
161                                                                                                                                                                                           
162--              FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,'
163--                      || ' startPoint(' || pre || quote_ident(geo_cname) || post || ') AS source,'
164--                      || ' endPoint(' || pre || quote_ident(geo_cname) || post || ') as target'
165--                      || ' FROM ' || quote_ident(geom_table) loop
166--
167                FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,'
168                        || ' PointN('|| quote_ident(geo_cname) ||', 1) AS source,'
169                        || ' PointN('|| quote_ident(geo_cname) ||', NumPoints('|| quote_ident(geo_cname) ||')) as target'
170                        || ' FROM ' || quote_ident(geom_table) || ' ORDER BY ' || quote_ident(gid_cname) loop
171                       
172                        IF points.id%10=0 THEN
173                          RAISE NOTICE '% out of % edges processed', points.id, countids;
174                        END IF;
175
176                                source_id := point_to_id(setsrid(points.source, srid), tolerance);
177                                target_id := point_to_id(setsrid(points.target, srid), tolerance);
178                                                                                                                                                                                                                                       
179
180                                EXECUTE 'update ' || quote_ident(geom_table) ||
181                                    ' SET source = ' || source_id ||
182                                    ', target = ' || target_id ||
183                                    ' WHERE ' || quote_ident(gid_cname) || ' =  ' || points.id;
184                END LOOP;
185                                                                                                                                                                                                                                                                                                           
186RETURN 'OK';
187
188END;
189$$
190LANGUAGE 'plpgsql' VOLATILE STRICT;
191
192-----------------------------------------------------------------------
193-- Update the cost column from the edges table, from the length of
194--  all lines which belong to an edge.
195--
196-- Last changes: 14.02.2008
197-----------------------------------------------------------------------
198CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar)
199       RETURNS VOID AS
200$$
201DECLARE
202BEGIN
203        BEGIN
204          EXECUTE 'CREATE INDEX ' || quote_ident(geom_table) ||
205                  '_edge_id_idx ON ' || quote_ident(geom_table) ||
206                  ' (edge_id)';
207        EXCEPTION
208                WHEN DUPLICATE_TABLE THEN
209                RAISE NOTICE 'Not creating index, already there';
210        END;
211
212        EXECUTE 'UPDATE ' || quote_ident(geom_table) ||
213              '_edges SET cost = (SELECT sum( length( g.the_geom ) ) FROM ' ||
214              quote_ident(geom_table) ||
215              ' g WHERE g.edge_id = id GROUP BY id)';
216
217        RETURN;
218END;
219$$
220LANGUAGE 'plpgsql' VOLATILE STRICT;
221
222
223CREATE TYPE geoms AS
224(
225  id integer,
226  gid integer,
227  the_geom geometry
228);
229
230-----------------------------------------------------------------------
231-- A* function for undirected graphs.
232-- Compute the shortest path using edges table, and return
233--  the result as a set of (gid integer, the_geom geometry) records.
234--
235-- Last changes: 14.02.2008
236-----------------------------------------------------------------------
237CREATE OR REPLACE FUNCTION dijkstra_sp(
238       geom_table varchar, source int4, target int4)
239       RETURNS SETOF GEOMS AS
240$$
241DECLARE
242        r record;
243        path_result record;
244        v_id integer;
245        e_id integer;
246        geom geoms;
247        id integer;
248BEGIN
249       
250        id :=0;
251       
252        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
253          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
254          'length::double precision as cost FROM ' ||
255          quote_ident(geom_table) || ''', ' || quote_literal(source) ||
256          ' , ' || quote_literal(target) || ' , false, false), ' ||
257          quote_ident(geom_table) || ' where edge_id = gid '
258        LOOP
259
260                 geom.gid      := path_result.gid;
261                 geom.the_geom := path_result.the_geom;
262                 id := id+1;
263                 geom.id       := id;
264                 
265                 RETURN NEXT geom;
266
267        END LOOP;
268        RETURN;
269END;
270$$
271LANGUAGE 'plpgsql' VOLATILE STRICT;
272
273-----------------------------------------------------------------------
274-- Dijkstra wrapper function for directed graphs.
275-- Compute the shortest path using edges table, and return
276--  the result as a set of (gid integer, the_geom geometry) records.
277--
278-- Last changes: 14.02.2008
279-----------------------------------------------------------------------
280CREATE OR REPLACE FUNCTION dijkstra_sp_directed(
281       geom_table varchar, source int4, target int4, dir boolean, rc boolean)
282       RETURNS SETOF GEOMS AS
283$$
284DECLARE
285        r record;
286        path_result record;
287        v_id integer;
288        e_id integer;
289        geom geoms;
290        query text;
291        id integer;
292BEGIN
293       
294        id :=0;
295       
296        query := 'SELECT gid,the_geom FROM ' ||
297          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
298          'length::double precision as cost ';
299         
300        IF rc THEN query := query || ', reverse_cost '; 
301        END IF;
302       
303        query := query || 'FROM ' ||  quote_ident(geom_table) || ''', ' || quote_literal(source) ||
304          ' , ' || quote_literal(target) || ' , '''||text(dir)||''', '''||text(rc)||'''), ' ||
305          quote_ident(geom_table) || ' where edge_id = gid ';
306
307        FOR path_result IN EXECUTE query
308        LOOP
309
310                 geom.gid      := path_result.gid;
311                 geom.the_geom := path_result.the_geom;
312                 id := id+1;
313                 geom.id       := id;
314                 
315                 RETURN NEXT geom;
316
317        END LOOP;
318        RETURN;
319END;
320$$
321LANGUAGE 'plpgsql' VOLATILE STRICT;
322
323-----------------------------------------------------------------------
324-- A* function for undirected graphs.
325-- Compute the shortest path using edges table, and return
326--  the result as a set of (gid integer, the_geom geometry) records.
327-- Also data clipping added to improve function performance.
328--
329-- Last changes: 14.02.2008
330-----------------------------------------------------------------------
331CREATE OR REPLACE FUNCTION astar_sp_delta(
332       varchar,int4, int4, float8)
333       RETURNS SETOF GEOMS AS
334$$
335DECLARE
336        geom_table ALIAS FOR $1;
337        sourceid ALIAS FOR $2;
338        targetid ALIAS FOR $3;
339        delta ALIAS FOR $4;
340
341        rec record;
342        r record;
343        path_result record;
344        v_id integer;
345        e_id integer;
346        geom geoms;
347       
348        id integer;
349BEGIN
350       
351        id :=0;
352
353        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
354           'astar_sp_delta_directed(''' ||
355           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
356           quote_literal(targetid) || ', ' || delta || ', false, false)'
357        LOOP
358
359                 geom.gid      := path_result.gid;
360                 geom.the_geom := path_result.the_geom;
361                 id := id+1;
362                 geom.id       := id;
363                 
364                 RETURN NEXT geom;
365
366        END LOOP;
367        RETURN;
368END;
369$$
370LANGUAGE 'plpgsql' VOLATILE STRICT;
371
372-----------------------------------------------------------------------
373-- A* function for directed graphs.
374-- Compute the shortest path using edges table, and return
375--  the result as a set of (gid integer, the_geom geometry) records.
376-- Also data clipping added to improve function performance.
377--
378-- Last changes: 14.02.2008
379-----------------------------------------------------------------------
380CREATE OR REPLACE FUNCTION astar_sp_delta_directed(
381       varchar,int4, int4, float8, boolean, boolean)
382       RETURNS SETOF GEOMS AS
383$$
384DECLARE
385        geom_table ALIAS FOR $1;
386        sourceid ALIAS FOR $2;
387        targetid ALIAS FOR $3;
388        delta ALIAS FOR $4;
389        dir ALIAS FOR $5;
390        rc ALIAS FOR $6;
391
392        rec record;
393        r record;
394        path_result record;
395        v_id integer;
396        e_id integer;
397        geom geoms;
398       
399        srid integer;
400
401        source_x float8;
402        source_y float8;
403        target_x float8;
404        target_y float8;
405       
406        ll_x float8;
407        ll_y float8;
408        ur_x float8;
409        ur_y float8;
410       
411        query text;
412
413        id integer;
414BEGIN
415       
416        id :=0;
417        FOR rec IN EXECUTE
418            'select srid(the_geom) from ' ||
419            quote_ident(geom_table) || ' limit 1'
420        LOOP
421        END LOOP;
422        srid := rec.srid;
423       
424        FOR rec IN EXECUTE
425            'select x(startpoint(the_geom)) as source_x from ' ||
426            quote_ident(geom_table) || ' where source = ' ||
427            sourceid ||  ' or target='||sourceid||' limit 1'
428        LOOP
429        END LOOP;
430        source_x := rec.source_x;
431       
432        FOR rec IN EXECUTE
433            'select y(startpoint(the_geom)) as source_y from ' ||
434            quote_ident(geom_table) || ' where source = ' ||
435            sourceid ||  ' or target='||sourceid||' limit 1'
436        LOOP
437        END LOOP;
438
439        source_y := rec.source_y;
440
441        FOR rec IN EXECUTE
442            'select x(startpoint(the_geom)) as target_x from ' ||
443            quote_ident(geom_table) || ' where source = ' ||
444            targetid ||  ' or target='||targetid||' limit 1'
445        LOOP
446        END LOOP;
447
448        target_x := rec.target_x;
449       
450        FOR rec IN EXECUTE
451            'select y(startpoint(the_geom)) as target_y from ' ||
452            quote_ident(geom_table) || ' where source = ' ||
453            targetid ||  ' or target='||targetid||' limit 1'
454        LOOP
455        END LOOP;
456        target_y := rec.target_y;
457
458        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
459           ' THEN '||source_x||' ELSE '||target_x||
460           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
461           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
462        LOOP
463        END LOOP;
464
465        ll_x := rec.ll_x;
466        ur_x := rec.ur_x;
467
468        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
469            target_y||' THEN '||source_y||' ELSE '||
470            target_y||' END as ll_y, CASE WHEN '||
471            source_y||'>'||target_y||' THEN '||
472            source_y||' ELSE '||target_y||' END as ur_y'
473        LOOP
474        END LOOP;
475
476        ll_y := rec.ll_y;
477        ur_y := rec.ur_y;
478
479        query := 'SELECT gid,the_geom FROM ' ||
480          'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
481          'target::integer, length::double precision as cost, ' ||
482          'x1::double precision, y1::double precision, x2::double ' ||
483          'precision, y2::double precision ';
484         
485        IF rc THEN query := query || ' , reverse_cost '; 
486        END IF;
487         
488        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
489          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
490          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
491          quote_literal(sourceid) || ' , ' ||
492          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),' ||
493          quote_ident(geom_table) || ' where edge_id = gid ';
494         
495        FOR path_result IN EXECUTE query
496        LOOP
497                 geom.gid      := path_result.gid;
498                 geom.the_geom := path_result.the_geom;
499                 id := id+1;
500                 geom.id       := id;
501                 
502                 RETURN NEXT geom;
503--
504--                v_id = path_result.vertex_id;
505--                e_id = path_result.edge_id;
506
507--                FOR r IN EXECUTE 'SELECT gid, the_geom FROM ' ||
508--                      quote_ident(geom_table) || '  WHERE gid = ' ||
509--                      quote_literal(e_id) LOOP
510--                        geom.gid := r.gid;
511--                        geom.the_geom := r.the_geom;
512--                        RETURN NEXT geom;
513--                END LOOP;
514
515        END LOOP;
516        RETURN;
517END;
518$$
519LANGUAGE 'plpgsql' VOLATILE STRICT;
520
521
522-----------------------------------------------------------------------
523-- A* function for undirected graphs.
524-- Compute the shortest path using edges table, and return
525--  the result as a set of (gid integer, the_geom geometry) records.
526-- Also data clipping added to improve function performance.
527-- Cost column name can be specified (last parameter)
528--
529-- Last changes: 14.02.2008
530-----------------------------------------------------------------------
531CREATE OR REPLACE FUNCTION astar_sp_delta_cc(
532       varchar,int4, int4, float8, varchar)
533       RETURNS SETOF GEOMS AS
534$$
535DECLARE
536        geom_table ALIAS FOR $1;
537        sourceid ALIAS FOR $2;
538        targetid ALIAS FOR $3;
539        delta ALIAS FOR $4;
540        cost_column ALIAS FOR $5;
541
542        rec record;
543        r record;
544        path_result record;
545        v_id integer;
546        e_id integer;
547        geom geoms;
548       
549        id integer;
550BEGIN
551       
552        id :=0;
553        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
554           'astar_sp_delta_cc_directed(''' ||
555           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
556           quote_literal(targetid) || ', ' || delta || ',' ||
557           quote_literal(cost_column) || ', false, false)'
558        LOOP
559
560                 geom.gid      := path_result.gid;
561                 geom.the_geom := path_result.the_geom;
562                 id := id+1;
563                 geom.id       := id;
564                 
565                 RETURN NEXT geom;
566
567        END LOOP;
568        RETURN;
569END;
570$$
571LANGUAGE 'plpgsql' VOLATILE STRICT;
572
573-----------------------------------------------------------------------
574-- A* function for directed graphs.
575-- Compute the shortest path using edges table, and return
576--  the result as a set of (gid integer, the_geom geometry) records.
577-- Also data clipping added to improve function performance.
578-- Cost column name can be specified (last parameter)
579--
580-- Last changes: 14.02.2008
581-----------------------------------------------------------------------
582CREATE OR REPLACE FUNCTION astar_sp_delta_cc_directed(
583       varchar,int4, int4, float8, varchar, boolean, boolean)
584       RETURNS SETOF GEOMS AS
585$$
586DECLARE
587        geom_table ALIAS FOR $1;
588        sourceid ALIAS FOR $2;
589        targetid ALIAS FOR $3;
590        delta ALIAS FOR $4;
591        cost_column ALIAS FOR $5;
592        dir ALIAS FOR $6;
593        rc ALIAS FOR $7;
594
595        rec record;
596        r record;
597        path_result record;
598        v_id integer;
599        e_id integer;
600        geom geoms;
601       
602        srid integer;
603
604        source_x float8;
605        source_y float8;
606        target_x float8;
607        target_y float8;
608       
609        ll_x float8;
610        ll_y float8;
611        ur_x float8;
612        ur_y float8;
613       
614        query text;
615
616        id integer;
617BEGIN
618       
619        id :=0;
620        FOR rec IN EXECUTE
621            'select srid(the_geom) from ' ||
622            quote_ident(geom_table) || ' limit 1'
623        LOOP
624        END LOOP;
625        srid := rec.srid;
626       
627        FOR rec IN EXECUTE
628            'select x(startpoint(the_geom)) as source_x from ' ||
629            quote_ident(geom_table) || ' where source = ' ||
630            sourceid || ' or target='||sourceid||' limit 1'
631        LOOP
632        END LOOP;
633        source_x := rec.source_x;
634       
635        FOR rec IN EXECUTE
636            'select y(startpoint(the_geom)) as source_y from ' ||
637            quote_ident(geom_table) || ' where source = ' ||
638            sourceid ||  ' or target='||sourceid||' limit 1'
639        LOOP
640        END LOOP;
641
642        source_y := rec.source_y;
643
644        FOR rec IN EXECUTE
645            'select x(startpoint(the_geom)) as target_x from ' ||
646            quote_ident(geom_table) || ' where source = ' ||
647            targetid ||  ' or target='||targetid||' limit 1'
648        LOOP
649        END LOOP;
650
651        target_x := rec.target_x;
652       
653        FOR rec IN EXECUTE
654            'select y(startpoint(the_geom)) as target_y from ' ||
655            quote_ident(geom_table) || ' where source = ' ||
656            targetid ||  ' or target='||targetid||' limit 1'
657        LOOP
658        END LOOP;
659        target_y := rec.target_y;
660
661
662        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
663           ' THEN '||source_x||' ELSE '||target_x||
664           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
665           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
666        LOOP
667        END LOOP;
668
669        ll_x := rec.ll_x;
670        ur_x := rec.ur_x;
671
672        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
673            target_y||' THEN '||source_y||' ELSE '||
674            target_y||' END as ll_y, CASE WHEN '||
675            source_y||'>'||target_y||' THEN '||
676            source_y||' ELSE '||target_y||' END as ur_y'
677        LOOP
678        END LOOP;
679
680        ll_y := rec.ll_y;
681        ur_y := rec.ur_y;
682
683        query := 'SELECT gid,the_geom FROM ' ||
684          'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
685          'target::integer, '||cost_column||'::double precision as cost, ' ||
686          'x1::double precision, y1::double precision, x2::double ' ||
687          'precision, y2::double precision ';
688       
689        IF rc THEN query := query || ' , reverse_cost ';
690        END IF;
691         
692        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
693          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
694          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
695          quote_literal(sourceid) || ' , ' ||
696          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),' ||
697          quote_ident(geom_table) || ' where edge_id = gid ';
698       
699        FOR path_result IN EXECUTE query
700        LOOP
701
702                 geom.gid      := path_result.gid;
703                 geom.the_geom := path_result.the_geom;
704                 id := id+1;
705                 geom.id       := id;
706                 
707                 RETURN NEXT geom;
708
709        END LOOP;
710        RETURN;
711END;
712$$
713LANGUAGE 'plpgsql' VOLATILE STRICT;
714
715
716-----------------------------------------------------------------------
717-- Dijkstra function for undirected graphs.
718-- Compute the shortest path using edges table, and return
719--  the result as a set of (gid integer, the_geom geometry) records.
720-- Also data clipping added to improve function performance.
721--
722-- Last changes: 14.02.2008
723-----------------------------------------------------------------------
724CREATE OR REPLACE FUNCTION dijkstra_sp_delta(
725       varchar,int4, int4, float8)
726       RETURNS SETOF GEOMS AS
727$$
728DECLARE
729        geom_table ALIAS FOR $1;
730        sourceid ALIAS FOR $2;
731        targetid ALIAS FOR $3;
732        delta ALIAS FOR $4;
733
734        rec record;
735        r record;
736        path_result record;
737        v_id integer;
738        e_id integer;
739        geom geoms;
740       
741        id integer;
742BEGIN
743       
744        id :=0;
745        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
746           'dijkstra_sp_delta_directed(''' ||
747           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
748           quote_literal(targetid) || ', ' || delta || ', false, false)'
749        LOOP
750                 geom.gid      := path_result.gid;
751                 geom.the_geom := path_result.the_geom;
752                 id := id+1;
753                 geom.id       := id;
754                 
755                 RETURN NEXT geom;
756
757        END LOOP;
758        RETURN;
759END;
760$$
761LANGUAGE 'plpgsql' VOLATILE STRICT;
762
763-----------------------------------------------------------------------
764-- Dijkstra function for directed graphs.
765-- Compute the shortest path using edges table, and return
766--  the result as a set of (gid integer, the_geom geometry) records.
767-- Also data clipping added to improve function performance.
768--
769-- Last changes: 14.02.2008
770-----------------------------------------------------------------------
771CREATE OR REPLACE FUNCTION dijkstra_sp_delta_directed(
772       varchar,int4, int4, float8, boolean, boolean)
773       RETURNS SETOF GEOMS AS
774$$
775DECLARE
776        geom_table ALIAS FOR $1;
777        sourceid ALIAS FOR $2;
778        targetid ALIAS FOR $3;
779        delta ALIAS FOR $4;
780        dir ALIAS FOR $5;
781        rc ALIAS FOR $6;
782
783        rec record;
784        r record;
785        path_result record;
786        v_id integer;
787        e_id integer;
788        geom geoms;
789       
790        srid integer;
791
792        source_x float8;
793        source_y float8;
794        target_x float8;
795        target_y float8;
796       
797        ll_x float8;
798        ll_y float8;
799        ur_x float8;
800        ur_y float8;
801       
802        query text;
803        id integer;
804BEGIN
805       
806        id :=0;
807        FOR rec IN EXECUTE
808            'select srid(the_geom) from ' ||
809            quote_ident(geom_table) || ' limit 1'
810        LOOP
811        END LOOP;
812        srid := rec.srid;
813
814        FOR rec IN EXECUTE
815            'select x(startpoint(the_geom)) as source_x from ' ||
816            quote_ident(geom_table) || ' where source = ' ||
817            sourceid ||  ' or target='||sourceid||' limit 1'
818        LOOP
819        END LOOP;
820        source_x := rec.source_x;
821       
822        FOR rec IN EXECUTE
823            'select y(startpoint(the_geom)) as source_y from ' ||
824            quote_ident(geom_table) || ' where source = ' ||
825            sourceid ||  ' or target='||sourceid||' limit 1'
826        LOOP
827        END LOOP;
828
829        source_y := rec.source_y;
830
831        FOR rec IN EXECUTE
832            'select x(startpoint(the_geom)) as target_x from ' ||
833            quote_ident(geom_table) || ' where source = ' ||
834            targetid ||  ' or target='||targetid||' limit 1'
835        LOOP
836        END LOOP;
837
838        target_x := rec.target_x;
839       
840        FOR rec IN EXECUTE
841            'select y(startpoint(the_geom)) as target_y from ' ||
842            quote_ident(geom_table) || ' where source = ' ||
843            targetid ||  ' or target='||targetid||' limit 1'
844        LOOP
845        END LOOP;
846        target_y := rec.target_y;
847
848
849        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
850           ' THEN '||source_x||' ELSE '||target_x||
851           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
852           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
853        LOOP
854        END LOOP;
855
856        ll_x := rec.ll_x;
857        ur_x := rec.ur_x;
858
859        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
860            target_y||' THEN '||source_y||' ELSE '||
861            target_y||' END as ll_y, CASE WHEN '||
862            source_y||'>'||target_y||' THEN '||
863            source_y||' ELSE '||target_y||' END as ur_y'
864        LOOP
865        END LOOP;
866
867        ll_y := rec.ll_y;
868        ur_y := rec.ur_y;
869
870        query := 'SELECT gid,the_geom FROM ' ||
871          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
872          'length::double precision as cost ';
873         
874        IF rc THEN query := query || ' , reverse_cost ';
875        END IF;
876
877        query := query || ' FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
878          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
879          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
880          quote_literal(sourceid) || ' , ' ||
881          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ), ' ||
882          quote_ident(geom_table) || ' where edge_id = gid ';
883         
884        FOR path_result IN EXECUTE query
885        LOOP
886                 geom.gid      := path_result.gid;
887                 geom.the_geom := path_result.the_geom;
888                 id := id+1;
889                 geom.id       := id;
890                 
891                 RETURN NEXT geom;
892
893        END LOOP;
894        RETURN;
895END;
896$$
897LANGUAGE 'plpgsql' VOLATILE STRICT;
898
899
900-----------------------------------------------------------------------
901-- A* function for undirected graphs.
902-- Compute the shortest path using edges table, and return
903--  the result as a set of (gid integer, the_geom geometry) records.
904-- Also data clipping added to improve function performance
905--  (specified by lower left and upper right box corners).
906--
907-- Last changes: 14.02.2008
908-----------------------------------------------------------------------
909CREATE OR REPLACE FUNCTION astar_sp_bbox(
910       varchar,int4, int4, float8, float8, float8, float8)
911       RETURNS SETOF GEOMS AS
912$$
913DECLARE
914        geom_table ALIAS FOR $1;
915        sourceid ALIAS FOR $2;
916        targetid ALIAS FOR $3;
917        ll_x ALIAS FOR $4;
918        ll_y ALIAS FOR $5;
919        ur_x ALIAS FOR $6;
920        ur_y ALIAS FOR $7;
921
922        rec record;
923        r record;
924        path_result record;
925        v_id integer;
926        e_id integer;
927        geom geoms;
928       
929        srid integer;
930
931        id integer;
932BEGIN
933       
934        id :=0;
935        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
936           'astar_sp_bbox_directed(''' ||
937           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
938           quote_literal(targetid) || ', ' || ll_x || ', ' || ll_y || ', ' ||
939           ur_x || ', ' || ur_y || ', false, false)'
940        LOOP
941
942               geom.gid      := path_result.gid;
943               geom.the_geom := path_result.the_geom;
944               id := id+1;
945               geom.id       := id;
946                 
947               RETURN NEXT geom;
948
949        END LOOP;
950        RETURN;
951END;
952$$
953LANGUAGE 'plpgsql' VOLATILE STRICT;
954
955-----------------------------------------------------------------------
956-- A* function for directed graphs.
957-- Compute the shortest path using edges table, and return
958--  the result as a set of (gid integer, the_geom geometry) records.
959-- Also data clipping added to improve function performance
960--  (specified by lower left and upper right box corners).
961--
962-- Last changes: 14.02.2008
963-----------------------------------------------------------------------
964CREATE OR REPLACE FUNCTION astar_sp_bbox_directed(
965       varchar,int4, int4, float8, float8, float8, float8, boolean, boolean)
966       RETURNS SETOF GEOMS AS
967$$
968DECLARE
969        geom_table ALIAS FOR $1;
970        sourceid ALIAS FOR $2;
971        targetid ALIAS FOR $3;
972        ll_x ALIAS FOR $4;
973        ll_y ALIAS FOR $5;
974        ur_x ALIAS FOR $6;
975        ur_y ALIAS FOR $7;
976        dir ALIAS FOR $8;
977        rc ALIAS FOR $9;
978
979        rec record;
980        r record;
981        path_result record;
982        v_id integer;
983        e_id integer;
984        geom geoms;
985       
986        srid integer;
987       
988        query text;
989
990        id integer;
991BEGIN
992       
993        id :=0;
994        FOR rec IN EXECUTE
995            'select srid(the_geom) from ' ||
996            quote_ident(geom_table) || ' limit 1'
997        LOOP
998        END LOOP;
999        srid := rec.srid;
1000       
1001        query := 'SELECT gid,the_geom FROM ' ||
1002           'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
1003           'target::integer, length::double precision as cost, ' ||
1004           'x1::double precision, y1::double precision, ' ||
1005           'x2::double precision, y2::double precision ';
1006           
1007        IF rc THEN query := query || ' , reverse_cost ';
1008        END IF;
1009           
1010        query := query || 'FROM ' ||
1011           quote_ident(geom_table) || ' where setSRID(''''BOX3D('||ll_x||' '||
1012           ll_y||','||ur_x||' '||ur_y||')''''::BOX3D, ' || srid ||
1013           ') && the_geom'', ' || quote_literal(sourceid) || ' , ' ||
1014           quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),'  ||
1015           quote_ident(geom_table) || ' where edge_id = gid ';
1016       
1017        FOR path_result IN EXECUTE query
1018        LOOP
1019               geom.gid      := path_result.gid;
1020               geom.the_geom := path_result.the_geom;
1021               id := id+1;
1022               geom.id       := id;
1023                 
1024               RETURN NEXT geom;
1025
1026        END LOOP;
1027        RETURN;
1028END;
1029$$
1030LANGUAGE 'plpgsql' VOLATILE STRICT;
1031
1032
1033CREATE OR REPLACE FUNCTION astar_sp(
1034       geom_table varchar, source int4, target int4)
1035       RETURNS SETOF GEOMS AS
1036$$
1037DECLARE
1038        r record;
1039        path_result record;
1040        v_id integer;
1041        e_id integer;
1042        geom geoms;
1043
1044        id integer;
1045BEGIN
1046       
1047        id :=0;
1048        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
1049           'astar_sp_directed(''' ||
1050           quote_ident(geom_table) || ''', ' || quote_literal(source) || ', ' ||
1051           quote_literal(target) || ', false, false)'
1052        LOOP
1053
1054              geom.gid      := path_result.gid;
1055              geom.the_geom := path_result.the_geom;
1056              id := id+1;
1057              geom.id       := id;
1058                 
1059              RETURN NEXT geom;
1060
1061        END LOOP;
1062        RETURN;
1063END;
1064$$
1065LANGUAGE 'plpgsql' VOLATILE STRICT;
1066
1067-----------------------------------------------------------------------
1068-- A* function for directed graphs.
1069-- Compute the shortest path using edges table, and return
1070--  the result as a set of (gid integer, the_geom geometry) records.
1071-- Also data clipping added to improve function performance.
1072--
1073-- Last changes: 14.02.2008
1074-----------------------------------------------------------------------
1075CREATE OR REPLACE FUNCTION astar_sp_directed(
1076       geom_table varchar, source int4, target int4, dir boolean, rc boolean)
1077       RETURNS SETOF GEOMS AS
1078$$
1079DECLARE
1080        r record;
1081        path_result record;
1082        v_id integer;
1083        e_id integer;
1084        geom geoms;
1085       
1086        query text;
1087
1088        id integer;
1089BEGIN
1090       
1091        id :=0;
1092        query := 'SELECT gid,the_geom FROM ' ||
1093           'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
1094           'target::integer, length::double precision as cost, ' ||
1095           'x1::double precision, y1::double precision, ' ||
1096           'x2::double precision, y2::double precision ';
1097           
1098        IF rc THEN query := query || ' , reverse_cost ';
1099        END IF;
1100
1101        query := query || 'FROM ' || quote_ident(geom_table) || ' '', ' ||
1102           quote_literal(source) || ' , ' ||
1103           quote_literal(target) || ' , '''||text(dir)||''', '''||text(rc)||'''), ' ||
1104           quote_ident(geom_table) || ' where edge_id = gid ';
1105           
1106        FOR path_result IN EXECUTE query
1107        LOOP
1108
1109              geom.gid      := path_result.gid;
1110              geom.the_geom := path_result.the_geom;
1111              id := id+1;
1112              geom.id       := id;
1113                 
1114              RETURN NEXT geom;
1115
1116        END LOOP;
1117        RETURN;
1118END;
1119$$
1120LANGUAGE 'plpgsql' VOLATILE STRICT;
1121
1122-----------------------------------------------------------------------
1123-- Shooting* function for directed graphs.
1124-- Compute the shortest path using edges table, and return
1125--  the result as a set of (gid integer, the_geom geometry) records.
1126--
1127-- Last changes: 14.02.2008
1128-----------------------------------------------------------------------
1129CREATE OR REPLACE FUNCTION shootingstar_sp(
1130       varchar,int4, int4, float8, varchar, boolean, boolean)
1131       RETURNS SETOF GEOMS AS
1132$$
1133DECLARE
1134        geom_table ALIAS FOR $1;
1135        sourceid ALIAS FOR $2;
1136        targetid ALIAS FOR $3;
1137        delta ALIAS FOR $4;
1138        cost_column ALIAS FOR $5;
1139        dir ALIAS FOR $6;
1140        rc ALIAS FOR $7;
1141
1142        rec record;
1143        r record;
1144        path_result record;
1145        v_id integer;
1146        e_id integer;
1147        geom geoms;
1148       
1149        srid integer;
1150
1151        source_x float8;
1152        source_y float8;
1153        target_x float8;
1154        target_y float8;
1155       
1156        ll_x float8;
1157        ll_y float8;
1158        ur_x float8;
1159        ur_y float8;
1160       
1161        query text;
1162
1163        id integer;
1164BEGIN
1165       
1166        id :=0;
1167        FOR rec IN EXECUTE
1168            'select srid(the_geom) from ' ||
1169            quote_ident(geom_table) || ' limit 1'
1170        LOOP
1171        END LOOP;
1172        srid := rec.srid;
1173       
1174        FOR rec IN EXECUTE
1175            'select x(startpoint(the_geom)) as source_x from ' ||
1176            quote_ident(geom_table) || ' where gid = '||sourceid
1177        LOOP
1178        END LOOP;
1179        source_x := rec.source_x;
1180       
1181        FOR rec IN EXECUTE
1182            'select y(startpoint(the_geom)) as source_y from ' ||
1183            quote_ident(geom_table) || ' where gid = ' ||sourceid
1184        LOOP
1185        END LOOP;
1186
1187        source_y := rec.source_y;
1188
1189        FOR rec IN EXECUTE
1190            'select x(startpoint(the_geom)) as target_x from ' ||
1191            quote_ident(geom_table) || ' where gid = ' ||targetid
1192        LOOP
1193        END LOOP;
1194
1195        target_x := rec.target_x;
1196       
1197        FOR rec IN EXECUTE
1198            'select y(startpoint(the_geom)) as target_y from ' ||
1199            quote_ident(geom_table) || ' where gid = ' ||targetid
1200        LOOP
1201        END LOOP;
1202        target_y := rec.target_y;
1203
1204        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
1205           ' THEN '||source_x||' ELSE '||target_x||
1206           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
1207           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
1208        LOOP
1209        END LOOP;
1210
1211        ll_x := rec.ll_x;
1212        ur_x := rec.ur_x;
1213
1214        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
1215            target_y||' THEN '||source_y||' ELSE '||
1216            target_y||' END as ll_y, CASE WHEN '||
1217            source_y||'>'||target_y||' THEN '||
1218            source_y||' ELSE '||target_y||' END as ur_y'
1219        LOOP
1220        END LOOP;
1221
1222        ll_y := rec.ll_y;
1223        ur_y := rec.ur_y;
1224
1225        query := 'SELECT gid,the_geom FROM ' ||
1226          'shortest_path_shooting_star(''SELECT gid as id, source::integer, ' ||
1227          'target::integer, '||cost_column||'::double precision as cost, ' ||
1228          'x1::double precision, y1::double precision, x2::double ' ||
1229          'precision, y2::double precision, rule::varchar, ' ||
1230          'to_cost::double precision ';
1231         
1232        IF rc THEN query := query || ' , reverse_cost '; 
1233        END IF;
1234         
1235        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
1236          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
1237          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
1238          quote_literal(sourceid) || ' , ' ||
1239          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),' ||
1240          quote_ident(geom_table) || ' where edge_id = gid ';
1241         
1242        FOR path_result IN EXECUTE query
1243        LOOP
1244                 geom.gid      := path_result.gid;
1245                 geom.the_geom := path_result.the_geom;
1246                 id := id+1;
1247                 geom.id       := id;
1248                 
1249                 RETURN NEXT geom;
1250
1251        END LOOP;
1252        RETURN;
1253END;
1254$$
1255LANGUAGE 'plpgsql' VOLATILE STRICT;
1256
1257-- COMMIT;
Note: See TracBrowser for help on using the browser.