| 283 | EXECUTE 'select update_cost_from_distance('||quote_literal(geom_table) ||',''the_geom'',''length'')'; |
| 284 | RETURN; |
| 285 | END; |
| 286 | $$ |
| 287 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
| 288 | CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar,geocol varchar) |
| 289 | RETURNS VOID AS |
| 290 | $$ |
| 291 | DECLARE |
| 292 | BEGIN |
| 293 | EXECUTE 'select update_cost_from_distance('||quote_literal(geom_table) ||','||quote_literal(geocol)||',''length'')'; |
| 294 | RETURN; |
| 295 | END; |
| 296 | $$ |
| 297 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
| 298 | |
| 299 | CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar,geocol varchar,geodistfn varchar) |
| 300 | RETURNS VOID AS |
| 301 | $$ |
| 302 | DECLARE |
| 303 | BEGIN |
| 304 | EXECUTE 'SELECT update_cost_from_distance('||quote_literal(geom_table)||','||quote_literal(geocol)||','||quote_literal(geodistfn)||',''cost'')'; |
| 305 | $$ |
| 306 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
| 307 | |
| 308 | |
| 309 | CREATE OR REPLACE FUNCTION update_cost_from_distance(geom_table varchar,geocol varchar,geodistfn varchar,costcol varchar) |
| 310 | RETURNS VOID AS |
| 311 | $$ |
| 312 | DECLARE |
| 313 | BEGIN |
| 337 | CREATE OR REPLACE FUNCTION time_wgs84(geom geometry,tname varchar,geocol varchar) |
| 338 | RETURNS DOUBLE PRECISION AS |
| 339 | $$ |
| 340 | DECLARE |
| 341 | len DOUBLE PRECISION; |
| 342 | speed DOUBLE PRECISION; |
| 343 | astxt varchar; |
| 344 | BEGIN |
| 345 | SELECT length_wgs84(geom) into len; |
| 346 | SELECT astext(geom) into astxt; -- someone might know a better way to do this ??? |
| 347 | EXECUTE 'SELECT speed FROM '||quote_ident(tname)||' where astext('||quote_ident(geocol)||') = '||quote_literal(astxt) INTO speed ; |
| 348 | return (len/1000.0)/speed; |
| 349 | END |
| 350 | $$ |
| 351 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
| 352 | |
| 353 | -- |
| 354 | -- A demo only function, neet to specify table name & geometry column name for particlar app. |
| 355 | -- |
| 356 | CREATE OR REPLACE FUNCTION time_wgs84(geom geometry) |
| 357 | RETURNS DOUBLE PRECISION AS |
| 358 | $$ |
| 359 | DECLARE |
| 360 | ans DOUBLE PRECISION; |
| 361 | BEGIN |
| 362 | SELECT time_wgs84(geom,'splitroadnet','wkb_geometry') into ans; |
| 363 | return ans; |
| 364 | END |
| 365 | $$ |
| 366 | LANGUAGE 'plpgsql' VOLATILE STRICT; |
| 367 | |
| 368 | |
| 369 | CREATE OR REPLACE FUNCTION length_wgs84(geom geometry) |
| 370 | RETURNS DOUBLE PRECISION AS |
| 371 | $$ |
| 372 | DECLARE |
| 373 | ans DOUBLE PRECISION; |
| 374 | BEGIN |
| 375 | SELECT st_length_spheroid(geom,'SPHEROID["WGS_1984",6378173,298.257223563]') INTO ans; |
| 376 | return ans ; |
| 377 | END |
| 378 | $$ |
| 379 | LANGUAGE 'plpgsql' VOLATILE STRICT; |