1 | -- Function: dijkstra_sp(character varying, integer, integer) |
---|
2 | |
---|
3 | CREATE OR REPLACE FUNCTION dijkstra_sp(geom_table character varying, source integer, target integer) |
---|
4 | RETURNS SETOF geoms AS |
---|
5 | $BODY$ |
---|
6 | DECLARE |
---|
7 | r record; |
---|
8 | g_rec record; |
---|
9 | p_rec record; |
---|
10 | path_result record; |
---|
11 | v_id integer; |
---|
12 | e_id integer; |
---|
13 | geom geoms; |
---|
14 | id integer; |
---|
15 | g_schema text; |
---|
16 | g_table text; |
---|
17 | pos int; |
---|
18 | BEGIN |
---|
19 | |
---|
20 | pos := strpos(geom_table,'.'); |
---|
21 | |
---|
22 | if pos=0 then |
---|
23 | g_schema := 'public'; |
---|
24 | g_table := geom_table; |
---|
25 | else |
---|
26 | g_schema = substr(geom_table,0,pos); |
---|
27 | pos := pos + 1; |
---|
28 | g_table = substr(geom_table,pos); |
---|
29 | END IF; |
---|
30 | |
---|
31 | select into g_rec f_geometry_column, type as geom_type |
---|
32 | from public.geometry_columns |
---|
33 | where f_table_schema = g_schema |
---|
34 | and f_table_name = g_table; |
---|
35 | |
---|
36 | select into p_rec col.column_name as pkey |
---|
37 | from information_schema.table_constraints as key, information_schema.key_column_usage as col |
---|
38 | where key.table_schema = g_schema::name |
---|
39 | and key.table_name = g_table::name |
---|
40 | and key.constraint_type='PRIMARY KEY' |
---|
41 | and key.table_catalog = col.table_catalog |
---|
42 | and key.table_schema = col.table_schema |
---|
43 | and key.table_name = col.table_name; |
---|
44 | |
---|
45 | id :=0; |
---|
46 | |
---|
47 | FOR path_result IN EXECUTE 'SELECT '||p_rec.pkey||' as gid,'||g_rec.f_geometry_column||' as the_geom FROM ' || |
---|
48 | 'shortest_path(''SELECT '||p_rec.pkey||' as id, source::integer, target::integer, ' || |
---|
49 | 'length::double precision as cost FROM ' || |
---|
50 | quote_ident(g_schema)||'.'||quote_ident(g_table) || ''', ' || quote_literal(source) || |
---|
51 | ' , ' || quote_literal(target) || ' , false, false), ' || |
---|
52 | quote_ident(g_schema)||'.'||quote_ident(g_table) || ' where edge_id = '||p_rec.pkey |
---|
53 | LOOP |
---|
54 | |
---|
55 | geom.gid := path_result.gid; |
---|
56 | geom.the_geom := path_result.the_geom; |
---|
57 | id := id+1; |
---|
58 | geom.id := id; |
---|
59 | |
---|
60 | RETURN NEXT geom; |
---|
61 | |
---|
62 | END LOOP; |
---|
63 | RETURN; |
---|
64 | END; |
---|
65 | $BODY$ |
---|
66 | LANGUAGE 'plpgsql' VOLATILE STRICT |
---|
67 | COST 100 |
---|
68 | ROWS 1000; |
---|
69 | |
---|