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

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