root/tags/release-1.0-beta/routing_wrappers.sql

Revision 39, 38.5 KB (checked in by anton, 3 years ago)

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