Ticket #87: routing_core_wrappers_141207.patch.forthem

File routing_core_wrappers_141207.patch.forthem, 5.9 KB (added by rodj59, 3 years ago)

This applies to original file in this ticket.

Line 
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 (