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 | -- BEGIN; |
---|
21 | |
---|
22 | CREATE OR REPLACE FUNCTION tsp_ids(geom_table varchar, |
---|
23 | ids varchar, source integer) |
---|
24 | RETURNS SETOF integer AS |
---|
25 | $$ |
---|
26 | DECLARE |
---|
27 | r record; |
---|
28 | path_result record; |
---|
29 | v_id integer; |
---|
30 | prev integer; |
---|
31 | |
---|
32 | BEGIN |
---|
33 | prev := -1; |
---|
34 | 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 ' || |
---|
35 | quote_ident(geom_table) || ' where source in (' || |
---|
36 | ids || ')'', '''|| ids ||''', '|| source ||')' LOOP |
---|
37 | |
---|
38 | v_id = path_result.vertex_id; |
---|
39 | RETURN NEXT v_id; |
---|
40 | END LOOP; |
---|
41 | |
---|
42 | RETURN; |
---|
43 | END; |
---|
44 | $$ |
---|
45 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
46 | |
---|
47 | |
---|
48 | CREATE OR REPLACE FUNCTION tsp_astar( |
---|
49 | geom_table varchar,ids varchar, source integer) |
---|
50 | RETURNS SETOF GEOMS AS |
---|
51 | $$ |
---|
52 | DECLARE |
---|
53 | r record; |
---|
54 | path_result record; |
---|
55 | v_id integer; |
---|
56 | prev integer; |
---|
57 | geom geoms; |
---|
58 | |
---|
59 | id integer; |
---|
60 | BEGIN |
---|
61 | |
---|
62 | id :=0; |
---|
63 | prev := source; |
---|
64 | 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 ' || |
---|
65 | quote_ident(geom_table) || ' where source in (' || |
---|
66 | ids || ')'', '''|| ids ||''', '|| source ||')' LOOP |
---|
67 | |
---|
68 | v_id = path_result.vertex_id; |
---|
69 | |
---|
70 | FOR r IN EXECUTE 'SELECT gid, the_geom FROM astar_sp_delta( ''' || |
---|
71 | quote_ident(geom_table) ||''', '|| v_id ||', '|| |
---|
72 | prev ||',0.03)' LOOP |
---|
73 | geom.gid := r.gid; |
---|
74 | geom.the_geom := r.the_geom; |
---|
75 | id := id+1; |
---|
76 | geom.id := id; |
---|
77 | RETURN NEXT geom; |
---|
78 | END LOOP; |
---|
79 | |
---|
80 | prev = v_id; |
---|
81 | END LOOP; |
---|
82 | RETURN; |
---|
83 | END; |
---|
84 | $$ |
---|
85 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
86 | |
---|
87 | CREATE OR REPLACE FUNCTION tsp_astar_directed( |
---|
88 | geom_table varchar,ids varchar, source integer, delta float8, dir boolean, rc boolean) |
---|
89 | RETURNS SETOF GEOMS AS |
---|
90 | $$ |
---|
91 | DECLARE |
---|
92 | r record; |
---|
93 | path_result record; |
---|
94 | v_id integer; |
---|
95 | prev integer; |
---|
96 | geom geoms; |
---|
97 | |
---|
98 | query text; |
---|
99 | |
---|
100 | id integer; |
---|
101 | BEGIN |
---|
102 | |
---|
103 | id :=0; |
---|
104 | prev := source; |
---|
105 | query := 'SELECT vertex_id FROM tsp(''select distinct source::integer '|| |
---|
106 | 'as source_id, x1::double precision as x, y1::double precision as y'; |
---|
107 | |
---|
108 | IF rc THEN query := query || ' , reverse_cost '; |
---|
109 | END IF; |
---|
110 | |
---|
111 | query := query || 'from ' || quote_ident(geom_table) || ' where source in (' || |
---|
112 | ids || ')'', '''|| ids ||''', '|| source ||')'; |
---|
113 | |
---|
114 | FOR path_result IN EXECUTE query |
---|
115 | LOOP |
---|
116 | |
---|
117 | v_id = path_result.vertex_id; |
---|
118 | |
---|
119 | FOR r IN EXECUTE 'SELECT gid, the_geom FROM astar_sp_delta_directed( ''' || |
---|
120 | quote_ident(geom_table) ||''', '|| v_id ||', '|| |
---|
121 | prev ||','||delta||', '''||dir||''', '''||rc||''')' LOOP |
---|
122 | geom.gid := r.gid; |
---|
123 | geom.the_geom := r.the_geom; |
---|
124 | id := id+1; |
---|
125 | geom.id := id; |
---|
126 | RETURN NEXT geom; |
---|
127 | END LOOP; |
---|
128 | |
---|
129 | prev = v_id; |
---|
130 | END LOOP; |
---|
131 | RETURN; |
---|
132 | END; |
---|
133 | $$ |
---|
134 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
135 | |
---|
136 | |
---|
137 | CREATE OR REPLACE FUNCTION tsp_dijkstra( |
---|
138 | geom_table varchar,ids varchar, source integer) |
---|
139 | RETURNS SETOF GEOMS AS |
---|
140 | $$ |
---|
141 | DECLARE |
---|
142 | r record; |
---|
143 | path_result record; |
---|
144 | v_id integer; |
---|
145 | prev integer; |
---|
146 | geom geoms; |
---|
147 | |
---|
148 | id integer; |
---|
149 | BEGIN |
---|
150 | |
---|
151 | id :=0; |
---|
152 | prev := source; |
---|
153 | 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 ' || |
---|
154 | quote_ident(geom_table) || ' where source in (' || |
---|
155 | ids || ')'', '''|| ids ||''', '|| source ||')' LOOP |
---|
156 | |
---|
157 | v_id = path_result.vertex_id; |
---|
158 | |
---|
159 | |
---|
160 | FOR r IN EXECUTE 'SELECT gid, the_geom FROM dijkstra_sp_delta( ''' || |
---|
161 | quote_ident(geom_table) ||''', '|| v_id ||', '|| |
---|
162 | prev ||',0.03)' LOOP |
---|
163 | geom.gid := r.gid; |
---|
164 | geom.the_geom := r.the_geom; |
---|
165 | id := id+1; |
---|
166 | geom.id := id; |
---|
167 | RETURN NEXT geom; |
---|
168 | END LOOP; |
---|
169 | |
---|
170 | prev = v_id; |
---|
171 | END LOOP; |
---|
172 | RETURN; |
---|
173 | END; |
---|
174 | $$ |
---|
175 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
176 | |
---|
177 | CREATE OR REPLACE FUNCTION tsp_dijkstra_directed( |
---|
178 | geom_table varchar,ids varchar, source integer, delta float8, dir boolean, rc boolean) |
---|
179 | RETURNS SETOF GEOMS AS |
---|
180 | $$ |
---|
181 | DECLARE |
---|
182 | r record; |
---|
183 | path_result record; |
---|
184 | v_id integer; |
---|
185 | prev integer; |
---|
186 | geom geoms; |
---|
187 | |
---|
188 | query text; |
---|
189 | |
---|
190 | id integer; |
---|
191 | BEGIN |
---|
192 | |
---|
193 | id :=0; |
---|
194 | prev := source; |
---|
195 | |
---|
196 | query := 'SELECT vertex_id FROM tsp(''select distinct source::integer as source_id, '|| |
---|
197 | 'x(startpoint(the_geom)), y(startpoint(the_geom))'; |
---|
198 | |
---|
199 | IF rc THEN query := query || ' , reverse_cost '; |
---|
200 | END IF; |
---|
201 | |
---|
202 | query := 'from ' || quote_ident(geom_table) || ' where source in (' || |
---|
203 | ids || ')'', '''|| ids ||''', '|| source ||')'; |
---|
204 | |
---|
205 | FOR path_result IN EXECUTE query |
---|
206 | LOOP |
---|
207 | |
---|
208 | v_id = path_result.vertex_id; |
---|
209 | |
---|
210 | |
---|
211 | FOR r IN EXECUTE 'SELECT gid, the_geom FROM dijkstra_sp_delta_directed( ''' || |
---|
212 | quote_ident(geom_table) ||''', '|| v_id ||', '|| |
---|
213 | prev ||','||delta||', '''||dir||''', '''||rc||''')' LOOP |
---|
214 | geom.gid := r.gid; |
---|
215 | geom.the_geom := r.the_geom; |
---|
216 | id := id+1; |
---|
217 | geom.id := id; |
---|
218 | RETURN NEXT geom; |
---|
219 | END LOOP; |
---|
220 | |
---|
221 | prev = v_id; |
---|
222 | END LOOP; |
---|
223 | RETURN; |
---|
224 | END; |
---|
225 | $$ |
---|
226 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
227 | |
---|
228 | -- COMMIT; |
---|