Ticket #87: routing_core_wrappers.sql

File routing_core_wrappers.sql, 34.2 kB (added by rodj59, 1 year ago)

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