routing_wrappers.sql

Revision 39, 38.5 kB (checked in by anton, 1 year ago)
1.0.0b tag added

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