routing_postgis.sql

Revision 39, 39.1 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_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	-----------------------------------------------------------------------
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_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;
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_id and target_id 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	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
164	RETURN 'OK';
165
166	END;
167	$$
168	LANGUAGE '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 ?
175	CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar)
176	RETURNS VOID AS
177	$$
178	DECLARE
179	BEGIN
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;
195	END;
196	$$
197	LANGUAGE 'plpgsql' VOLATILE STRICT;
198
199
200	CREATE 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	-----------------------------------------------------------------------
211	CREATE OR REPLACE FUNCTION shortest_path_as_geometry_internal_id(
212	geom_table varchar, source int4, target int4)
213	RETURNS SETOF GEOMS AS
214	$$
215	DECLARE
216	r record;
217	path_result record;
218	v_id integer;
219	e_id integer;
220	geom geoms;
221	BEGIN
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;
238	END;
239	$$
240	LANGUAGE 'plpgsql' VOLATILE STRICT;
241
242	CREATE 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	$$
246	DECLARE
247	r record;
248	path_result record;
249	v_id integer;
250	e_id integer;
251	geom geoms;
252	query text;
253	BEGIN
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;
276	END;
277	$$
278	LANGUAGE '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	-----------------------------------------------------------------------
284	CREATE OR REPLACE FUNCTION shortest_path_as_geometry(geom_table varchar,
285	geom_source anyelement,geom_target anyelement)
286	RETURNS SETOF GEOMS AS
287	$$
288	DECLARE
289	r record;
290	source int4;
291	target int4;
292	path_result record;
293	v_id integer;
294	e_id integer;
295	geom geoms;
296	BEGIN
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;
324	END;
325	$$
326	LANGUAGE '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	-----------------------------------------------------------------------
335	CREATE OR REPLACE FUNCTION shortest_path_astar1_as_geometry_internal_id(
336	varchar,int4, int4, float8)
337	RETURNS SETOF GEOMS AS
338	$$
339	DECLARE
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
352	BEGIN
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;
377	END;
378	$$
379	LANGUAGE 'plpgsql' VOLATILE STRICT;
380
381	CREATE OR REPLACE FUNCTION shortest_path_astar1_as_geometry_internal_id_directed(
382	varchar,int4, int4, float8, boolean, boolean)
383	RETURNS SETOF GEOMS AS
384	$$
385	DECLARE
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
414	BEGIN
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;
513	END;
514	$$
515	LANGUAGE 'plpgsql' VOLATILE STRICT;
516
517
518	CREATE OR REPLACE FUNCTION shortest_path_astar3_as_geometry_internal_id(
519	varchar,int4, int4, float8, varchar)
520	RETURNS SETOF GEOMS AS
521	$$
522	DECLARE
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
536	BEGIN
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;
551	END;
552	$$
553	LANGUAGE 'plpgsql' VOLATILE STRICT;
554
555	CREATE 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	$$
559	DECLARE
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
589	BEGIN
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;
679	END;
680	$$
681	LANGUAGE 'plpgsql' VOLATILE STRICT;
682
683
684	CREATE OR REPLACE FUNCTION shortest_path_dijkstra1_as_geometry_internal_id(
685	varchar,int4, int4, float8)
686	RETURNS SETOF GEOMS AS
687	$$
688	DECLARE
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
701	BEGIN
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;
714	END;
715	$$
716	LANGUAGE 'plpgsql' VOLATILE STRICT;
717
718	CREATE OR REPLACE FUNCTION shortest_path_dijkstra1_as_geometry_internal_id_directed(
719	varchar,int4, int4, float8, boolean, boolean)
720	RETURNS SETOF GEOMS AS
721	$$
722	DECLARE
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;
750	BEGIN
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;
837	END;
838	$$
839	LANGUAGE 'plpgsql' VOLATILE STRICT;
840
841
842	CREATE OR REPLACE FUNCTION shortest_path_astar2_as_geometry_internal_id(
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
865	BEGIN
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;
880	END;
881	$$
882	LANGUAGE 'plpgsql' VOLATILE STRICT;
883
884	CREATE 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	$$
888	DECLARE
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
910	BEGIN
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;
943	END;
944	$$
945	LANGUAGE 'plpgsql' VOLATILE STRICT;
946
947
948	CREATE OR REPLACE FUNCTION shortest_path_astar_as_geometry_internal_id(
949	geom_table varchar, source int4, target int4)
950	RETURNS SETOF GEOMS AS
951	$$
952	DECLARE
953	r record;
954	path_result record;
955	v_id integer;
956	e_id integer;
957	geom geoms;
958
959	BEGIN
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;
973	END;
974	$$
975	LANGUAGE 'plpgsql' VOLATILE STRICT;
976
977	CREATE 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	$$
981	DECLARE
982	r record;
983	path_result record;
984	v_id integer;
985	e_id integer;
986	geom geoms;
987
988	query text;
989
990	BEGIN
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;
1015	END;
1016	$$
1017	LANGUAGE 'plpgsql' VOLATILE STRICT;
1018
1019	-----------------------------------------------------------------------
1020	-- Set of function for TSP solving.
1021	-----------------------------------------------------------------------
1022	CREATE OR REPLACE FUNCTION tsp_as_geometry_internal_id(geom_table varchar,
1023	ids varchar, source integer)
1024	RETURNS SETOF integer AS
1025	$$
1026	DECLARE
1027	r record;
1028	path_result record;
1029	v_id integer;
1030	prev integer;
1031
1032	BEGIN
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;
1043	END;
1044	$$
1045	LANGUAGE 'plpgsql' VOLATILE STRICT;
1046
1047
1048	CREATE OR REPLACE FUNCTION tsp_astar_as_geometry_internal_id(
1049	geom_table varchar,ids varchar, source integer)
1050	RETURNS SETOF GEOMS AS
1051	$$
1052	DECLARE
1053	r record;
1054	path_result record;
1055	v_id integer;
1056	prev integer;
1057	geom geoms;
1058
1059	BEGIN
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;
1078	END;
1079	$$
1080	LANGUAGE 'plpgsql' VOLATILE STRICT;
1081
1082	CREATE 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	$$
1086	DECLARE
1087	r record;
1088	path_result record;
1089	v_id integer;
1090	prev integer;
1091	geom geoms;
1092
1093	query text;
1094
1095	BEGIN
1096	prev := source;
1097	query := 'SELECT vertex_id FROM tsp(''select distinct source::integer '\|\|
1098	'as source_id, x1: