Ticket #133: dijkstra.sql

File dijkstra.sql, 2.1 KB (added by daniel, 2 years ago)

Dijkstra modified

Line 
1-- Function: dijkstra_sp(character varying, integer, integer)
2
3CREATE OR REPLACE FUNCTION dijkstra_sp(geom_table character varying, source integer, target integer)
4  RETURNS SETOF geoms AS
5$BODY$
6DECLARE
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;       
18BEGIN
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;
64END;
65$BODY$
66  LANGUAGE 'plpgsql' VOLATILE STRICT
67  COST 100
68  ROWS 1000;
69