root/branches/pgrouting-light/sql/routing_postgis.sql

Revision 25, 39.3 KB (checked in by anton, 3 years ago)

light and extra branches
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_id
29-- target_id
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_id';
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_id 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_id and target_id 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_id = ' || source_id ||
167                                    ', target_id = ' || 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  gid int4,
210  the_geom geometry
211);
212
213-----------------------------------------------------------------------
214-- Compute the shortest path using edges and vertices table, and return
215--  the result as a set of (gid integer, the_geom gemoetry) records.
216-- This function uses the internal vertices identifiers.
217-----------------------------------------------------------------------
218CREATE OR REPLACE FUNCTION shortest_path_as_geometry_internal_id(
219       geom_table varchar, source int4, target int4)
220       RETURNS SETOF GEOMS AS
221$$
222DECLARE
223        r record;
224        path_result record;
225        v_id integer;
226        e_id integer;
227        geom geoms;
228BEGIN
229       
230        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
231          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
232          'length::double precision as cost FROM ' ||
233          quote_ident(geom_table) || ''', ' || quote_literal(source) ||
234          ' , ' || quote_literal(target) || ' , false, false), ' ||
235          quote_ident(geom_table) || ' where edge_id = gid '
236        LOOP
237
238                 geom.gid      := path_result.gid;
239                 geom.the_geom := path_result.the_geom;
240                 
241                 RETURN NEXT geom;
242
243        END LOOP;
244        RETURN;
245END;
246$$
247LANGUAGE 'plpgsql' VOLATILE STRICT;
248
249CREATE OR REPLACE FUNCTION shortest_path_as_geometry_internal_id_directed(
250       geom_table varchar, source int4, target int4, dir boolean, rc boolean)
251       RETURNS SETOF GEOMS AS
252$$
253DECLARE
254        r record;
255        path_result record;
256        v_id integer;
257        e_id integer;
258        geom geoms;
259        query text;
260BEGIN
261       
262        query := 'SELECT gid,the_geom FROM ' ||
263          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
264          'length::double precision as cost ';
265         
266        IF rc THEN query := query || ', reverse_cost '; 
267        END IF;
268       
269        query := query || 'FROM ' ||  quote_ident(geom_table) || ''', ' || quote_literal(source) ||
270          ' , ' || quote_literal(target) || ' , '''||dir||''', '''||rc||'''), ' ||
271          quote_ident(geom_table) || ' where edge_id = gid ';
272
273        FOR path_result IN EXECUTE query
274        LOOP
275
276                 geom.gid      := path_result.gid;
277                 geom.the_geom := path_result.the_geom;
278                 
279                 RETURN NEXT geom;
280
281        END LOOP;
282        RETURN;
283END;
284$$
285LANGUAGE 'plpgsql' VOLATILE STRICT;
286
287-----------------------------------------------------------------------
288-- Compute the shortest path using edges and vertices table, and return
289--  the result as a set of (gid integer, the_geom gemoetry) records.
290-----------------------------------------------------------------------
291CREATE OR REPLACE FUNCTION shortest_path_as_geometry(geom_table varchar,
292       geom_source anyelement,geom_target anyelement)
293       RETURNS SETOF GEOMS AS
294$$
295DECLARE
296        r record;
297        source int4;
298        target int4;
299        path_result record;
300        v_id integer;
301        e_id integer;
302        geom geoms;
303BEGIN
304        FOR r IN EXECUTE 'SELECT id FROM ' || quote_ident(geom_table) ||
305           '_vertices WHERE geom_id = ' || quote_literal(geom_source) LOOP
306
307                source = r.id;
308
309        END LOOP;
310
311        IF source IS NULL THEN
312                RAISE EXCEPTION 'Can''t find source edge';
313        END IF;
314
315        FOR r IN EXECUTE 'SELECT id FROM ' || quote_ident(geom_table) ||
316            '_vertices WHERE geom_id = ' || quote_literal(geom_target) LOOP
317                target = r.id;
318        END LOOP;
319
320        IF target IS NULL THEN
321                RAISE EXCEPTION 'Can''t find target edge';
322        END IF;
323       
324        FOR geom IN SELECT * FROM
325          shortest_path_as_geometry_internal_id(geom_table,
326                                                source, target) LOOP
327                RETURN NEXT geom;
328        END LOOP;
329
330        RETURN;
331END;
332$$
333LANGUAGE 'plpgsql' VOLATILE STRICT;
334
335
336-----------------------------------------------------------------------
337-- Compute the shortest path using edges and vertices table, and return
338--  the result as a set of (gid integer, the_geom gemoetry) records.
339-- This function uses the internal vertices identifiers.
340-- Also data clipping added to improve function performance.
341-----------------------------------------------------------------------
342CREATE OR REPLACE FUNCTION shortest_path_astar1_as_geometry_internal_id(
343       varchar,int4, int4, float8)
344       RETURNS SETOF GEOMS AS
345$$
346DECLARE
347        geom_table ALIAS FOR $1;
348        sourceid ALIAS FOR $2;
349        targetid ALIAS FOR $3;
350        delta ALIAS FOR $4;
351
352        rec record;
353        r record;
354        path_result record;
355        v_id integer;
356        e_id integer;
357        geom geoms;
358       
359BEGIN
360        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
361           'shortest_path_astar1_as_geometry_internal_id_directed(''' ||
362           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
363           quote_literal(targetid) || ', ' || delta || ', false, false)'
364        LOOP
365
366                 geom.gid      := path_result.gid;
367                 geom.the_geom := path_result.the_geom;
368                 
369                 RETURN NEXT geom;
370--
371--                v_id = path_result.vertex_id;
372--                e_id = path_result.edge_id;
373
374--                FOR r IN EXECUTE 'SELECT gid, the_geom FROM ' ||
375--                      quote_ident(geom_table) || '  WHERE gid = ' ||
376--                      quote_literal(e_id) LOOP
377--                        geom.gid := r.gid;
378--                        geom.the_geom := r.the_geom;
379--                        RETURN NEXT geom;
380--                END LOOP;
381
382        END LOOP;
383        RETURN;
384END;
385$$
386LANGUAGE 'plpgsql' VOLATILE STRICT;
387
388CREATE OR REPLACE FUNCTION shortest_path_astar1_as_geometry_internal_id_directed(
389       varchar,int4, int4, float8, boolean, boolean)
390       RETURNS SETOF GEOMS AS
391$$
392DECLARE
393        geom_table ALIAS FOR $1;
394        sourceid ALIAS FOR $2;
395        targetid ALIAS FOR $3;
396        delta ALIAS FOR $4;
397        dir ALIAS FOR $5;
398        rc ALIAS FOR $6;
399
400        rec record;
401        r record;
402        path_result record;
403        v_id integer;
404        e_id integer;
405        geom geoms;
406       
407        srid integer;
408
409        source_x float8;
410        source_y float8;
411        target_x float8;
412        target_y float8;
413       
414        ll_x float8;
415        ll_y float8;
416        ur_x float8;
417        ur_y float8;
418       
419        query text;
420
421BEGIN
422        FOR rec IN EXECUTE
423            'select srid(the_geom) from ' ||
424            quote_ident(geom_table) || ' limit 1'
425        LOOP
426        END LOOP;
427        srid := rec.srid;
428       
429        FOR rec IN EXECUTE
430            'select x(startpoint(the_geom)) as source_x from ' ||
431            quote_ident(geom_table) || ' where source = ' ||
432            sourceid ||  ' or target='||sourceid||' limit 1'
433        LOOP
434        END LOOP;
435        source_x := rec.source_x;
436       
437        FOR rec IN EXECUTE
438            'select y(startpoint(the_geom)) as source_y from ' ||
439            quote_ident(geom_table) || ' where source = ' ||
440            sourceid ||  ' or target='||sourceid||' limit 1'
441        LOOP
442        END LOOP;
443
444        source_y := rec.source_y;
445
446        FOR rec IN EXECUTE
447            'select x(startpoint(the_geom)) as target_x from ' ||
448            quote_ident(geom_table) || ' where source = ' ||
449            targetid ||  ' or target='||targetid||' limit 1'
450        LOOP
451        END LOOP;
452
453        target_x := rec.target_x;
454       
455        FOR rec IN EXECUTE
456            'select y(startpoint(the_geom)) as target_y from ' ||
457            quote_ident(geom_table) || ' where source = ' ||
458            targetid ||  ' or target='||targetid||' limit 1'
459        LOOP
460        END LOOP;
461        target_y := rec.target_y;
462
463        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
464           ' THEN '||source_x||' ELSE '||target_x||
465           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
466           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
467        LOOP
468        END LOOP;
469
470        ll_x := rec.ll_x;
471        ur_x := rec.ur_x;
472
473        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
474            target_y||' THEN '||source_y||' ELSE '||
475            target_y||' END as ll_y, CASE WHEN '||
476            source_y||'>'||target_y||' THEN '||
477            source_y||' ELSE '||target_y||' END as ur_y'
478        LOOP
479        END LOOP;
480
481        ll_y := rec.ll_y;
482        ur_y := rec.ur_y;
483
484        query := 'SELECT gid,the_geom FROM ' ||
485          'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
486          'target::integer, length::double precision as cost, ' ||
487          'x1::double precision, y1::double precision, x2::double ' ||
488          'precision, y2::double precision ';
489         
490        IF rc THEN query := query || ' , reverse_cost '; 
491        END IF;
492         
493        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
494          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
495          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
496          quote_literal(sourceid) || ' , ' ||
497          quote_literal(targetid) || ' , '''||dir||''', '''||rc||''' ),' ||
498          quote_ident(geom_table) || ' where edge_id = gid ';
499         
500        FOR path_result IN EXECUTE query
501        LOOP
502                 geom.gid      := path_result.gid;
503                 geom.the_geom := path_result.the_geom;
504                 
505                 RETURN NEXT geom;
506--
507--                v_id = path_result.vertex_id;
508--                e_id = path_result.edge_id;
509
510--                FOR r IN EXECUTE 'SELECT gid, the_geom FROM ' ||
511--                      quote_ident(geom_table) || '  WHERE gid = ' ||
512--                      quote_literal(e_id) LOOP
513--                        geom.gid := r.gid;
514--                        geom.the_geom := r.the_geom;
515--                        RETURN NEXT geom;
516--                END LOOP;
517
518        END LOOP;
519        RETURN;
520END;
521$$
522LANGUAGE 'plpgsql' VOLATILE STRICT;
523
524
525CREATE OR REPLACE FUNCTION shortest_path_astar3_as_geometry_internal_id(
526       varchar,int4, int4, float8, varchar)
527       RETURNS SETOF GEOMS AS
528$$
529DECLARE
530        geom_table ALIAS FOR $1;
531        sourceid ALIAS FOR $2;
532        targetid ALIAS FOR $3;
533        delta ALIAS FOR $4;
534        cost_column ALIAS FOR $5;
535
536        rec record;
537        r record;
538        path_result record;
539        v_id integer;
540        e_id integer;
541        geom geoms;
542       
543BEGIN
544        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
545           'shortest_path_astar3_as_geometry_internal_id_directed(''' ||
546           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
547           quote_literal(targetid) || ', ' || delta || ',' ||
548           quote_literal(cost_column) || ', false, false)'
549        LOOP
550
551                 geom.gid      := path_result.gid;
552                 geom.the_geom := path_result.the_geom;
553                 
554                 RETURN NEXT geom;
555
556        END LOOP;
557        RETURN;
558END;
559$$
560LANGUAGE 'plpgsql' VOLATILE STRICT;
561
562CREATE OR REPLACE FUNCTION shortest_path_astar3_as_geometry_internal_id_directed(
563       varchar,int4, int4, float8, varchar, boolean, boolean)
564       RETURNS SETOF GEOMS AS
565$$
566DECLARE
567        geom_table ALIAS FOR $1;
568        sourceid ALIAS FOR $2;
569        targetid ALIAS FOR $3;
570        delta ALIAS FOR $4;
571        cost_column ALIAS FOR $5;
572        dir ALIAS FOR $6;
573        rc ALIAS FOR $7;
574
575        rec record;
576        r record;
577        path_result record;
578        v_id integer;
579        e_id integer;
580        geom geoms;
581       
582        srid integer;
583
584        source_x float8;
585        source_y float8;
586        target_x float8;
587        target_y float8;
588       
589        ll_x float8;
590        ll_y float8;
591        ur_x float8;
592        ur_y float8;
593       
594        query text;
595
596BEGIN
597        FOR rec IN EXECUTE
598            'select srid(the_geom) from ' ||
599            quote_ident(geom_table) || ' limit 1'
600        LOOP
601        END LOOP;
602        srid := rec.srid;
603       
604        FOR rec IN EXECUTE
605            'select x(startpoint(the_geom)) as source_x from ' ||
606            quote_ident(geom_table) || ' where source = ' ||
607            sourceid || ' or target='||sourceid||' limit 1'
608        LOOP
609        END LOOP;
610        source_x := rec.source_x;
611       
612        FOR rec IN EXECUTE
613            'select y(startpoint(the_geom)) as source_y from ' ||
614            quote_ident(geom_table) || ' where source = ' ||
615            sourceid ||  ' or target='||sourceid||' limit 1'
616        LOOP
617        END LOOP;
618
619        source_y := rec.source_y;
620
621        FOR rec IN EXECUTE
622            'select x(startpoint(the_geom)) as target_x from ' ||
623            quote_ident(geom_table) || ' where source = ' ||
624            targetid ||  ' or target='||targetid||' limit 1'
625        LOOP
626        END LOOP;
627
628        target_x := rec.target_x;
629       
630        FOR rec IN EXECUTE
631            'select y(startpoint(the_geom)) as target_y from ' ||
632            quote_ident(geom_table) || ' where source = ' ||
633            targetid ||  ' or target='||targetid||' limit 1'
634        LOOP
635        END LOOP;
636        target_y := rec.target_y;
637
638
639        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
640           ' THEN '||source_x||' ELSE '||target_x||
641           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
642           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
643        LOOP
644        END LOOP;
645
646        ll_x := rec.ll_x;
647        ur_x := rec.ur_x;
648
649        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
650            target_y||' THEN '||source_y||' ELSE '||
651            target_y||' END as ll_y, CASE WHEN '||
652            source_y||'>'||target_y||' THEN '||
653            source_y||' ELSE '||target_y||' END as ur_y'
654        LOOP
655        END LOOP;
656
657        ll_y := rec.ll_y;
658        ur_y := rec.ur_y;
659
660        query := 'SELECT gid,the_geom FROM ' ||
661          'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
662          'target::integer, '||cost_column||'::double precision as cost, ' ||
663          'x1::double precision, y1::double precision, x2::double ' ||
664          'precision, y2::double precision ';
665       
666        IF rc THEN query := query || ' , reverse_cost ';
667        END IF;
668         
669        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
670          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
671          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
672          quote_literal(sourceid) || ' , ' ||
673          quote_literal(targetid) || ' , '''||dir||''', '''||rc||''' ),' ||
674          quote_ident(geom_table) || ' where edge_id = gid ';
675       
676        FOR path_result IN EXECUTE query
677        LOOP
678
679                 geom.gid      := path_result.gid;
680                 geom.the_geom := path_result.the_geom;
681                 
682                 RETURN NEXT geom;
683
684        END LOOP;
685        RETURN;
686END;
687$$
688LANGUAGE 'plpgsql' VOLATILE STRICT;
689
690
691CREATE OR REPLACE FUNCTION shortest_path_dijkstra1_as_geometry_internal_id(
692       varchar,int4, int4, float8)
693       RETURNS SETOF GEOMS AS
694$$
695DECLARE
696        geom_table ALIAS FOR $1;
697        sourceid ALIAS FOR $2;
698        targetid ALIAS FOR $3;
699        delta ALIAS FOR $4;
700
701        rec record;
702        r record;
703        path_result record;
704        v_id integer;
705        e_id integer;
706        geom geoms;
707       
708BEGIN
709        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
710           'shortest_path_dijkstra1_as_geometry_internal_id_directed(''' ||
711           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
712           quote_literal(targetid) || ', ' || delta || ', false, false)'
713        LOOP
714                 geom.gid      := path_result.gid;
715                 geom.the_geom := path_result.the_geom;
716                 
717                 RETURN NEXT geom;
718
719        END LOOP;
720        RETURN;
721END;
722$$
723LANGUAGE 'plpgsql' VOLATILE STRICT;
724
725CREATE OR REPLACE FUNCTION shortest_path_dijkstra1_as_geometry_internal_id_directed(
726       varchar,int4, int4, float8, boolean, boolean)
727       RETURNS SETOF GEOMS AS
728$$
729DECLARE
730        geom_table ALIAS FOR $1;
731        sourceid ALIAS FOR $2;
732        targetid ALIAS FOR $3;
733        delta ALIAS FOR $4;
734        dir ALIAS FOR $5;
735        rc ALIAS FOR $6;
736
737        rec record;
738        r record;
739        path_result record;
740        v_id integer;
741        e_id integer;
742        geom geoms;
743       
744        srid integer;
745
746        source_x float8;
747        source_y float8;
748        target_x float8;
749        target_y float8;
750       
751        ll_x float8;
752        ll_y float8;
753        ur_x float8;
754        ur_y float8;
755       
756        query text;
757BEGIN
758        FOR rec IN EXECUTE
759            'select srid(the_geom) from ' ||
760            quote_ident(geom_table) || ' limit 1'
761        LOOP
762        END LOOP;
763        srid := rec.srid;
764
765        FOR rec IN EXECUTE
766            'select x(startpoint(the_geom)) as source_x from ' ||
767            quote_ident(geom_table) || ' where source = ' ||
768            sourceid ||  ' or target='||sourceid||' limit 1'
769        LOOP
770        END LOOP;
771        source_x := rec.source_x;
772       
773        FOR rec IN EXECUTE
774            'select y(startpoint(the_geom)) as source_y from ' ||
775            quote_ident(geom_table) || ' where source = ' ||
776            sourceid ||  ' or target='||sourceid||' limit 1'
777        LOOP
778        END LOOP;
779
780        source_y := rec.source_y;
781
782        FOR rec IN EXECUTE
783            'select x(startpoint(the_geom)) as target_x from ' ||
784            quote_ident(geom_table) || ' where source = ' ||
785            targetid ||  ' or target='||targetid||' limit 1'
786        LOOP
787        END LOOP;
788
789        target_x := rec.target_x;
790       
791        FOR rec IN EXECUTE
792            'select y(startpoint(the_geom)) as target_y from ' ||
793            quote_ident(geom_table) || ' where source = ' ||
794            targetid ||  ' or target='||targetid||' limit 1'
795        LOOP
796        END LOOP;
797        target_y := rec.target_y;
798
799
800        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
801           ' THEN '||source_x||' ELSE '||target_x||
802           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
803           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
804        LOOP
805        END LOOP;
806
807        ll_x := rec.ll_x;
808        ur_x := rec.ur_x;
809
810        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
811            target_y||' THEN '||source_y||' ELSE '||
812            target_y||' END as ll_y, CASE WHEN '||
813            source_y||'>'||target_y||' THEN '||
814            source_y||' ELSE '||target_y||' END as ur_y'
815        LOOP
816        END LOOP;
817
818        ll_y := rec.ll_y;
819        ur_y := rec.ur_y;
820
821        query := 'SELECT gid,the_geom FROM ' ||
822          'shortest_path(''SELECT gid as id, source::integer, target::integer, ' ||
823          'length::double precision as cost ';
824         
825        IF rc THEN query := query || ' , reverse_cost ';
826        END IF;
827
828        query := query || ' FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
829          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
830          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
831          quote_literal(sourceid) || ' , ' ||
832          quote_literal(targetid) || ' , '''||dir||''', '''||rc||''' ), ' ||
833          quote_ident(geom_table) || ' where edge_id = gid ';
834         
835        FOR path_result IN EXECUTE query
836        LOOP
837                 geom.gid      := path_result.gid;
838                 geom.the_geom := path_result.the_geom;
839                 
840                 RETURN NEXT geom;
841
842        END LOOP;
843        RETURN;
844END;
845$$
846LANGUAGE 'plpgsql' VOLATILE STRICT;
847
848
849CREATE OR REPLACE FUNCTION shortest_path_astar2_as_geometry_internal_id(
850       varchar,int4, int4, float8, float8, float8, float8)
851       RETURNS SETOF GEOMS AS
852$$
853DECLARE
854        geom_table ALIAS FOR $1;
855        sourceid ALIAS FOR $2;
856        targetid ALIAS FOR $3;
857        ll_x ALIAS FOR $4;
858        ll_y ALIAS FOR $5;
859        ur_x ALIAS FOR $6;
860        ur_y ALIAS FOR $7;
861
862        rec record;
863        r record;
864        path_result record;
865        v_id integer;
866        e_id integer;
867        geom geoms;
868       
869        srid integer;
870
871
872BEGIN
873        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
874           'shortest_path_astar2_as_geometry_internal_id_directed(''' ||
875           quote_ident(geom_table) || ''', ' || quote_literal(sourceid) || ', ' ||
876           quote_literal(targetid) || ', ' || ll_x || ', ' || ll_y || ', ' ||
877           ur_x || ', ' || ur_y || ', false, false)'
878        LOOP
879
880               geom.gid      := path_result.gid;
881               geom.the_geom := path_result.the_geom;
882                 
883               RETURN NEXT geom;
884
885        END LOOP;
886        RETURN;
887END;
888$$
889LANGUAGE 'plpgsql' VOLATILE STRICT;
890
891CREATE OR REPLACE FUNCTION shortest_path_astar2_as_geometry_internal_id_directed(
892       varchar,int4, int4, float8, float8, float8, float8, boolean, boolean)
893       RETURNS SETOF GEOMS AS
894$$
895DECLARE
896        geom_table ALIAS FOR $1;
897        sourceid ALIAS FOR $2;
898        targetid ALIAS FOR $3;
899        ll_x ALIAS FOR $4;
900        ll_y ALIAS FOR $5;
901        ur_x ALIAS FOR $6;
902        ur_y ALIAS FOR $7;
903        dir ALIAS FOR $8;
904        rc ALIAS FOR $9;
905
906        rec record;
907        r record;
908        path_result record;
909        v_id integer;
910        e_id integer;
911        geom geoms;
912       
913        srid integer;
914       
915        query text;
916
917BEGIN
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                 
946               RETURN NEXT geom;
947
948        END LOOP;
949        RETURN;
950END;
951$$
952LANGUAGE 'plpgsql' VOLATILE STRICT;
953
954
955CREATE OR REPLACE FUNCTION shortest_path_astar_as_geometry_internal_id(
956       geom_table varchar, source int4, target int4)
957       RETURNS SETOF GEOMS AS
958$$
959DECLARE
960        r record;
961        path_result record;
962        v_id integer;
963        e_id integer;
964        geom geoms;
965
966BEGIN
967        FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' ||
968           'shortest_path_astar_as_geometry_internal_id_directed(''' ||
969           quote_ident(geom_table) || ''', ' || quote_literal(source) || ', ' ||
970           quote_literal(target) || ', false, false)'
971        LOOP
972
973              geom.gid      := path_result.gid;
974              geom.the_geom := path_result.the_geom;
975                 
976              RETURN NEXT geom;
977
978        END LOOP;
979        RETURN;
980END;
981$$
982LANGUAGE 'plpgsql' VOLATILE STRICT;
983
984CREATE OR REPLACE FUNCTION shortest_path_astar_as_geometry_internal_id_directed(
985       geom_table varchar, source int4, target int4, dir boolean, rc boolean)
986       RETURNS SETOF GEOMS AS
987$$
988DECLARE
989        r record;
990        path_result record;
991        v_id integer;
992        e_id integer;
993        geom geoms;
994       
995        query text;
996
997BEGIN
998        query := 'SELECT gid,the_geom FROM ' ||
999           'shortest_path_astar(''SELECT gid as id, source::integer, ' ||
1000           'target::integer, length::double precision as cost, ' ||
1001           'x1::double precision, y1::double precision, ' ||
1002           'x2::double precision, y2::double precision ';
1003           
1004        IF rc THEN query := query || ' , reverse_cost ';
1005        END IF;
1006
1007        query := query || 'FROM ' || quote_ident(geom_table) || ' '', ' ||
1008           quote_literal(source) || ' , ' ||
1009           quote_literal(target) || ' , '''||dir||''', '''||rc||'''), ' ||
1010           quote_ident(geom_table) || ' where edge_id = gid ';
1011           
1012        FOR path_result IN EXECUTE query
1013        LOOP
1014
1015              geom.gid      := path_result.gid;
1016              geom.the_geom := path_result.the_geom;
1017                 
1018              RETURN NEXT geom;
1019
1020        END LOOP;
1021        RETURN;
1022END;
1023$$
1024LANGUAGE 'plpgsql' VOLATILE STRICT;
1025
1026-----------------------------------------------------------------------
1027--  Set of function for TSP solving.
1028-----------------------------------------------------------------------
1029CREATE OR REPLACE FUNCTION tsp_as_geometry_internal_id(geom_table varchar,
1030       ids varchar, source integer)
1031       RETURNS SETOF integer AS
1032$$
1033DECLARE
1034        r record;
1035        path_result record;
1036        v_id integer;
1037        prev integer;
1038
1039BEGIN
1040        prev := -1;
1041        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 ' ||
1042                quote_ident(geom_table) || ' where source in (' ||
1043                ids || ')'', '''|| ids  ||''', '|| source  ||')' LOOP
1044
1045                v_id = path_result.vertex_id;
1046        RETURN NEXT v_id;
1047        END LOOP;
1048
1049        RETURN;
1050END;
1051$$
1052LANGUAGE 'plpgsql' VOLATILE STRICT;
1053
1054
1055CREATE OR REPLACE FUNCTION tsp_astar_as_geometry_internal_id(
1056       geom_table varchar,ids varchar, source integer)
1057       RETURNS SETOF GEOMS AS
1058$$
1059DECLARE
1060        r record;
1061        path_result record;
1062        v_id integer;
1063        prev integer;
1064        geom geoms;
1065
1066BEGIN
1067        prev := source;
1068        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 ' ||
1069          quote_ident(geom_table) || ' where source in (' ||
1070          ids || ')'', '''|| ids  ||''', '|| source  ||')' LOOP
1071
1072                v_id = path_result.vertex_id;
1073               
1074                FOR r IN EXECUTE 'SELECT gid, the_geom FROM shortest_path_astar1_as_geometry_internal_id( ''' ||
1075                  quote_ident(geom_table)  ||''', '|| v_id ||', '||
1076                  prev ||',0.03)' LOOP
1077                    geom.gid := r.gid;
1078                    geom.the_geom := r.the_geom;
1079                    RETURN NEXT geom;
1080                END LOOP;
1081               
1082        prev = v_id;
1083        END LOOP;
1084        RETURN;
1085END;
1086$$
1087LANGUAGE 'plpgsql' VOLATILE STRICT;
1088
1089CREATE OR REPLACE FUNCTION tsp_astar_as_geometry_internal_id_directed(
1090       geom_table varchar,ids varchar, source integer, delta float8, dir boolean, rc boolean)
1091       RETURNS SETOF GEOMS AS
1092$$
1093DECLARE
1094        r record;
1095        path_result record;
1096        v_id integer;
1097        prev integer;
1098        geom geoms;
1099       
1100        query text;
1101
1102BEGIN
1103        prev := source;
1104        query := 'SELECT vertex_id FROM tsp(''select distinct source::integer '||
1105                'as source_id, x1::double precision as x, y1::double precision as y';
1106               
1107        IF rc THEN query := query || ' , reverse_cost ';
1108        END IF;
1109
1110        query := query || 'from ' || quote_ident(geom_table) || ' where source in (' ||
1111          ids || ')'', '''|| ids  ||''', '|| source  ||')';
1112       
1113        FOR path_result IN EXECUTE query
1114        LOOP
1115
1116                v_id = path_result.vertex_id;
1117               
1118                FOR r IN EXECUTE 'SELECT gid, the_geom FROM shortest_path_astar1_as_geometry_internal_id_directed( ''' ||
1119                  quote_ident(geom_table)  ||''', '|| v_id ||', '||
1120                  prev ||','||delta||', '''||dir||''', '''||rc||''')' LOOP
1121                    geom.gid := r.gid;
1122                    geom.the_geom := r.the_geom;
1123                    RETURN NEXT geom;
1124                END LOOP;
1125               
1126        prev = v_id;
1127        END LOOP;
1128        RETURN;
1129END;
1130$$
1131LANGUAGE 'plpgsql' VOLATILE STRICT;
1132
1133
1134CREATE OR REPLACE FUNCTION tsp_dijkstra_as_geometry_internal_id(
1135       geom_table varchar,ids varchar, source integer)
1136       RETURNS SETOF GEOMS AS
1137$$
1138DECLARE
1139        r record;
1140        path_result record;
1141        v_id integer;
1142        prev integer;
1143        geom geoms;
1144
1145BEGIN
1146        prev := source;
1147        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 ' ||
1148           quote_ident(geom_table) || ' where source in (' ||
1149           ids || ')'', '''|| ids  ||''', '|| source  ||')' LOOP
1150
1151                v_id = path_result.vertex_id;
1152               
1153
1154                FOR r IN EXECUTE 'SELECT gid, the_geom FROM shortest_path_dijkstra1_as_geometry_internal_id( ''' ||
1155                  quote_ident(geom_table)  ||''', '|| v_id ||', '||
1156                  prev ||',0.03)' LOOP
1157                    geom.gid := r.gid;
1158                    geom.the_geom := r.the_geom;
1159                    RETURN NEXT geom;
1160                END LOOP;
1161               
1162        prev = v_id;
1163        END LOOP;
1164        RETURN;
1165END;
1166$$
1167LANGUAGE 'plpgsql' VOLATILE STRICT;
1168
1169CREATE OR REPLACE FUNCTION tsp_dijkstra_as_geometry_internal_id_directed(
1170       geom_table varchar,ids varchar, source integer, delta float8, dir boolean, rc boolean)
1171       RETURNS SETOF GEOMS AS
1172$$
1173DECLARE
1174        r record;
1175        path_result record;
1176        v_id integer;
1177        prev integer;
1178        geom geoms;
1179       
1180        query text;
1181
1182BEGIN
1183        prev := source;
1184       
1185        query := 'SELECT vertex_id FROM tsp(''select distinct source::integer as source_id, '||
1186                    'x(startpoint(the_geom)), y(startpoint(the_geom))';
1187                   
1188        IF rc THEN query := query || ' , reverse_cost ';
1189        END IF;
1190
1191        query := 'from ' || quote_ident(geom_table) || ' where source in (' ||
1192           ids || ')'', '''|| ids  ||''', '|| source  ||')';
1193           
1194        FOR path_result IN EXECUTE query
1195        LOOP
1196
1197                v_id = path_result.vertex_id;
1198               
1199
1200                FOR r IN EXECUTE 'SELECT gid, the_geom FROM shortest_path_dijkstra1_as_geometry_internal_id_directed( ''' ||
1201                  quote_ident(geom_table)  ||''', '|| v_id ||', '||
1202                  prev ||','||delta||', '''||dir||''', '''||rc||''')' LOOP
1203                    geom.gid := r.gid;
1204                    geom.the_geom := r.the_geom;
1205                    RETURN NEXT geom;
1206                END LOOP;
1207               
1208        prev = v_id;
1209        END LOOP;
1210        RETURN;
1211END;
1212$$
1213LANGUAGE 'plpgsql' VOLATILE STRICT;
1214
1215
1216CREATE OR REPLACE FUNCTION points_as_polygon(query varchar)
1217       RETURNS SETOF GEOMS AS
1218$$
1219DECLARE
1220     r record;
1221     path_result record;                                             
1222     i int;                                                         
1223     q text;
1224     x float8[];
1225     y float8[];
1226     geom geoms;
1227BEGIN
1228                                                                             
1229     i := 1;                                                                 
1230     q := 'select 1 as gid, GeometryFromText(''POLYGON((';
1231     
1232     FOR path_result IN EXECUTE 'select x, y from alphashape('''||
1233         query || ''')' LOOP
1234         x[i] = path_result.x;
1235         y[i] = path_result.y;
1236         i := i+1;
1237     END LOOP;
1238
1239     q := q || x[1] || ' ' || y[1];
1240     i := 2;
1241
1242     WHILE x[i] IS NOT NULL LOOP
1243         q := q || ', ' || x[i] || ' ' || y[i];
1244         i := i + 1;
1245     END LOOP;
1246
1247    q := q || ', ' || x[1] || ' ' || y[1];
1248    q := q || '))'',-1) as the_geom';
1249
1250    FOR r in EXECUTE q LOOP
1251         geom.gid=r.gid;
1252         geom.the_geom=r.the_geom;
1253         RETURN NEXT geom;
1254    END LOOP;
1255
1256    RETURN;
1257END;
1258$$
1259
1260LANGUAGE 'plpgsql' VOLATILE STRICT;
1261
1262CREATE OR REPLACE FUNCTION shooting_star_route(
1263       varchar,int4, int4, float8, varchar, boolean, boolean)
1264       RETURNS SETOF GEOMS AS
1265$$
1266DECLARE
1267        geom_table ALIAS FOR $1;
1268        sourceid ALIAS FOR $2;
1269        targetid ALIAS FOR $3;
1270        delta ALIAS FOR $4;
1271        cost_column ALIAS FOR $5;
1272        dir ALIAS FOR $6;
1273        rc ALIAS FOR $7;
1274
1275        rec record;
1276        r record;
1277        path_result record;
1278        v_id integer;
1279        e_id integer;
1280        geom geoms;
1281       
1282        srid integer;
1283
1284        source_x float8;
1285        source_y float8;
1286        target_x float8;
1287        target_y float8;
1288       
1289        ll_x float8;
1290        ll_y float8;
1291        ur_x float8;
1292        ur_y float8;
1293       
1294        query text;
1295
1296BEGIN
1297        FOR rec IN EXECUTE
1298            'select srid(the_geom) from ' ||
1299            quote_ident(geom_table) || ' limit 1'
1300        LOOP
1301        END LOOP;
1302        srid := rec.srid;
1303       
1304        FOR rec IN EXECUTE
1305            'select x(startpoint(the_geom)) as source_x from ' ||
1306            quote_ident(geom_table) || ' where gid = '||sourceid
1307        LOOP
1308        END LOOP;
1309        source_x := rec.source_x;
1310       
1311        FOR rec IN EXECUTE
1312            'select y(startpoint(the_geom)) as source_y from ' ||
1313            quote_ident(geom_table) || ' where gid = ' ||sourceid
1314        LOOP
1315        END LOOP;
1316
1317        source_y := rec.source_y;
1318
1319        FOR rec IN EXECUTE
1320            'select x(startpoint(the_geom)) as target_x from ' ||
1321            quote_ident(geom_table) || ' where gid = ' ||targetid
1322        LOOP
1323        END LOOP;
1324
1325        target_x := rec.target_x;
1326       
1327        FOR rec IN EXECUTE
1328            'select y(startpoint(the_geom)) as target_y from ' ||
1329            quote_ident(geom_table) || ' where gid = ' ||targetid
1330        LOOP
1331        END LOOP;
1332        target_y := rec.target_y;
1333
1334        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
1335           ' THEN '||source_x||' ELSE '||target_x||
1336           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
1337           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
1338        LOOP
1339        END LOOP;
1340
1341        ll_x := rec.ll_x;
1342        ur_x := rec.ur_x;
1343
1344        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
1345            target_y||' THEN '||source_y||' ELSE '||
1346            target_y||' END as ll_y, CASE WHEN '||
1347            source_y||'>'||target_y||' THEN '||
1348            source_y||' ELSE '||target_y||' END as ur_y'
1349        LOOP
1350        END LOOP;
1351
1352        ll_y := rec.ll_y;
1353        ur_y := rec.ur_y;
1354
1355        query := 'SELECT gid,the_geom FROM ' ||
1356          'shortest_path_shooting_star(''SELECT gid as id, source::integer, ' ||
1357          'target::integer, '||cost_column||'::double precision as cost, ' ||
1358          'x1::double precision, y1::double precision, x2::double ' ||
1359          'precision, y2::double precision, rule::varchar, ' ||
1360          'to_cost::double precision ';
1361         
1362        IF rc THEN query := query || ' , reverse_cost '; 
1363        END IF;
1364         
1365        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
1366          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
1367          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom'', ' ||
1368          quote_literal(sourceid) || ' , ' ||
1369          quote_literal(targetid) || ' , '''||dir||''', '''||rc||''' ),' ||
1370          quote_ident(geom_table) || ' where edge_id = gid ';
1371         
1372        FOR path_result IN EXECUTE query
1373        LOOP
1374                 geom.gid      := path_result.gid;
1375                 geom.the_geom := path_result.the_geom;
1376                 
1377                 RETURN NEXT geom;
1378
1379        END LOOP;
1380        RETURN;
1381END;
1382$$
1383LANGUAGE 'plpgsql' VOLATILE STRICT;
1384
1385-- COMMIT;
Note: See TracBrowser for help on using the browser.