routing_postgis.sql

Revision 25, 39.3 kB (checked in by anton, 1 year ago)
light and extra branches

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