1 | --- /home/ftp/transport/Desktop/routing_core_wrappers_originalsubmit.sql 2007-12-14 20:05:59.000000000 +1000 |
---|
2 | +++ /usr/share/postgresql/routing_core_wrappers.sql 2007-12-13 02:33:38.000000000 +1000 |
---|
3 | @@ -97,7 +97,7 @@ |
---|
4 | -- For each vertex in the vertices table, set a point geometry which is |
---|
5 | -- the corresponding line start or line end point |
---|
6 | ----------------------------------------------------------------------- |
---|
7 | -CREATE OR REPLACE FUNCTION add_vertices_geometry(geom_table varchar,SRID int) |
---|
8 | +CREATE OR REPLACE FUNCTION add_vertices_geometry(geom_table varchar,SRID int,geocol varchar) |
---|
9 | RETURNS VOID AS |
---|
10 | $$ |
---|
11 | DECLARE |
---|
12 | @@ -107,25 +107,39 @@ |
---|
13 | BEGIN |
---|
14 | EXECUTE 'SELECT addGeometryColumn(''' || |
---|
15 | quote_ident(vertices_table) || |
---|
16 | - ''', ''the_geom'',' || quote_literal(SRID) ||', ''POINT'', 2)'; |
---|
17 | + ''', '||quote_literal(geocol)||',' || quote_literal(SRID) ||', ''POINT'', 2)'; |
---|
18 | EXCEPTION |
---|
19 | WHEN DUPLICATE_COLUMN THEN |
---|
20 | END; |
---|
21 | |
---|
22 | EXECUTE 'UPDATE ' || quote_ident(vertices_table) || |
---|
23 | - ' SET the_geom = NULL'; |
---|
24 | + ' SET '||quote_ident(geocol)||' = NULL'; |
---|
25 | |
---|
26 | EXECUTE 'UPDATE ' || quote_ident(vertices_table) || |
---|
27 | - ' SET the_geom = startPoint(relaxed_geometryn(m.the_geom, 1)) FROM ' || |
---|
28 | + ' SET '||quote_ident(geocol)||' = startPoint(relaxed_geometryn(m.'||quote_ident(geocol)||', 1)) FROM ' || |
---|
29 | quote_ident(geom_table) || |
---|
30 | ' m where geom_id = m.source_id'; |
---|
31 | |
---|
32 | EXECUTE 'UPDATE ' || quote_ident(vertices_table) || |
---|
33 | - ' set the_geom = st_PointN(relaxed_geometryn(m.the_geom, 1),st_NumPoints(relaxed_geometryN(m.the_geom,1))) FROM ' || |
---|
34 | + ' set '||quote_ident(geocol)||' = st_PointN(relaxed_geometryn(m.'||quote_ident(geocol)||', 1),st_NumPoints(relaxed_geometryN(m.'||quote_ident(geocol)||',1))) FROM ' || |
---|
35 | quote_ident(geom_table) || |
---|
36 | ' m where geom_id = m.target_id AND ' || |
---|
37 | quote_ident(vertices_table) || |
---|
38 | - '.the_geom IS NULL'; |
---|
39 | + '.'||quote_ident(geocol)||' IS NULL'; |
---|
40 | + |
---|
41 | + RETURN; |
---|
42 | +END; |
---|
43 | +$$ |
---|
44 | +LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
45 | + |
---|
46 | +CREATE OR REPLACE FUNCTION add_vertices_geometry(geom_table varchar,SRID int) |
---|
47 | + RETURNS VOID AS |
---|
48 | +$$ |
---|
49 | +BEGIN |
---|
50 | + |
---|
51 | + BEGIN |
---|
52 | + EXECUTE 'SELECT add_vertices_geometry('||quote_literal(geom_table)||','||text(SRID)||',''the_geom'')'; |
---|
53 | + END; |
---|
54 | |
---|
55 | RETURN; |
---|
56 | END; |
---|
57 | @@ -138,7 +152,7 @@ |
---|
58 | BEGIN |
---|
59 | |
---|
60 | BEGIN |
---|
61 | - EXECUTE 'SELECT add_vertices_geometry('||quote_ident(geom_table)||',-1)'; |
---|
62 | + EXECUTE 'SELECT add_vertices_geometry('||quote_literal(geom_table)||',-1)'; |
---|
63 | END; |
---|
64 | |
---|
65 | RETURN; |
---|
66 | @@ -280,6 +294,41 @@ |
---|
67 | $$ |
---|
68 | DECLARE |
---|
69 | BEGIN |
---|
70 | + EXECUTE 'select update_cost_from_distance('||quote_literal(geom_table) ||',''the_geom'',''length'')'; |
---|
71 | + RETURN; |
---|
72 | +END; |
---|
73 | +$$ |
---|
74 | +LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
75 | +CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar,geocol varchar) |
---|
76 | + RETURNS VOID AS |
---|
77 | +$$ |
---|
78 | +DECLARE |
---|
79 | +BEGIN |
---|
80 | + EXECUTE 'select update_cost_from_distance('||quote_literal(geom_table) ||','||quote_literal(geocol)||',''length'')'; |
---|
81 | + RETURN; |
---|
82 | +END; |
---|
83 | +$$ |
---|
84 | +LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
85 | + |
---|
86 | +CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar,geocol varchar,geodistfn varchar) |
---|
87 | + RETURNS VOID AS |
---|
88 | +$$ |
---|
89 | +DECLARE |
---|
90 | +BEGIN |
---|
91 | + EXECUTE 'SELECT update_cost_from_distance('||quote_literal(geom_table)||','||quote_literal(geocol)||','||quote_literal(geodistfn)||',''cost'')'; |
---|
92 | + RETURN; |
---|
93 | +END |
---|
94 | +$$ |
---|
95 | +LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
96 | + |
---|
97 | +-- |
---|
98 | +-- geodistfn is the name of a function(geom) which returns the cost of the geometry geom (eg, time_wgs84(),length_wgs84()) |
---|
99 | +-- |
---|
100 | +CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar,geocol varchar,geodistfn varchar,costcol varchar) |
---|
101 | + RETURNS VOID AS |
---|
102 | +$$ |
---|
103 | +DECLARE |
---|
104 | +BEGIN |
---|
105 | BEGIN |
---|
106 | EXECUTE 'CREATE INDEX ' || quote_ident(geom_table) || |
---|
107 | '_edge_id_idx ON ' || quote_ident(geom_table) || |
---|
108 | @@ -289,16 +338,66 @@ |
---|
109 | RAISE NOTICE 'Not creating index, already there'; |
---|
110 | END; |
---|
111 | |
---|
112 | + BEGIN |
---|
113 | + EXECUTE 'ALTER TABLE '||quote_ident(geom_table)||'_edges add column '||quote_ident(costcol)||' double precision'; |
---|
114 | + EXCEPTION |
---|
115 | + WHEN DUPLICATE_COLUMN THEN |
---|
116 | + RAISE NOTICE 'Not creating cost column, already there'; |
---|
117 | + END; |
---|
118 | EXECUTE 'UPDATE ' || quote_ident(geom_table) || |
---|
119 | - '_edges SET cost = (SELECT sum( length( g.the_geom ) ) FROM ' || |
---|
120 | + '_edges SET '||quote_ident(costcol)||' = (SELECT sum( '||quote_ident(geodistfn)||'( g.'||quote_ident(geocol)||' ) ) FROM ' || |
---|
121 | quote_ident(geom_table) || |
---|
122 | ' g WHERE g.edge_id = id GROUP BY id)'; |
---|
123 | - |
---|
124 | - RETURN; |
---|
125 | END; |
---|
126 | $$ |
---|
127 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
128 | |
---|
129 | +-- |
---|
130 | +-- Assumes column speed exists. Calculates distance from geometry in geocol. |
---|
131 | +-- |
---|
132 | +CREATE OR REPLACE FUNCTION time_wgs84(geom geometry,tname varchar,geocol varchar) |
---|
133 | +RETURNS DOUBLE PRECISION AS |
---|
134 | +$$ |
---|
135 | +DECLARE |
---|
136 | + len DOUBLE PRECISION; |
---|
137 | + speed DOUBLE PRECISION; |
---|
138 | + astxt varchar; |
---|
139 | +BEGIN |
---|
140 | + SELECT length_wgs84(geom) into len; |
---|
141 | + SELECT astext(geom) into astxt; -- someone might know a better way to do this ??? |
---|
142 | + EXECUTE 'SELECT speed FROM '||quote_ident(tname)||' where astext('||quote_ident(geocol)||') = '||quote_literal(astxt) INTO speed ; |
---|
143 | + return (len/1000.0)/speed; |
---|
144 | +END |
---|
145 | +$$ |
---|
146 | +LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
147 | + |
---|
148 | +-- |
---|
149 | +-- A demo only function, neet to specify table name & geometry column name for particlar app. |
---|
150 | +-- |
---|
151 | +CREATE OR REPLACE FUNCTION time_wgs84(geom geometry) |
---|
152 | +RETURNS DOUBLE PRECISION AS |
---|
153 | +$$ |
---|
154 | +DECLARE |
---|
155 | + ans DOUBLE PRECISION; |
---|
156 | +BEGIN |
---|
157 | + SELECT time_wgs84(geom,'splitroadnet','the_geom') into ans; |
---|
158 | + return ans; |
---|
159 | +END |
---|
160 | +$$ |
---|
161 | +LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
162 | + |
---|
163 | + |
---|
164 | +CREATE OR REPLACE FUNCTION length_wgs84(geom geometry) |
---|
165 | +RETURNS DOUBLE PRECISION AS |
---|
166 | +$$ |
---|
167 | +DECLARE |
---|
168 | + ans DOUBLE PRECISION; |
---|
169 | +BEGIN |
---|
170 | + SELECT st_length_spheroid(geom,'SPHEROID["WGS_1984",6378173,298.257223563]') INTO ans; |
---|
171 | + return ans ; |
---|
172 | +END |
---|
173 | +$$ |
---|
174 | +LANGUAGE 'plpgsql' VOLATILE STRICT; |
---|
175 | |
---|
176 | CREATE TYPE geoms AS |
---|
177 | ( |
---|