root/trunk/core/sql/routing_core_wrappers.sql

Revision 163, 31.8 KB (checked in by anton, 3 years ago)

New improved topology script 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-- Update the cost column from the edges table, from the length of
72--  all lines which belong to an edge.
73--
74-- Last changes: 14.02.2008
75-----------------------------------------------------------------------
76CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar)
77       RETURNS VOID AS
78$$
79DECLARE
80BEGIN
81        BEGIN
82          EXECUTE 'CREATE INDEX ' || quote_ident(geom_table) ||
83                  '_edge_id_idx ON ' || quote_ident(geom_table) ||
84                  ' (edge_id)';
85        EXCEPTION
86                WHEN DUPLICATE_TABLE THEN
87                RAISE NOTICE 'Not creating index, already there';
88        END;
89
90        EXECUTE 'UPDATE ' || quote_ident(geom_table) ||
91              '_edges SET cost = (SELECT sum( length( g.the_geom ) ) FROM ' ||
92              quote_ident(geom_table) ||
93              ' g WHERE g.edge_id = id GROUP BY id)';
94
95        RETURN;
96END;
97$$
98LANGUAGE 'plpgsql' VOLATILE STRICT;
99
100
101CREATE TYPE geoms AS
102(
103  id integer,
104  gid integer,
105  the_geom geometry
106);
107
108-----------------------------------------------------------------------
109-- Dijkstra function for undirected graphs.
110-- Compute the shortest path using edges table, and return
111--  the result as a set of (gid integer, the_geom geometry) records.
112--
113-- Last changes: 14.02.2008
114-----------------------------------------------------------------------
115CREATE OR REPLACE FUNCTION dijkstra_sp(
116       geom_table varchar, source int4, target int4)
117       RETURNS SETOF GEOMS AS
118$$
119DECLARE
120        r record;
121        path_result record;
122        v_id integer;
123        e_id integer;
124        geom geoms;
125        id integer;
126BEGIN
127       
128        id :=0;
129       
130        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
131          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
132          'length::double precision as cost FROM ' ||
133          quote_ident(geom_table) || ''', ' || quote_literal(source) ||
134          ' , ' || quote_literal(target) || ' , false, false), ' ||
135          quote_ident(geom_table) || ' where edge_id = gid '
136        LOOP
137
138                 geom.gid      := path_result.gid;
139                 geom.the_geom := path_result.the_geom;
140                 id := id+1;
141                 geom.id       := id;
142                 
143                 RETURN NEXT geom;
144
145        END LOOP;
146        RETURN;
147END;
148$$
149LANGUAGE 'plpgsql' VOLATILE STRICT;
150
151-----------------------------------------------------------------------
152-- Dijkstra wrapper function for directed graphs.
153-- Compute the shortest path using edges table, and return
154--  the result as a set of (gid integer, the_geom geometry) records.
155--
156-- Last changes: 14.02.2008
157-----------------------------------------------------------------------
158CREATE OR REPLACE FUNCTION dijkstra_sp_directed(
159       geom_table varchar, source int4, target int4, dir boolean, rc boolean)
160       RETURNS SETOF GEOMS AS
161$$
162DECLARE
163        r record;
164        path_result record;
165        v_id integer;
166        e_id integer;
167        geom geoms;
168        query text;
169        id integer;
170BEGIN
171       
172        id :=0;
173       
174        query := 'SELECT gid,the_geom FROM ' ||
175          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
176          'length::double precision as cost ';
177         
178        IF rc THEN query := query || ', reverse_cost '; 
179        END IF;
180       
181        query := query || 'FROM ' ||  quote_ident(geom_table) || ''', ' || quote_literal(source) ||
182          ' , ' || quote_literal(target) || ' , '''||text(dir)||''', '''||text(rc)||'''), ' ||
183          quote_ident(geom_table) || ' where edge_id = gid ';
184
185        FOR path_result IN EXECUTE query
186        LOOP
187
188                 geom.gid      := path_result.gid;
189                 geom.the_geom := path_result.the_geom;
190                 id := id+1;
191                 geom.id       := id;
192                 
193                 RETURN NEXT geom;
194
195        END LOOP;
196        RETURN;
197END;
198$$
199LANGUAGE 'plpgsql' VOLATILE STRICT;
200
201-----------------------------------------------------------------------
202-- A* function for undirected graphs.
203-- Compute the shortest path using edges table, and return
204--  the result as a set of (gid integer, the_geom geometry) records.
205-- Also data clipping added to improve function performance.
206--
207-- Last changes: 14.02.2008
208-----------------------------------------------------------------------
209CREATE OR REPLACE FUNCTION astar_sp_delta(
210       varchar,int4, int4, float8)
211       RETURNS SETOF GEOMS AS
212$$
213DECLARE
214        geom_table ALIAS FOR $1;
215        sourceid ALIAS FOR $2;
216        targetid ALIAS FOR $3;
217        delta ALIAS FOR $4;
218
219        rec record;
220        r record;
221        path_result record;
222        v_id integer;
223        e_id integer;
224        geom geoms;
225       
226        id integer;
227BEGIN
228       
229        id :=0;
230
231        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
232           'astar_sp_delta_directed(''' ||
233           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
234           quote_literal(targetid) || ', ' || delta || ', false, false)'
235        LOOP
236
237                 geom.gid      := path_result.gid;
238                 geom.the_geom := path_result.the_geom;
239                 id := id+1;
240                 geom.id       := id;
241                 
242                 RETURN NEXT geom;
243
244        END LOOP;
245        RETURN;
246END;
247$$
248LANGUAGE 'plpgsql' VOLATILE STRICT;
249
250-----------------------------------------------------------------------
251-- A* function for directed graphs.
252-- Compute the shortest path using edges table, and return
253--  the result as a set of (gid integer, the_geom geometry) records.
254-- Also data clipping added to improve function performance.
255--
256-- Last changes: 14.02.2008
257-----------------------------------------------------------------------
258CREATE OR REPLACE FUNCTION astar_sp_delta_directed(
259       varchar,int4, int4, float8, boolean, boolean)
260       RETURNS SETOF GEOMS AS
261$$
262DECLARE
263        geom_table ALIAS FOR $1;
264        sourceid ALIAS FOR $2;
265        targetid ALIAS FOR $3;
266        delta ALIAS FOR $4;
267        dir ALIAS FOR $5;
268        rc ALIAS FOR $6;
269
270        rec record;
271        r record;
272        path_result record;
273        v_id integer;
274        e_id integer;
275        geom geoms;
276       
277        srid integer;
278
279        source_x float8;
280        source_y float8;
281        target_x float8;
282        target_y float8;
283       
284        ll_x float8;
285        ll_y float8;
286        ur_x float8;
287        ur_y float8;
288       
289        query text;
290
291        id integer;
292BEGIN
293       
294        id :=0;
295        FOR rec IN EXECUTE
296            'select srid(the_geom) from ' ||
297            quote_ident(geom_table) || ' limit 1'
298        LOOP
299        END LOOP;
300        srid := rec.srid;
301       
302        FOR rec IN EXECUTE
303            'select x(startpoint(the_geom)) as source_x from ' ||
304            quote_ident(geom_table) || ' where source = ' ||
305            sourceid ||  ' or target='||sourceid||' limit 1'
306        LOOP
307        END LOOP;
308        source_x := rec.source_x;
309       
310        FOR rec IN EXECUTE
311            'select y(startpoint(the_geom)) as source_y from ' ||
312            quote_ident(geom_table) || ' where source = ' ||
313            sourceid ||  ' or target='||sourceid||' limit 1'
314        LOOP
315        END LOOP;
316
317        source_y := rec.source_y;
318
319        FOR rec IN EXECUTE
320            'select x(startpoint(the_geom)) as target_x from ' ||
321            quote_ident(geom_table) || ' where source = ' ||
322            targetid ||  ' or target='||targetid||' limit 1'
323        LOOP
324        END LOOP;
325
326        target_x := rec.target_x;
327       
328        FOR rec IN EXECUTE
329            'select y(startpoint(the_geom)) as target_y from ' ||
330            quote_ident(geom_table) || ' where source = ' ||
331            targetid ||  ' or target='||targetid||' limit 1'
332        LOOP
333        END LOOP;
334        target_y := rec.target_y;
335
336        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
337           ' THEN '||source_x||' ELSE '||target_x||
338           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
339           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
340        LOOP
341        END LOOP;
342
343        ll_x := rec.ll_x;
344        ur_x := rec.ur_x;
345
346        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
347            target_y||' THEN '||source_y||' ELSE '||
348            target_y||' END as ll_y, CASE WHEN '||
349            source_y||'>'||target_y||' THEN '||
350            source_y||' ELSE '||target_y||' END as ur_y'
351        LOOP
352        END LOOP;
353
354        ll_y := rec.ll_y;
355        ur_y := rec.ur_y;
356
357        query := 'SELECT gid,the_geom FROM ' ||
358          'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
359          'target::integer, length::double precision as cost, ' ||
360          'x1::double precision, y1::double precision, x2::double ' ||
361          'precision, y2::double precision ';
362         
363        IF rc THEN query := query || ' , reverse_cost '; 
364        END IF;
365         
366        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
367          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
368          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
369          quote_literal(sourceid) || ' , ' ||
370          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),' ||
371          quote_ident(geom_table) || ' where edge_id = gid ';
372         
373        FOR path_result IN EXECUTE query
374        LOOP
375                 geom.gid      := path_result.gid;
376                 geom.the_geom := path_result.the_geom;
377                 id := id+1;
378                 geom.id       := id;
379                 
380                 RETURN NEXT geom;
381--
382--                v_id = path_result.vertex_id;
383--                e_id = path_result.edge_id;
384
385--                FOR r IN EXECUTE 'SELECT gid, the_geom FROM ' ||
386--                      quote_ident(geom_table) || '  WHERE gid = ' ||
387--                      quote_literal(e_id) LOOP
388--                        geom.gid := r.gid;
389--                        geom.the_geom := r.the_geom;
390--                        RETURN NEXT geom;
391--                END LOOP;
392
393        END LOOP;
394        RETURN;
395END;
396$$
397LANGUAGE 'plpgsql' VOLATILE STRICT;
398
399
400-----------------------------------------------------------------------
401-- A* function for undirected graphs.
402-- Compute the shortest path using edges table, and return
403--  the result as a set of (gid integer, the_geom geometry) records.
404-- Also data clipping added to improve function performance.
405-- Cost column name can be specified (last parameter)
406--
407-- Last changes: 14.02.2008
408-----------------------------------------------------------------------
409CREATE OR REPLACE FUNCTION astar_sp_delta_cc(
410       varchar,int4, int4, float8, varchar)
411       RETURNS SETOF GEOMS AS
412$$
413DECLARE
414        geom_table ALIAS FOR $1;
415        sourceid ALIAS FOR $2;
416        targetid ALIAS FOR $3;
417        delta ALIAS FOR $4;
418        cost_column ALIAS FOR $5;
419
420        rec record;
421        r record;
422        path_result record;
423        v_id integer;
424        e_id integer;
425        geom geoms;
426       
427        id integer;
428BEGIN
429       
430        id :=0;
431        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
432           'astar_sp_delta_cc_directed(''' ||
433           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
434           quote_literal(targetid) || ', ' || delta || ',' ||
435           quote_literal(cost_column) || ', false, false)'
436        LOOP
437
438                 geom.gid      := path_result.gid;
439                 geom.the_geom := path_result.the_geom;
440                 id := id+1;
441                 geom.id       := id;
442                 
443                 RETURN NEXT geom;
444
445        END LOOP;
446        RETURN;
447END;
448$$
449LANGUAGE 'plpgsql' VOLATILE STRICT;
450
451-----------------------------------------------------------------------
452-- A* function for directed graphs.
453-- Compute the shortest path using edges table, and return
454--  the result as a set of (gid integer, the_geom geometry) records.
455-- Also data clipping added to improve function performance.
456-- Cost column name can be specified (last parameter)
457--
458-- Last changes: 14.02.2008
459-----------------------------------------------------------------------
460CREATE OR REPLACE FUNCTION astar_sp_delta_cc_directed(
461       varchar,int4, int4, float8, varchar, boolean, boolean)
462       RETURNS SETOF GEOMS AS
463$$
464DECLARE
465        geom_table ALIAS FOR $1;
466        sourceid ALIAS FOR $2;
467        targetid ALIAS FOR $3;
468        delta ALIAS FOR $4;
469        cost_column ALIAS FOR $5;
470        dir ALIAS FOR $6;
471        rc ALIAS FOR $7;
472
473        rec record;
474        r record;
475        path_result record;
476        v_id integer;
477        e_id integer;
478        geom geoms;
479       
480        srid integer;
481
482        source_x float8;
483        source_y float8;
484        target_x float8;
485        target_y float8;
486       
487        ll_x float8;
488        ll_y float8;
489        ur_x float8;
490        ur_y float8;
491       
492        query text;
493
494        id integer;
495BEGIN
496       
497        id :=0;
498        FOR rec IN EXECUTE
499            'select srid(the_geom) from ' ||
500            quote_ident(geom_table) || ' limit 1'
501        LOOP
502        END LOOP;
503        srid := rec.srid;
504       
505        FOR rec IN EXECUTE
506            'select x(startpoint(the_geom)) as source_x from ' ||
507            quote_ident(geom_table) || ' where source = ' ||
508            sourceid || ' or target='||sourceid||' limit 1'
509        LOOP
510        END LOOP;
511        source_x := rec.source_x;
512       
513        FOR rec IN EXECUTE
514            'select y(startpoint(the_geom)) as source_y from ' ||
515            quote_ident(geom_table) || ' where source = ' ||
516            sourceid ||  ' or target='||sourceid||' limit 1'
517        LOOP
518        END LOOP;
519
520        source_y := rec.source_y;
521
522        FOR rec IN EXECUTE
523            'select x(startpoint(the_geom)) as target_x from ' ||
524            quote_ident(geom_table) || ' where source = ' ||
525            targetid ||  ' or target='||targetid||' limit 1'
526        LOOP
527        END LOOP;
528
529        target_x := rec.target_x;
530       
531        FOR rec IN EXECUTE
532            'select y(startpoint(the_geom)) as target_y from ' ||
533            quote_ident(geom_table) || ' where source = ' ||
534            targetid ||  ' or target='||targetid||' limit 1'
535        LOOP
536        END LOOP;
537        target_y := rec.target_y;
538
539
540        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
541           ' THEN '||source_x||' ELSE '||target_x||
542           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
543           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
544        LOOP
545        END LOOP;
546
547        ll_x := rec.ll_x;
548        ur_x := rec.ur_x;
549
550        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
551            target_y||' THEN '||source_y||' ELSE '||
552            target_y||' END as ll_y, CASE WHEN '||
553            source_y||'>'||target_y||' THEN '||
554            source_y||' ELSE '||target_y||' END as ur_y'
555        LOOP
556        END LOOP;
557
558        ll_y := rec.ll_y;
559        ur_y := rec.ur_y;
560
561        query := 'SELECT gid,the_geom FROM ' ||
562          'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
563          'target::integer, '||cost_column||'::double precision as cost, ' ||
564          'x1::double precision, y1::double precision, x2::double ' ||
565          'precision, y2::double precision ';
566       
567        IF rc THEN query := query || ' , reverse_cost ';
568        END IF;
569         
570        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
571          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
572          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
573          quote_literal(sourceid) || ' , ' ||
574          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),' ||
575          quote_ident(geom_table) || ' where edge_id = gid ';
576       
577        FOR path_result IN EXECUTE query
578        LOOP
579
580                 geom.gid      := path_result.gid;
581                 geom.the_geom := path_result.the_geom;
582                 id := id+1;
583                 geom.id       := id;
584                 
585                 RETURN NEXT geom;
586
587        END LOOP;
588        RETURN;
589END;
590$$
591LANGUAGE 'plpgsql' VOLATILE STRICT;
592
593
594-----------------------------------------------------------------------
595-- Dijkstra function for undirected graphs.
596-- Compute the shortest path using edges table, and return
597--  the result as a set of (gid integer, the_geom geometry) records.
598-- Also data clipping added to improve function performance.
599--
600-- Last changes: 14.02.2008
601-----------------------------------------------------------------------
602CREATE OR REPLACE FUNCTION dijkstra_sp_delta(
603       varchar,int4, int4, float8)
604       RETURNS SETOF GEOMS AS
605$$
606DECLARE
607        geom_table ALIAS FOR $1;
608        sourceid ALIAS FOR $2;
609        targetid ALIAS FOR $3;
610        delta ALIAS FOR $4;
611
612        rec record;
613        r record;
614        path_result record;
615        v_id integer;
616        e_id integer;
617        geom geoms;
618       
619        id integer;
620BEGIN
621       
622        id :=0;
623        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
624           'dijkstra_sp_delta_directed(''' ||
625           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
626           quote_literal(targetid) || ', ' || delta || ', false, false)'
627        LOOP
628                 geom.gid      := path_result.gid;
629                 geom.the_geom := path_result.the_geom;
630                 id := id+1;
631                 geom.id       := id;
632                 
633                 RETURN NEXT geom;
634
635        END LOOP;
636        RETURN;
637END;
638$$
639LANGUAGE 'plpgsql' VOLATILE STRICT;
640
641-----------------------------------------------------------------------
642-- Dijkstra function for directed graphs.
643-- Compute the shortest path using edges table, and return
644--  the result as a set of (gid integer, the_geom geometry) records.
645-- Also data clipping added to improve function performance.
646--
647-- Last changes: 14.02.2008
648-----------------------------------------------------------------------
649CREATE OR REPLACE FUNCTION dijkstra_sp_delta_directed(
650       varchar,int4, int4, float8, boolean, boolean)
651       RETURNS SETOF GEOMS AS
652$$
653DECLARE
654        geom_table ALIAS FOR $1;
655        sourceid ALIAS FOR $2;
656        targetid ALIAS FOR $3;
657        delta ALIAS FOR $4;
658        dir ALIAS FOR $5;
659        rc ALIAS FOR $6;
660
661        rec record;
662        r record;
663        path_result record;
664        v_id integer;
665        e_id integer;
666        geom geoms;
667       
668        srid integer;
669
670        source_x float8;
671        source_y float8;
672        target_x float8;
673        target_y float8;
674       
675        ll_x float8;
676        ll_y float8;
677        ur_x float8;
678        ur_y float8;
679       
680        query text;
681        id integer;
682BEGIN
683       
684        id :=0;
685        FOR rec IN EXECUTE
686            'select srid(the_geom) from ' ||
687            quote_ident(geom_table) || ' limit 1'
688        LOOP
689        END LOOP;
690        srid := rec.srid;
691
692        FOR rec IN EXECUTE
693            'select x(startpoint(the_geom)) as source_x from ' ||
694            quote_ident(geom_table) || ' where source = ' ||
695            sourceid ||  ' or target='||sourceid||' limit 1'
696        LOOP
697        END LOOP;
698        source_x := rec.source_x;
699       
700        FOR rec IN EXECUTE
701            'select y(startpoint(the_geom)) as source_y from ' ||
702            quote_ident(geom_table) || ' where source = ' ||
703            sourceid ||  ' or target='||sourceid||' limit 1'
704        LOOP
705        END LOOP;
706
707        source_y := rec.source_y;
708
709        FOR rec IN EXECUTE
710            'select x(startpoint(the_geom)) as target_x from ' ||
711            quote_ident(geom_table) || ' where source = ' ||
712            targetid ||  ' or target='||targetid||' limit 1'
713        LOOP
714        END LOOP;
715
716        target_x := rec.target_x;
717       
718        FOR rec IN EXECUTE
719            'select y(startpoint(the_geom)) as target_y from ' ||
720            quote_ident(geom_table) || ' where source = ' ||
721            targetid ||  ' or target='||targetid||' limit 1'
722        LOOP
723        END LOOP;
724        target_y := rec.target_y;
725
726
727        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
728           ' THEN '||source_x||' ELSE '||target_x||
729           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
730           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
731        LOOP
732        END LOOP;
733
734        ll_x := rec.ll_x;
735        ur_x := rec.ur_x;
736
737        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
738            target_y||' THEN '||source_y||' ELSE '||
739            target_y||' END as ll_y, CASE WHEN '||
740            source_y||'>'||target_y||' THEN '||
741            source_y||' ELSE '||target_y||' END as ur_y'
742        LOOP
743        END LOOP;
744
745        ll_y := rec.ll_y;
746        ur_y := rec.ur_y;
747
748        query := 'SELECT gid,the_geom FROM ' ||
749          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
750          'length::double precision as cost ';
751         
752        IF rc THEN query := query || ' , reverse_cost ';
753        END IF;
754
755        query := query || ' FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
756          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
757          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
758          quote_literal(sourceid) || ' , ' ||
759          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ), ' ||
760          quote_ident(geom_table) || ' where edge_id = gid ';
761         
762        FOR path_result IN EXECUTE query
763        LOOP
764                 geom.gid      := path_result.gid;
765                 geom.the_geom := path_result.the_geom;
766                 id := id+1;
767                 geom.id       := id;
768                 
769                 RETURN NEXT geom;
770
771        END LOOP;
772        RETURN;
773END;
774$$
775LANGUAGE 'plpgsql' VOLATILE STRICT;
776
777
778-----------------------------------------------------------------------
779-- A* function for undirected graphs.
780-- Compute the shortest path using edges table, and return
781--  the result as a set of (gid integer, the_geom geometry) records.
782-- Also data clipping added to improve function performance
783--  (specified by lower left and upper right box corners).
784--
785-- Last changes: 14.02.2008
786-----------------------------------------------------------------------
787CREATE OR REPLACE FUNCTION astar_sp_bbox(
788       varchar,int4, int4, float8, float8, float8, float8)
789       RETURNS SETOF GEOMS AS
790$$
791DECLARE
792        geom_table ALIAS FOR $1;
793        sourceid ALIAS FOR $2;
794        targetid ALIAS FOR $3;
795        ll_x ALIAS FOR $4;
796        ll_y ALIAS FOR $5;
797        ur_x ALIAS FOR $6;
798        ur_y ALIAS FOR $7;
799
800        rec record;
801        r record;
802        path_result record;
803        v_id integer;
804        e_id integer;
805        geom geoms;
806       
807        srid integer;
808
809        id integer;
810BEGIN
811       
812        id :=0;
813        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
814           'astar_sp_bbox_directed(''' ||
815           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
816           quote_literal(targetid) || ', ' || ll_x || ', ' || ll_y || ', ' ||
817           ur_x || ', ' || ur_y || ', false, false)'
818        LOOP
819
820               geom.gid      := path_result.gid;
821               geom.the_geom := path_result.the_geom;
822               id := id+1;
823               geom.id       := id;
824                 
825               RETURN NEXT geom;
826
827        END LOOP;
828        RETURN;
829END;
830$$
831LANGUAGE 'plpgsql' VOLATILE STRICT;
832
833-----------------------------------------------------------------------
834-- A* function for directed graphs.
835-- Compute the shortest path using edges table, and return
836--  the result as a set of (gid integer, the_geom geometry) records.
837-- Also data clipping added to improve function performance
838--  (specified by lower left and upper right box corners).
839--
840-- Last changes: 14.02.2008
841-----------------------------------------------------------------------
842CREATE OR REPLACE FUNCTION astar_sp_bbox_directed(
843       varchar,int4, int4, float8, float8, float8, float8, boolean, boolean)
844       RETURNS SETOF GEOMS AS
845$$
846DECLARE
847        geom_table ALIAS FOR $1;
848        sourceid ALIAS FOR $2;
849        targetid ALIAS FOR $3;
850        ll_x ALIAS FOR $4;
851        ll_y ALIAS FOR $5;
852        ur_x ALIAS FOR $6;
853        ur_y ALIAS FOR $7;
854        dir ALIAS FOR $8;
855        rc ALIAS FOR $9;
856
857        rec record;
858        r record;
859        path_result record;
860        v_id integer;
861        e_id integer;
862        geom geoms;
863       
864        srid integer;
865       
866        query text;
867
868        id integer;
869BEGIN
870       
871        id :=0;
872        FOR rec IN EXECUTE
873            'select srid(the_geom) from ' ||
874            quote_ident(geom_table) || ' limit 1'
875        LOOP
876        END LOOP;
877        srid := rec.srid;
878       
879        query := 'SELECT gid,the_geom FROM ' ||
880           'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
881           'target::integer, length::double precision as cost, ' ||
882           'x1::double precision, y1::double precision, ' ||
883           'x2::double precision, y2::double precision ';
884           
885        IF rc THEN query := query || ' , reverse_cost ';
886        END IF;
887           
888        query := query || 'FROM ' ||
889           quote_ident(geom_table) || ' where setSRID(''''BOX3D('||ll_x||' '||
890           ll_y||','||ur_x||' '||ur_y||')''''::BOX3D, ' || srid ||
891           ') && the_geom'', ' || quote_literal(sourceid) || ' , ' ||
892           quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),'  ||
893           quote_ident(geom_table) || ' where edge_id = gid ';
894       
895        FOR path_result IN EXECUTE query
896        LOOP
897               geom.gid      := path_result.gid;
898               geom.the_geom := path_result.the_geom;
899               id := id+1;
900               geom.id       := id;
901                 
902               RETURN NEXT geom;
903
904        END LOOP;
905        RETURN;
906END;
907$$
908LANGUAGE 'plpgsql' VOLATILE STRICT;
909
910
911CREATE OR REPLACE FUNCTION astar_sp(
912       geom_table varchar, source int4, target int4)
913       RETURNS SETOF GEOMS AS
914$$
915DECLARE
916        r record;
917        path_result record;
918        v_id integer;
919        e_id integer;
920        geom geoms;
921
922        id integer;
923BEGIN
924       
925        id :=0;
926        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
927           'astar_sp_directed(''' ||
928           quote_ident(geom_table) || ''', ' || quote_literal(source) || ', ' ||
929           quote_literal(target) || ', false, false)'
930        LOOP
931
932              geom.gid      := path_result.gid;
933              geom.the_geom := path_result.the_geom;
934              id := id+1;
935              geom.id       := id;
936                 
937              RETURN NEXT geom;
938
939        END LOOP;
940        RETURN;
941END;
942$$
943LANGUAGE 'plpgsql' VOLATILE STRICT;
944
945-----------------------------------------------------------------------
946-- A* function for directed graphs.
947-- Compute the shortest path using edges table, and return
948--  the result as a set of (gid integer, the_geom geometry) records.
949-- Also data clipping added to improve function performance.
950--
951-- Last changes: 14.02.2008
952-----------------------------------------------------------------------
953CREATE OR REPLACE FUNCTION astar_sp_directed(
954       geom_table varchar, source int4, target int4, dir boolean, rc boolean)
955       RETURNS SETOF GEOMS AS
956$$
957DECLARE
958        r record;
959        path_result record;
960        v_id integer;
961        e_id integer;
962        geom geoms;
963       
964        query text;
965
966        id integer;
967BEGIN
968       
969        id :=0;
970        query := 'SELECT gid,the_geom FROM ' ||
971           'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
972           'target::integer, length::double precision as cost, ' ||
973           'x1::double precision, y1::double precision, ' ||
974           'x2::double precision, y2::double precision ';
975           
976        IF rc THEN query := query || ' , reverse_cost ';
977        END IF;
978
979        query := query || 'FROM ' || quote_ident(geom_table) || ' '', ' ||
980           quote_literal(source) || ' , ' ||
981           quote_literal(target) || ' , '''||text(dir)||''', '''||text(rc)||'''), ' ||
982           quote_ident(geom_table) || ' where edge_id = gid ';
983           
984        FOR path_result IN EXECUTE query
985        LOOP
986
987              geom.gid      := path_result.gid;
988              geom.the_geom := path_result.the_geom;
989              id := id+1;
990              geom.id       := id;
991                 
992              RETURN NEXT geom;
993
994        END LOOP;
995        RETURN;
996END;
997$$
998LANGUAGE 'plpgsql' VOLATILE STRICT;
999
1000-----------------------------------------------------------------------
1001-- Shooting* function for directed graphs.
1002-- Compute the shortest path using edges table, and return
1003--  the result as a set of (gid integer, the_geom geometry) records.
1004--
1005-- Last changes: 14.02.2008
1006-----------------------------------------------------------------------
1007CREATE OR REPLACE FUNCTION shootingstar_sp(
1008       varchar,int4, int4, float8, varchar, boolean, boolean)
1009       RETURNS SETOF GEOMS AS
1010$$
1011DECLARE
1012        geom_table ALIAS FOR $1;
1013        sourceid ALIAS FOR $2;
1014        targetid ALIAS FOR $3;
1015        delta ALIAS FOR $4;
1016        cost_column ALIAS FOR $5;
1017        dir ALIAS FOR $6;
1018        rc ALIAS FOR $7;
1019
1020        rec record;
1021        r record;
1022        path_result record;
1023        v_id integer;
1024        e_id integer;
1025        geom geoms;
1026       
1027        srid integer;
1028
1029        source_x float8;
1030        source_y float8;
1031        target_x float8;
1032        target_y float8;
1033       
1034        ll_x float8;
1035        ll_y float8;
1036        ur_x float8;
1037        ur_y float8;
1038       
1039        query text;
1040
1041        id integer;
1042BEGIN
1043       
1044        id :=0;
1045        FOR rec IN EXECUTE
1046            'select srid(the_geom) from ' ||
1047            quote_ident(geom_table) || ' limit 1'
1048        LOOP
1049        END LOOP;
1050        srid := rec.srid;
1051       
1052        FOR rec IN EXECUTE
1053            'select x(startpoint(the_geom)) as source_x from ' ||
1054            quote_ident(geom_table) || ' where gid = '||sourceid
1055        LOOP
1056        END LOOP;
1057        source_x := rec.source_x;
1058       
1059        FOR rec IN EXECUTE
1060            'select y(startpoint(the_geom)) as source_y from ' ||
1061            quote_ident(geom_table) || ' where gid = ' ||sourceid
1062        LOOP
1063        END LOOP;
1064
1065        source_y := rec.source_y;
1066
1067        FOR rec IN EXECUTE
1068            'select x(startpoint(the_geom)) as target_x from ' ||
1069            quote_ident(geom_table) || ' where gid = ' ||targetid
1070        LOOP
1071        END LOOP;
1072
1073        target_x := rec.target_x;
1074       
1075        FOR rec IN EXECUTE
1076            'select y(startpoint(the_geom)) as target_y from ' ||
1077            quote_ident(geom_table) || ' where gid = ' ||targetid
1078        LOOP
1079        END LOOP;
1080        target_y := rec.target_y;
1081
1082        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
1083           ' THEN '||source_x||' ELSE '||target_x||
1084           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
1085           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
1086        LOOP
1087        END LOOP;
1088
1089        ll_x := rec.ll_x;
1090        ur_x := rec.ur_x;
1091
1092        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
1093            target_y||' THEN '||source_y||' ELSE '||
1094            target_y||' END as ll_y, CASE WHEN '||
1095            source_y||'>'||target_y||' THEN '||
1096            source_y||' ELSE '||target_y||' END as ur_y'
1097        LOOP
1098        END LOOP;
1099
1100        ll_y := rec.ll_y;
1101        ur_y := rec.ur_y;
1102
1103        query := 'SELECT gid,the_geom FROM ' ||
1104          'shortest_path_shooting_star(''SELECT gid as id, source::integer, ' ||
1105          'target::integer, '||cost_column||'::double precision as cost, ' ||
1106          'x1::double precision, y1::double precision, x2::double ' ||
1107          'precision, y2::double precision, rule::varchar, ' ||
1108          'to_cost::double precision ';
1109         
1110        IF rc THEN query := query || ' , reverse_cost '; 
1111        END IF;
1112         
1113        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
1114          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
1115          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
1116          quote_literal(sourceid) || ' , ' ||
1117          quote_literal(targetid) || ' , '''||text(dir)||''', '''||text(rc)||''' ),' ||
1118          quote_ident(geom_table) || ' where edge_id = gid ';
1119         
1120        FOR path_result IN EXECUTE query
1121        LOOP
1122                 geom.gid      := path_result.gid;
1123                 geom.the_geom := path_result.the_geom;
1124                 id := id+1;
1125                 geom.id       := id;
1126                 
1127                 RETURN NEXT geom;
1128
1129        END LOOP;
1130        RETURN;
1131END;
1132$$
1133LANGUAGE 'plpgsql' VOLATILE STRICT;
1134
1135-- COMMIT;
Note: See TracBrowser for help on using the browser.