root/sandbox/wrappers/routing_core_smart.sql

Revision 331, 37.4 KB (checked in by anton, 18 months ago)

Smart wrapper fixed
Line 
1CREATE TYPE link_point AS (id integer, name varchar);
2CREATE TYPE links AS (f geometry, l geometry);
3
4CREATE TYPE edge AS ( gid integer,
5                         target       integer,
6                         source       integer,
7                         x1           double precision,
8                         y1           double precision,
9                         x2           double precision,
10                         y2           double precision,
11                         length       double precision,
12                         reverse_cost double precision,
13--                       id           integer,
14--                       class_id     smallint,
15                         to_cost      double precision,
16                         rule         text,
17                         the_geom     geometry );
18
19CREATE TYPE edge_array AS ( gid integer[6],
20                         target       integer[6],
21                         source       integer[6],
22                         x1           double precision[6],
23                         y1           double precision[6],
24                         x2           double precision[6],
25                         y2           double precision[6],
26                         length       double precision[6],
27                         reverse_cost double precision[6],
28--                       id           integer[6],
29--                       class_id     smallint[6],
30                         to_cost      double precision[6],
31                         rule         text[6],
32                         the_geom     geometry[6] );
33
34
35CREATE OR REPLACE FUNCTION add_network_info(src varchar) RETURNS void AS
36$$
37DECLARE
38  m_gid integer;
39  m_v_id integer;
40  longest_l double precision;
41  l_num integer; 
42 
43  r record;
44  ex boolean;
45 
46BEGIN
47  FOR r IN EXECUTE 'SELECT  count(*) as l_num, max(length(the_geom)) as longest_l, max(gid) AS max_gid, '''||src||''' as tname, greatest(max(source), max(target)) AS max_vertex_id FROM '||src
48  LOOP
49  END LOOP;
50 
51  m_gid := r.max_gid;
52  m_v_id := r.max_vertex_id;
53  longest_l := r.longest_l;
54  l_num := r.l_num;
55 
56  select (select relname from pg_class where relname='network_info') is null INTO ex;
57 
58  IF ex THEN
59  CREATE TABLE network_info(tname text, max_gid integer, max_vertex_id integer, longest_link_length double precision, link_num integer);
60  END IF;
61 
62  EXECUTE 'DELETE FROM network_info where tname='''||src||'''';
63  INSERT INTO network_info VALUES(src, m_gid, m_v_id, longest_l, l_num);
64END;
65$$
66LANGUAGE 'plpgsql';
67
68-------------------------------------------------------------------
69-- This function finds nearest link to a given node
70-- point - text representation of point
71-- distance - function will search for a link within this distance
72-- tbl - table name
73-------------------------------------------------------------------
74CREATE OR REPLACE FUNCTION find_nearest_link_within_distance(point varchar,
75        distance double precision, tbl varchar)
76        RETURNS INT AS
77$$
78DECLARE
79    row record;
80    x float8;
81    y float8;
82   
83    srid integer;
84   
85BEGIN
86
87    FOR row IN EXECUTE 'select getsrid(the_geom) as srid from '||tbl||' where gid = (select min(gid) from '||tbl||')' LOOP
88    END LOOP;
89        srid:= row.srid;
90   
91    -- Getting x and y of the point
92   
93    FOR row in EXECUTE 'select x(GeometryFromText('''||point||''', '||srid||')) as x' LOOP
94    END LOOP;
95        x:=row.x;
96
97    FOR row in EXECUTE 'select y(GeometryFromText('''||point||''', '||srid||')) as y' LOOP
98    END LOOP;
99        y:=row.y;
100
101    -- Searching for a link within the distance
102
103    FOR row in EXECUTE 'select gid, distance(the_geom, GeometryFromText('''||point||''', '||srid||')) as dist from '||tbl||
104                            ' where setsrid(''BOX3D('||x-distance||' '||y-distance||', '||x+distance||' '||y+distance||')''::BOX3D, '||srid||')&&the_geom order by dist asc limit 1'
105    LOOP
106    END LOOP;
107
108    IF row.gid IS NULL THEN
109            --RAISE EXCEPTION 'Data cannot be matched';
110            RETURN NULL;
111    END IF;
112
113    RETURN row.gid;
114
115END;
116$$
117LANGUAGE 'plpgsql' VOLATILE STRICT;
118
119CREATE OR REPLACE FUNCTION find_nearest_link_within_distance_xy(x double precision,
120        y double precision, distance double precision, tbl varchar)
121        RETURNS INT AS
122$$
123DECLARE
124    row record;
125       
126    srid integer;
127   
128BEGIN
129
130    FOR row IN EXECUTE 'select getsrid(the_geom) as srid from '||tbl||' where gid = (select min(gid) from '||tbl||')' LOOP
131    END LOOP;
132        srid:= row.srid;
133   
134    -- Searching for a link within the distance
135
136    FOR row in EXECUTE 'select gid, distance(the_geom, GeometryFromText(''POINT('||x||' '||y||')'', '||srid||')) as dist from '||tbl||
137                            ' where setsrid(''BOX3D('||x-distance||' '||y-distance||', '||x+distance||' '||y+distance||')''::BOX3D, '||srid||')&&the_geom order by dist asc limit 1'
138    LOOP
139    END LOOP;
140
141    IF row.gid IS NULL THEN
142            --RAISE EXCEPTION 'Data cannot be matched';
143            RETURN NULL;
144    END IF;
145
146    RETURN row.gid;
147
148END;
149$$
150LANGUAGE 'plpgsql' VOLATILE STRICT;
151
152
153CREATE OR REPLACE FUNCTION locate_point_as_geometry(tbl varchar, edge integer, px double precision, py double precision, col boolean)
154        RETURNS LINKS AS
155$$
156DECLARE
157    row record;
158    num integer;
159    i integer;
160    geom geoms;
161   
162    l links;
163    pos double precision;
164   
165    srid integer;
166   
167BEGIN
168
169    FOR row IN EXECUTE 'select getsrid(the_geom) as srid from '||tbl||' where gid = (select min(gid) from '||tbl||')' LOOP
170    END LOOP;
171        srid:= row.srid;
172
173--    RAISE NOTICE 'select * from line_locate_point((select linemerge(the_geom) from % where gid=%), geometryfromtext(''POINT(% %)'', %)) as pos', tbl, edge, px, py, srid;
174       
175    FOR row in EXECUTE 'select distinct * from line_locate_point((select distinct linemerge(the_geom) from '||tbl||' where gid='||edge||'), geometryfromtext(''POINT('
176                            ||px||' '||py||')'', '||srid||')) as pos'
177    LOOP
178    END LOOP;
179       
180    pos:=row.pos;
181   
182    -- Creating new geometries
183       
184    FOR row in EXECUTE 'select distinct * from line_substring((select distinct linemerge(the_geom) from '||tbl||' where gid='||edge||'), 0, '||pos||') as link'
185    LOOP
186    END LOOP;
187   
188    l.f:=row.link;
189    IF geometrytype(l.f) = 'POINT' THEN
190      --RAISE NOTICE 'POINT >>> %', astext(l.f);
191      l.f := geometryfromtext('LINESTRING('||x(l.f)||' '||y(l.f)||','||x(l.f)||' '||y(l.f)||')');
192    END IF;
193   
194    IF col THEN l.f = collect(l.f);
195    END IF;
196       
197
198    FOR row in EXECUTE 'select distinct * from line_substring((select distinct linemerge(the_geom) from '||tbl||' where gid='||edge||'), '||pos||', 1) as link'
199    LOOP
200    END LOOP;
201       
202    l.l:=row.link;
203    IF geometrytype(l.l) = 'POINT' THEN
204      --RAISE NOTICE 'POINT >>> %', astext(l.l);
205      l.l := geometryfromtext('LINESTRING('||x(l.l) ||' '||y(l.l)||','||x(l.l)||' '||y(l.l)||')');
206    END IF;
207
208    IF col THEN l.l = collect(l.l);
209    END IF;
210   
211    RETURN l;
212
213END;
214$$
215
216LANGUAGE 'plpgsql' VOLATILE STRICT;
217
218
219CREATE OR REPLACE FUNCTION get_middle(tbl varchar, edge integer, px1 double precision, py1 double precision, px2 double precision, py2 double precision, col boolean)
220        RETURNS GEOMETRY AS
221$$
222DECLARE
223    row record;
224    num integer;
225    i integer;
226    geom geoms;
227   
228    l geometry;
229    pos1 double precision;
230    pos2 double precision;
231   
232    srid integer;
233    query text;
234   
235BEGIN
236
237    FOR row IN EXECUTE 'select getsrid(the_geom) as srid from '||tbl||' where gid = (select min(gid) from '||tbl||')' LOOP
238    END LOOP;
239        srid:= row.srid;
240
241--    RAISE NOTICE 'select * from line_locate_point((select linemerge(the_geom) from % where gid=%), geometryfromtext(''POINT(% %)'', %)) as pos', tbl, edge, px, py, srid;
242       
243    FOR row in EXECUTE 'select distinct * from line_locate_point((select distinct linemerge(the_geom) from '||tbl||' where gid='||edge||'), geometryfromtext(''POINT('
244                            ||px1||' '||py1||')'', '||srid||')) as pos'
245    LOOP
246    END LOOP;   
247    pos1:=row.pos;
248
249    FOR row in EXECUTE 'select distinct * from line_locate_point((select distinct linemerge(the_geom) from '||tbl||' where gid='||edge||'), geometryfromtext(''POINT('
250                            ||px2||' '||py2||')'', '||srid||')) as pos'
251    LOOP
252    END LOOP;   
253    pos2:=row.pos;
254   
255    query := 'select line_substring((select distinct linemerge(the_geom) from '||tbl||' where gid='||edge||'), ';
256   
257    IF pos1 < pos2 THEN query:= query||pos1||', '||pos2;
258    ELSE query:= query || pos2||', '||pos1;
259    END IF;
260   
261    query := query ||') as link';
262   
263    -- Creating new geomety
264       
265    FOR row in EXECUTE query
266    LOOP
267    END LOOP;
268   
269    l:= row.link;
270   
271    IF col THEN l = collect(l);
272    END IF;
273   
274    RETURN l;
275
276END;
277$$
278
279
280LANGUAGE 'plpgsql' VOLATILE STRICT;
281
282
283CREATE OR REPLACE FUNCTION connected_substring_as_geometry(tbl varchar, edge integer, next_edge integer, x double precision, y double precision)
284        RETURNS GEOMETRY AS
285$$
286DECLARE
287    row record;
288    num integer;
289    i integer;
290    geom geoms;
291   
292    l geometry;
293    l1 geometry;
294    l2 geometry;
295    pos double precision;
296   
297    cp geometry;
298   
299    pnt integer;
300   
301    srid integer;
302   
303    query text;
304   
305    cont boolean;
306   
307BEGIN
308
309    FOR row IN EXECUTE 'select getsrid(the_geom) as srid from '||tbl||' where gid = (select min(gid) from '||tbl||')' LOOP
310    END LOOP;
311        srid:= row.srid;
312
313--    FOR row in EXECUTE 'select * from intersection((select linemerge(the_geom) from '||tbl||' where gid='||edge||'), '||
314--                      '(select linemerge(the_geom) from '||tbl||' where gid='||next_edge||')) as cp'
315--    LOOP
316--    END LOOP;
317   
318    FOR row in EXECUTE 'select case when (select source from '||tbl||' where gid='||edge||')=(select source from '||tbl||
319                        ' where gid='||next_edge||') then (select startpoint(the_geom) from '||tbl||' where gid='||next_edge||') when (select source from '
320                        ||tbl||' where gid='||edge||')=(select target from '||tbl||' where gid='||next_edge||') then (select endpoint(the_geom) from '
321                        ||tbl||' where gid='||next_edge||')  when (select target from '||tbl||' where gid='||edge||')=(select source from '
322                        ||tbl||' where gid='||next_edge||') then (select startpoint(the_geom) from '||tbl||
323                        ' where gid='||next_edge||') when (select target from '||tbl||' where gid='||edge||')=(select target from '||tbl||
324                        ' where gid='||next_edge||') then (select endpoint(the_geom) from '||tbl||' where gid='||next_edge||') end as cp'
325
326    LOOP
327    END LOOP;
328
329    cp:=row.cp;
330
331    FOR row in EXECUTE 'select * from line_locate_point((select linemerge(the_geom) from '||tbl||' where gid='||edge||'), geometryfromtext(''POINT('
332                            ||x||' '||y||')'', '||srid||')) as pos'
333    LOOP
334    END LOOP;
335       
336    pos:=row.pos;
337   
338--    FOR row IN EXECUTE 'select case when astext(startpoint(the_geom)) = '''||astext(cp)||''' then 0 else 1 end as pnt from '||tbl||' where gid='||edge
339--    LOOP
340--    END LOOP;
341--   
342--    pnt=row.pnt;
343
344    -- Creating new geometries
345       
346    FOR row in EXECUTE 'select * from line_substring((select linemerge(the_geom) from '||tbl||' where gid='||edge||'), 0, '||pos||') as link'
347    LOOP
348    END LOOP;
349
350    l1:=row.link;
351
352    FOR row in EXECUTE 'select * from line_substring((select linemerge(the_geom) from '||tbl||' where gid='||edge||'), '||pos||', 1) as link'
353    LOOP
354    END LOOP;
355
356    l2:=row.link;
357
358
359    IF cp=startpoint(l1) OR cp=endpoint(l1) THEN
360        l:=l1;
361    ELSE
362        l:=l2;
363    END IF;
364   
365    RETURN l;
366
367END;
368$$
369
370LANGUAGE 'plpgsql' VOLATILE STRICT;
371
372
373CREATE OR REPLACE FUNCTION shootingstar_sp_smart(
374       geom_table varchar, source_x float8, source_y float8, target_x float8, target_y float8, delta float8, cost_column varchar, dir boolean, rc boolean)
375       RETURNS SETOF GEOMS AS
376$$
377DECLARE
378r record;
379g geoms;
380BEGIN
381  FOR r IN EXECUTE 'SELECT id, gid, the_geom from shootingstar_sp_smart('''||geom_table||''', '||source_x||', '||source_y||', '||target_x||
382                   ', '||target_y||', '||delta||', '''||cost_column||''', ''reverse_cost'', ''to_cost'', '||text(dir)||', '||text(rc)||')'
383  LOOP
384    g.id := r.id;
385    g.gid := r.gid;
386    g.the_geom := r.the_geom;
387    RETURN NEXT g;
388  END LOOP;
389 
390END;
391$$
392
393LANGUAGE 'plpgsql' VOLATILE STRICT;
394
395
396CREATE OR REPLACE FUNCTION shootingstar_sp_smart(
397       geom_table varchar, source_x float8, source_y float8, target_x float8, target_y float8, delta float8, cost_column varchar, reverse_cost_column varchar, dir boolean, rc boolean)
398       RETURNS SETOF GEOMS AS
399$$
400DECLARE
401r record;
402g geoms;
403BEGIN
404  FOR r IN EXECUTE 'SELECT id, gid, the_geom from shootingstar_sp_smart('''||geom_table||''', '||source_x||', '||source_y||', '||target_x||
405                   ', '||target_y||', '||delta||', '''||cost_column||''', '''||reverse_cost_column||''', ''to_cost'', '||text(dir)||', '||text(rc)||')'
406  LOOP
407    g.id := r.id;
408    g.gid := r.gid;
409    g.the_geom := r.the_geom;
410    RETURN NEXT g;
411  END LOOP;
412 
413END;
414$$
415
416LANGUAGE 'plpgsql' VOLATILE STRICT;
417
418
419CREATE OR REPLACE FUNCTION shootingstar_sp_smart(
420       geom_table varchar, source_x float8, source_y float8, target_x float8, target_y float8, delta float8, cost_column varchar, reverse_cost_column varchar, to_cost_column varchar, dir boolean, rc boolean)
421       RETURNS SETOF GEOMS AS
422$$
423DECLARE
424        rec record;
425        r record;
426        path_result record;
427        v_id integer;
428        e_id integer;
429        geom geoms;
430       
431        intersection text;
432
433        srid integer;
434       
435        s_gid integer;
436        t_gid integer;
437
438        max_gid integer;
439        max_vertex_id integer;
440       
441        l_pair links;
442        middle geometry;
443
444        ll_x float8;
445        ll_y float8;
446        ur_x float8;
447        ur_y float8;
448
449        query text;
450        i integer;
451
452        id integer;
453       
454        seqname text;
455       
456        source edge;
457        target edge;
458       
459        curr edge;
460        tmp edge;
461       
462        extra_edges edge_array;
463BEGIN
464
465        id :=0;
466        FOR rec IN EXECUTE
467            'select srid(the_geom) from ' ||
468            quote_ident(geom_table) || ' limit 1'
469        LOOP
470        END LOOP;
471        srid := rec.srid;
472
473        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
474           ' THEN '||source_x||' ELSE '||target_x||
475           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
476           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
477        LOOP
478        END LOOP;
479
480        ll_x := rec.ll_x;
481        ur_x := rec.ur_x;
482
483        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
484            target_y||' THEN '||source_y||' ELSE '||
485            target_y||' END as ll_y, CASE WHEN '||
486            source_y||'>'||target_y||' THEN '||
487            source_y||' ELSE '||target_y||' END as ur_y'
488        LOOP
489        END LOOP;
490
491        ll_y := rec.ll_y;
492        ur_y := rec.ur_y;
493       
494        -- Searching for the source and target edges
495        SELECT find_nearest_link_within_distance_xy(source_x, source_y, delta, geom_table) INTO s_gid;
496        SELECT find_nearest_link_within_distance_xy(target_x, target_y, delta, geom_table) INTO t_gid;
497
498--        RAISE NOTICE 'SELECT gid,source,target,x1,y1,x2,y2,length,reverse_cost,id,class_id,to_cost::double precision,rule,the_geom FROM % WHERE gid = %', quote_ident(geom_table), s_gid;
499--        RAISE NOTICE 'SELECT gid,source,target,x1,y1,x2,y2,length,reverse_cost,id,class_id,to_cost::double precision,rule,the_geom FROM % WHERE gid = %', quote_ident(geom_table), t_gid;
500
501        FOR rec IN EXECUTE 'SELECT gid,source,target,x1,y1,x2,y2,'||cost_column||' as length, '||reverse_cost_column||' as reverse_cost,'||to_cost_column||'||''.0'' as to_cost,rule,the_geom FROM ' || quote_ident(geom_table) || ' WHERE gid = ' || s_gid
502        LOOP
503        END LOOP;
504       
505        source.gid          := rec.gid;
506        source.target       := rec.target;
507        source.source       := rec.source;
508        source.x1           := rec.x1;
509        source.y1           := rec.y1;
510        source.x2           := rec.x2;
511        source.y2           := rec.y2;
512        source.length       := rec.length;
513        source.reverse_cost := rec.reverse_cost;
514--      source.id           := rec.id;
515--      source.class_id     := rec.class_id;
516        source.to_cost      := rec.to_cost;
517       
518--      RAISE NOTICE 'source.to_cost = %', source.to_cost::double precision;
519       
520        source.rule         := rec.rule;
521        source.the_geom     := rec.the_geom;
522
523        FOR rec IN EXECUTE 'SELECT gid,source,target,x1,y1,x2,y2,'||cost_column||' as length, '||reverse_cost_column||' as reverse_cost,'||to_cost_column||' as to_cost,rule,the_geom FROM ' || quote_ident(geom_table) || ' WHERE gid = ' || t_gid
524        LOOP
525        END LOOP;
526       
527        target.gid          := rec.gid;
528        target.target       := rec.target;
529        target.source       := rec.source;
530        target.x1           := rec.x1;
531        target.y1           := rec.y1;
532        target.x2           := rec.x2;
533        target.y2           := rec.y2;
534        target.length       := rec.length;
535        target.reverse_cost := rec.reverse_cost;
536--      target.id           := rec.id;
537--      target.class_id     := rec.class_id;
538        target.to_cost      := rec.to_cost;
539        target.rule         := rec.rule;
540        target.the_geom     := rec.the_geom;
541
542--        FOR rec IN EXECUTE 'SELECT max(gid) AS max_gid, greatest(max(source), max(target)) AS max_vertex_id FROM '
543--        || quote_ident(geom_table) || ' where setSRID(''BOX3D('||
544--          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
545--          ur_y+delta||')''::BOX3D, ' || srid || ') && the_geom'
546--      LOOP
547--      END LOOP;
548
549        FOR rec IN EXECUTE 'SELECT max_gid, max_vertex_id FROM network_info WHERE tname = ''' || quote_ident(geom_table) || ''''
550        LOOP
551        END LOOP;
552       
553        max_gid:=rec.max_gid;
554        max_vertex_id:=rec.max_vertex_id;
555       
556--      RAISE NOTICE 'max_gid=%, max_vertex_id=%', max_gid, max_vertex_id;
557
558        -- Locate source and target points
559        -- extra_edges[1] - source first
560        -- extra_edges[2] - source last
561        SELECT DISTINCT l, f FROM locate_point_as_geometry(geom_table, s_gid, source_x, source_y, true) INTO l_pair.l, l_pair.f;
562       
563        extra_edges.the_geom[1] := l_pair.f;
564        extra_edges.gid[1] := max_gid+1;
565        extra_edges.source[1] := source.source;
566        -- New target vertex (max_vertex_id+1)
567        extra_edges.target[1] := max_vertex_id+1;
568        extra_edges.x1[1] := source.x1;
569        extra_edges.y1[1] := source.y1;
570        extra_edges.x2[1] := x(startpoint(l_pair.f));
571        extra_edges.y2[1] := y(startpoint(l_pair.f));
572--      extra_edges.x2[1] := x(PointN(l_pair.f, NumPoints(l_pair.f)));
573--      extra_edges.y2[1] := y(PointN(l_pair.f, NumPoints(l_pair.f)));
574        extra_edges.length[1] := source.length*(length(l_pair.f)/length(source.the_geom));
575        extra_edges.reverse_cost[1] := source.reverse_cost*(length(l_pair.f)/length(source.the_geom));
576--      extra_edges.id[1] := source.id;
577--      extra_edges.class_id[1] := source.class_id;
578--      extra_edges.to_cost[1] := source.to_cost;
579        extra_edges.rule[1] := source.rule;
580        --extra_edges[1]:=curr;
581       
582        extra_edges.the_geom[2] := l_pair.l;
583        extra_edges.gid[2] := max_gid+2;
584        extra_edges.target[2] := source.target;
585        -- New target vertex (max_vertex_id+1)
586        extra_edges.source[2] := max_vertex_id+1;
587        extra_edges.x2[2] := source.x2;
588        extra_edges.y2[2] := source.y2;
589--      extra_edges.x1[2] := x(startpoint(l_pair.f));
590--      extra_edges.y1[2] := y(startpoint(l_pair.f));
591        extra_edges.x1[2] := x(PointN(l_pair.f, NumPoints(l_pair.f)));
592        extra_edges.y1[2] := y(PointN(l_pair.f, NumPoints(l_pair.f)));
593        extra_edges.length[2] := source.length*(length(l_pair.l)/length(source.the_geom));
594        extra_edges.reverse_cost[2] := source.reverse_cost*(length(l_pair.l)/length(source.the_geom));
595--      extra_edges.id[2] := source.id;
596--      extra_edges.class_id[2] := source.class_id;
597        extra_edges.to_cost[2] := source.to_cost;
598        extra_edges.rule[2] := source.rule;
599        --extra_edges[2]:=curr;
600       
601        -- extra_edges[3] - target first
602        -- extra_edges[4] - target last
603        SELECT DISTINCT l, f FROM locate_point_as_geometry(geom_table, t_gid, target_x, target_y, true) INTO l_pair.l, l_pair.f;
604        extra_edges.the_geom[3] := l_pair.f;
605        extra_edges.gid[3] := max_gid+3;
606        extra_edges.source[3] := target.source;
607        -- New target vertex (max_vertex_id+2)
608        extra_edges.target[3] := max_vertex_id+2;
609        extra_edges.x1[3] := target.x1;
610        extra_edges.y1[3] := target.y1;
611        extra_edges.x2[3] := x(endpoint(l_pair.f));
612        extra_edges.y2[3] := y(endpoint(l_pair.f));
613        extra_edges.length[3] := target.length*(length(l_pair.f)/length(target.the_geom));
614        extra_edges.reverse_cost[3] := target.reverse_cost*(length(l_pair.f)/length(target.the_geom));
615--      extra_edges.id[3] := target.id;
616--      extra_edges.class_id[3] := target.class_id;
617        extra_edges.to_cost[3] := target.to_cost;
618        extra_edges.rule[3] := target.rule;
619        --extra_edges[3]:=curr;
620
621        extra_edges.the_geom[4] := l_pair.l;
622        extra_edges.gid[4] := max_gid+4;
623        extra_edges.target[4] := target.target;
624        -- New target vertex (max_vertex_id+2)
625        extra_edges.source[4] := max_vertex_id+2;
626        extra_edges.x2[4] := target.x2;
627        extra_edges.y2[4] := target.y2;
628        extra_edges.x1[4] := x(endpoint(l_pair.f));
629        extra_edges.y1[4] := y(endpoint(l_pair.f));
630        extra_edges.length[4] := target.length*(length(l_pair.l)/length(target.the_geom));
631        extra_edges.reverse_cost[4] := target.reverse_cost*(length(l_pair.l)/length(target.the_geom));
632--      extra_edges.id[4] := target.id;
633--      extra_edges.class_id[4] := target.class_id;
634        extra_edges.to_cost[4] := target.to_cost;
635        extra_edges.rule[4] := target.rule;
636        --extra_edges[4]:=curr;
637
638        -- extra_edges[5] - extra source edge
639        -- extra_edges[6] - extra target edge
640
641        --tmp := extra_edges[1];
642       
643        extra_edges.the_geom[5] := geometryfromtext('MULTILINESTRING(('||source_x||' '||source_y||','||extra_edges.x1[2]||' '||extra_edges.y1[2]||'))', srid);
644
645--      RAISE NOTICE 'source.rule = %', source.rule;
646
647        extra_edges.gid[5] := max_gid+5;
648        -- New target vertex (max_vertex_id+3)
649        extra_edges.source[5] := max_vertex_id+3;
650        extra_edges.target[5] := extra_edges.target[1];
651        extra_edges.x1[5] := source_x;
652        extra_edges.y1[5] := source_y;
653        extra_edges.x2[5] := extra_edges.x2[2];
654        extra_edges.y2[5] := extra_edges.y2[2];
655        extra_edges.length[5] := length(extra_edges.the_geom[5]);
656        extra_edges.reverse_cost[5] := 1000000.0;
657--      extra_edges.id[5] := 0;
658--      extra_edges.class_id[5] := source.class_id;
659--      RAISE NOTICE 'class_id[5] = %', extra_edges.class_id[5];
660        extra_edges.to_cost[5] := NULL;--source.to_cost;
661--      RAISE NOTICE 'to_cost[5] = %', extra_edges.to_cost[5];
662        extra_edges.rule[5] := NULL;--source.rule;
663--      RAISE NOTICE 'rule[5] = %', extra_edges.rule[5];
664        --extra_edges[5]:=curr;
665
666        --tmp := extra_edges[3];
667       
668        extra_edges.the_geom[6] := geometryfromtext('MULTILINESTRING(('||extra_edges.x2[3]||' '||extra_edges.y2[3]||','||target_x||' '||target_y||'))', srid);
669
670        extra_edges.gid[6] := max_gid+6;
671       
672--      RAISE NOTICE 'the_geom[6] = %', extra_edges.the_geom[6];
673       
674        -- New target vertex (max_vertex_id+4)
675        extra_edges.source[6] := max_vertex_id+4;
676        extra_edges.target[6] := extra_edges.target[3];
677        extra_edges.x2[6] := target_x;
678        extra_edges.y2[6] := target_y;
679        extra_edges.x1[6] := extra_edges.x2[3];
680        extra_edges.y1[6] := extra_edges.y2[3];
681        extra_edges.length[6] := length(extra_edges.the_geom[6]);
682        extra_edges.reverse_cost[6] := 1000000.0;
683--      extra_edges.id[6] := 0;
684--      extra_edges.class_id[6] := target.class_id;
685        extra_edges.to_cost[6] := target.to_cost;
686        extra_edges.rule[6] := target.rule;
687        --extra_edges[6]:=curr;
688
689        select relname INTO seqname from pg_class where relname='rownum';
690       
691        IF seqname IS NOT NULL THEN
692        EXECUTE 'drop sequence rownum';
693        END IF;
694
695        EXECUTE 'create sequence rownum';
696       
697        IF s_gid = t_gid THEN
698         
699          SELECT * FROM get_middle(geom_table, s_gid, source_x, source_y, target_x, target_y, true) INTO middle;
700         
701          geom.gid := extra_edges.gid[5];
702          geom.the_geom := extra_edges.the_geom[5];
703          geom.id := 0;   
704          RETURN NEXT geom;
705
706          geom.gid := extra_edges.gid[1];
707          geom.the_geom := middle;
708          geom.id := 1;
709          RETURN NEXT geom;
710
711          geom.gid := extra_edges.gid[6];
712          geom.the_geom := extra_edges.the_geom[6];
713          geom.id := 2;
714          RETURN NEXT geom;
715         
716          RETURN;
717
718        END IF;
719               
720       
721        -- Shooting* search query
722        -- Need to search for new geometries in the array instead of the table
723        query := 'select distinct a.rownum as id, a.edge_id, b.gid, b.the_geom from (select nextval(''rownum'') as rownum, edge_id from ' ||
724          'shortest_path_shooting_star(''SELECT gid as id, source::integer, ' ||
725          'target::integer, '||cost_column||'::double precision as cost, ' ||
726          'x1::double precision, y1::double precision, x2::double ' ||
727          'precision, y2::double precision, rule::varchar, ' ||
728          to_cost_column||'::double precision as to_cost ';
729         
730        IF rc THEN query := query || ' , '||reverse_cost_column||'::double precision as reverse_cost '; 
731        END IF;
732         
733        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
734          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
735          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom';
736
737--      RAISE NOTICE 'Query: %', query;
738
739        -- Newly created edges should be appended here
740        FOR i IN 1..6 LOOP
741          --curr := extra_edges[i];
742--        RAISE NOTICE 'i=%', i;
743          IF extra_edges.rule[i] IS NULL THEN extra_edges.rule[i]:='NULL';
744          ELSE extra_edges.rule[i]:=''''''||extra_edges.rule[i]||'''''';
745          END IF;
746          IF extra_edges.to_cost[i] IS NULL THEN extra_edges.to_cost[i]:=0;
747          END IF;
748         
749          query := query || ' UNION ALL SELECT ' || extra_edges.gid[i]::integer || ', ' || extra_edges.source[i]::integer ||
750                   ', ' || extra_edges.target[i]::integer || ', ' || extra_edges.length[i]::double precision ||
751                   ', ' || extra_edges.x1[i]::double precision || ', ' || extra_edges.y1[i]::double precision ||
752                   ', ' || extra_edges.x2[i]::double precision || ', ' || extra_edges.y2[i]::double precision ||
753                   ', ' || extra_edges.rule[i]::varchar || ', ' || extra_edges.to_cost[i]::double precision;
754                   IF rc THEN query := query || ' , ' || extra_edges.reverse_cost[i]::double precision;
755                   END IF;
756--      RAISE NOTICE 'Query: %', query;
757        END LOOP;
758       
759        -- Need to use new ids as source and target
760
761          --curr := extra_edges[5];
762          --tmp := extra_edges[6];
763
764--      RAISE NOTICE 'Query: %', query;
765
766        query := query || ' ORDER BY id'', ' ||           
767          quote_literal(extra_edges.gid[5]) || ' , ' ||
768          quote_literal(extra_edges.gid[6]) || ' , '''||text(dir)||''', '''||text(rc)||''' ) ) a LEFT JOIN ' ||
769          quote_ident(geom_table) || ' b ON (a.edge_id=b.gid) ';
770         
771        --RAISE NOTICE 'max_gid: %, max_vertex_id: %', max_gid, max_vertex_id;
772--      RAISE NOTICE 'Query: %', query;
773       
774        --geom.gid := extra_edges.gid[5];
775        --geom.the_geom := extra_edges.the_geom[5];
776        --geom.id := 0;
777       
778        --RETURN NEXT geom;
779       
780        FOR path_result IN EXECUTE query
781        LOOP
782                 geom.gid      := path_result.edge_id;
783--               RAISE NOTICE ' -- gid: %', geom.gid;
784                 
785                 -- Need to search for new geometries in the array instead of the table
786                 IF geom.gid > max_gid THEN
787                   --curr := extra_edges[geom.gid-max_gid];
788                   geom.the_geom := extra_edges.the_geom[geom.gid-max_gid];
789                 ELSE
790                   geom.the_geom := path_result.the_geom;
791                 END IF;
792                 
793                 id := id+1;
794--                 geom.id       := id;
795                 geom.id       := path_result.id;
796                 
797                 RETURN NEXT geom;
798
799        END LOOP;
800       
801        RETURN;
802END;
803$$
804LANGUAGE 'plpgsql' VOLATILE STRICT;
805
806
807CREATE OR REPLACE FUNCTION sp_smart_directed(
808       geom_table varchar, heuristic boolean, source_x float8, source_y float8, target_x float8, target_y float8,
809       delta float8, cost_column varchar, reverse_cost_column varchar, dir boolean, rc boolean)
810       RETURNS SETOF GEOMS AS
811$$
812DECLARE
813        rec record;
814        r record;
815        path_result record;
816        v_id integer;
817        e_id integer;
818        geom geoms;
819
820        srid integer;
821       
822        s_gid integer;
823        t_gid integer;
824
825        max_gid integer;
826        max_vertex_id integer;
827       
828        l_pair links;
829        middle geometry;
830
831        ll_x float8;
832        ll_y float8;
833        ur_x float8;
834        ur_y float8;
835
836        query text;
837        i integer;
838       
839        fname text;
840        seqname text;
841
842        id integer;
843       
844        source edge;
845        target edge;
846       
847        curr edge;
848        tmp edge;
849       
850        extra_edges edge_array;
851BEGIN
852
853        id :=0;
854       
855        IF heuristic THEN fname = 'shortest_path_astar';
856        ELSE fname = 'shortest_path';
857        END IF;
858       
859       
860        FOR rec IN EXECUTE
861            'select srid from geometry_columns where f_table_name= ''' ||
862            quote_ident(geom_table)||''''
863        LOOP
864        END LOOP;
865        srid := rec.srid;
866
867--      RAISE NOTICE 'SRID is set';
868
869
870        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_x||'<'||target_x||
871           ' THEN '||source_x||' ELSE '||target_x||
872           ' END as ll_x, CASE WHEN '||source_x||'>'||target_x||
873           ' THEN '||source_x||' ELSE '||target_x||' END as ur_x'
874        LOOP
875        END LOOP;
876
877        ll_x := rec.ll_x;
878        ur_x := rec.ur_x;
879
880        FOR rec IN EXECUTE 'SELECT CASE WHEN '||source_y||'<'||
881            target_y||' THEN '||source_y||' ELSE '||
882            target_y||' END as ll_y, CASE WHEN '||
883            source_y||'>'||target_y||' THEN '||
884            source_y||' ELSE '||target_y||' END as ur_y'
885        LOOP
886        END LOOP;
887
888        ll_y := rec.ll_y;
889        ur_y := rec.ur_y;
890       
891        -- Searching for the source and target edges
892       
893--      RAISE NOTICE 'Searching for the source and target edges';
894       
895        SELECT find_nearest_link_within_distance_xy(source_x, source_y, delta, geom_table) INTO s_gid;
896        SELECT find_nearest_link_within_distance_xy(target_x, target_y, delta, geom_table) INTO t_gid;
897
898--      RAISE NOTICE 'Nearest links were found';
899
900        FOR rec IN EXECUTE 'SELECT DISTINCT gid,source,target,x1,y1,x2,y2,'||cost_column||' as length, '||reverse_cost_column||' as reverse_cost,the_geom FROM ' || quote_ident(geom_table) || ' WHERE gid = ' || s_gid
901        LOOP
902        END LOOP;
903       
904        source.gid          := rec.gid;
905        source.target       := rec.target;
906        source.source       := rec.source;
907        source.x1           := rec.x1;
908        source.y1           := rec.y1;
909        source.x2           := rec.x2;
910        source.y2           := rec.y2;
911        source.length       := rec.length;
912        source.reverse_cost := rec.reverse_cost;
913        source.the_geom     := rec.the_geom;
914
915        FOR rec IN EXECUTE 'SELECT DISTINCT gid,source,target,x1,y1,x2,y2,'||cost_column||' as length, '||reverse_cost_column||' as reverse_cost,the_geom FROM ' || quote_ident(geom_table) || ' WHERE gid = ' || t_gid
916        LOOP
917        END LOOP;
918       
919        target.gid          := rec.gid;
920        target.target       := rec.target;
921        target.source       := rec.source;
922        target.x1           := rec.x1;
923        target.y1           := rec.y1;
924        target.x2           := rec.x2;
925        target.y2           := rec.y2;
926        target.length       := rec.length;
927        target.reverse_cost := rec.reverse_cost;
928        target.the_geom     := rec.the_geom;
929
930--      RAISE NOTICE 'Searching for max gid and node id';
931       
932--        FOR rec IN EXECUTE 'SELECT max(gid) AS max_gid, greatest(max(source), max(target)) AS max_vertex_id FROM '
933--        || quote_ident(geom_table) || ' where setSRID(''BOX3D('||
934--          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
935--          ur_y+delta||')''::BOX3D, ' || srid || ') && the_geom'
936--      LOOP
937--      END LOOP;
938
939        FOR rec IN EXECUTE 'SELECT max_gid, max_vertex_id FROM network_info WHERE tname = ''' || quote_ident(geom_table) || ''''
940        LOOP
941        END LOOP;
942       
943        max_gid:=rec.max_gid;
944        max_vertex_id:=rec.max_vertex_id;
945
946--      RAISE NOTICE 'Max gid and node id were found';
947
948        -- Locate source and target points
949        -- extra_edges[1] - source first
950        -- extra_edges[2] - source last
951        SELECT DISTINCT l, f FROM locate_point_as_geometry(geom_table, s_gid, source_x, source_y, false) INTO l_pair.l, l_pair.f;
952
953--        RAISE NOTICE 'Creating fake edges';
954       
955        extra_edges.the_geom[1] := l_pair.f;
956        extra_edges.gid[1] := max_gid+1;
957        extra_edges.source[1] := source.source;
958        -- New target vertex (max_vertex_id+1)
959        extra_edges.target[1] := max_vertex_id+1;
960        extra_edges.x1[1] := source.x1;
961        extra_edges.y1[1] := source.y1;
962        extra_edges.x2[1] := x(startpoint(l_pair.f));
963        extra_edges.y2[1] := y(startpoint(l_pair.f));
964        extra_edges.length[1] := source.length*(length(l_pair.f)/length(source.the_geom));
965        extra_edges.reverse_cost[1] := source.reverse_cost*(length(l_pair.f)/length(source.the_geom));
966       
967        extra_edges.the_geom[2] := l_pair.l;
968        extra_edges.gid[2] := max_gid+2;
969        extra_edges.target[2] := source.target;
970        -- New target vertex (max_vertex_id+1)
971        extra_edges.source[2] := max_vertex_id+1;
972        extra_edges.x2[2] := source.x2;
973        extra_edges.y2[2] := source.y2;
974        extra_edges.x1[2] := x(PointN(l_pair.f, NumPoints(l_pair.f)));
975        extra_edges.y1[2] := y(PointN(l_pair.f, NumPoints(l_pair.f)));
976        extra_edges.length[2] := source.length*(length(l_pair.l)/length(source.the_geom));
977        extra_edges.reverse_cost[2] := source.reverse_cost*(length(l_pair.l)/length(source.the_geom));
978       
979        -- extra_edges[3] - target first
980        -- extra_edges[4] - target last
981        SELECT DISTINCT l, f FROM locate_point_as_geometry(geom_table, t_gid, target_x, target_y, false) INTO l_pair.l, l_pair.f;
982        extra_edges.the_geom[3] := l_pair.f;
983        extra_edges.gid[3] := max_gid+3;
984        extra_edges.source[3] := target.source;
985        -- New target vertex (max_vertex_id+2)
986        extra_edges.target[3] := max_vertex_id+2;
987        extra_edges.x1[3] := target.x1;
988        extra_edges.y1[3] := target.y1;
989        extra_edges.x2[3] := x(endpoint(l_pair.f));
990        extra_edges.y2[3] := y(endpoint(l_pair.f));
991        extra_edges.length[3] := target.length*(length(l_pair.f)/length(target.the_geom));
992        extra_edges.reverse_cost[3] := target.reverse_cost*(length(l_pair.f)/length(target.the_geom));
993
994        extra_edges.the_geom[4] := l_pair.l;
995        extra_edges.gid[4] := max_gid+4;
996        extra_edges.target[4] := target.target;
997        -- New target vertex (max_vertex_id+2)
998        extra_edges.source[4] := max_vertex_id+2;
999        extra_edges.x2[4] := target.x2;
1000        extra_edges.y2[4] := target.y2;
1001        extra_edges.x1[4] := x(endpoint(l_pair.f));
1002        extra_edges.y1[4] := y(endpoint(l_pair.f));
1003        extra_edges.length[4] := target.length*(length(l_pair.l)/length(target.the_geom));
1004        extra_edges.reverse_cost[4] := target.reverse_cost*(length(l_pair.l)/length(target.the_geom));
1005
1006        extra_edges.the_geom[5] := geometryfromtext('LINESTRING('||source_x||' '||source_y||','||extra_edges.x1[2]||' '||extra_edges.y1[2]||')', srid);
1007
1008        extra_edges.gid[5] := max_gid+5;
1009        -- New target vertex (max_vertex_id+3)
1010        extra_edges.source[5] := max_vertex_id+3;
1011        extra_edges.target[5] := extra_edges.target[1];
1012        extra_edges.x1[5] := source_x;
1013        extra_edges.y1[5] := source_y;
1014        extra_edges.x2[5] := extra_edges.x2[2];
1015        extra_edges.y2[5] := extra_edges.y2[2];
1016        extra_edges.length[5] := length(extra_edges.the_geom[5]);
1017        extra_edges.reverse_cost[5] := 1000000.0;
1018
1019        extra_edges.the_geom[6] := geometryfromtext('LINESTRING('||extra_edges.x2[3]||' '||extra_edges.y2[3]||','||target_x||' '||target_y||')', srid);
1020
1021        extra_edges.gid[6] := max_gid+6;
1022       
1023        -- New target vertex (max_vertex_id+4)
1024        extra_edges.source[6] := max_vertex_id+4;
1025        extra_edges.target[6] := extra_edges.target[3];
1026        extra_edges.x2[6] := target_x;
1027        extra_edges.y2[6] := target_y;
1028        extra_edges.x1[6] := extra_edges.x2[3];
1029        extra_edges.y1[6] := extra_edges.y2[3];
1030        extra_edges.length[6] := length(extra_edges.the_geom[6]);
1031        extra_edges.reverse_cost[6] := 1000000.0;
1032       
1033        select relname INTO seqname from pg_class where relname='rownum';
1034       
1035        IF seqname IS NOT NULL THEN
1036        EXECUTE 'drop sequence rownum';
1037        END IF;
1038
1039        EXECUTE 'create sequence rownum';
1040       
1041        IF s_gid = t_gid THEN
1042         
1043          SELECT * FROM get_middle(geom_table, s_gid, source_x, source_y, target_x, target_y, true) INTO middle;
1044         
1045          geom.gid := extra_edges.gid[5];
1046          geom.the_geom := extra_edges.the_geom[5];
1047          geom.id := 0;   
1048          RETURN NEXT geom;
1049
1050          geom.gid := extra_edges.gid[1];
1051          geom.the_geom := middle;
1052          geom.id := 1;
1053          RETURN NEXT geom;
1054
1055          geom.gid := extra_edges.gid[6];
1056          geom.the_geom := extra_edges.the_geom[6];
1057          geom.id := 2;
1058          RETURN NEXT geom;
1059         
1060          RETURN;
1061
1062        END IF;
1063
1064        -- Main search query
1065        -- Need to search for new geometries in the array instead of the table
1066        query := 'select distinct a.rownum as id, a.edge_id, b.gid, b.the_geom from (select nextval(''rownum'') as rownum, edge_id from ' ||
1067          fname || '(''SELECT DISTINCT gid as id, source::integer, ' ||
1068          'target::integer, '||cost_column||'::double precision as cost, ' ||
1069          'x1::double precision, y1::double precision, x2::double ' ||
1070          'precision, y2::double precision ';
1071         
1072        IF rc THEN query := query || ' , '||reverse_cost_column||' as reverse_cost '; 
1073        END IF;
1074         
1075        query := query || 'FROM ' || quote_ident(geom_table) || ' where setSRID(''''BOX3D('||
1076          ll_x-delta||' '||ll_y-delta||','||ur_x+delta||' '||
1077          ur_y+delta||')''''::BOX3D, ' || srid || ') && the_geom';
1078
1079--      RAISE NOTICE 'Query: %', query;
1080
1081        -- Newly created edges should be appended here
1082        FOR i IN 1..6 LOOP
1083          --curr := extra_edges[i];
1084--        RAISE NOTICE 'i=%', i;
1085          IF extra_edges.rule[i] IS NULL THEN extra_edges.rule[i]:='NULL';
1086          ELSE extra_edges.rule[i]:=''''''||extra_edges.rule[i]||'''''';
1087          END IF;
1088         
1089          query := query || ' UNION ALL SELECT ' || extra_edges.gid[i]::integer || ', ' || extra_edges.source[i]::integer ||
1090                   ', ' || extra_edges.target[i]::integer || ', ' || extra_edges.length[i]::double precision ||
1091                   ', ' || extra_edges.x1[i]::double precision || ', ' || extra_edges.y1[i]::double precision ||
1092                   ', ' || extra_edges.x2[i]::double precision || ', ' || extra_edges.y2[i]::double precision;
1093                   IF rc THEN query := query || ' , ' || extra_edges.reverse_cost[i]::double precision;
1094                   END IF;
1095--      RAISE NOTICE 'Query: %', query;
1096        END LOOP;
1097       
1098        -- Need to use new ids as source and target
1099
1100          --curr := extra_edges[5];
1101          --tmp := extra_edges[6];
1102
1103--      RAISE NOTICE 'Query: %', query;
1104
1105        query := query || ''', ' ||       
1106          quote_literal(extra_edges.source[5]) || ' , ' ||
1107          quote_literal(extra_edges.target[6]) || ' , '''||text(dir)||''', '''||text(rc)||''' ) ) a LEFT JOIN ' ||
1108          quote_ident(geom_table) || ' b ON (a.edge_id=b.gid) ';
1109         
1110        --RAISE NOTICE 'max_gid: %, max_vertex_id: %', max_gid, max_vertex_id;
1111--      RAISE NOTICE 'Query: %', query;
1112       
1113        --geom.gid := extra_edges.gid[5];
1114        --geom.the_geom := extra_edges.the_geom[5];
1115        --geom.id := 0;
1116       
1117        --RETURN NEXT geom;
1118       
1119        FOR path_result IN EXECUTE query
1120        LOOP
1121                 geom.gid      := path_result.edge_id;
1122--               RAISE NOTICE ' -- gid: %', geom.gid;
1123                 
1124                 -- Need to search for new geometries in the array instead of the table
1125                 IF geom.gid > max_gid THEN
1126                   geom.the_geom := extra_edges.the_geom[geom.gid-max_gid];
1127                 ELSE
1128                   geom.the_geom := path_result.the_geom;
1129                 END IF;
1130                 
1131                 id := id+1;
1132                 geom.id       := path_result.id;
1133                 
1134                 RETURN NEXT geom;
1135
1136        END LOOP;
1137       
1138        RETURN;
1139END;
1140$$
1141LANGUAGE 'plpgsql' VOLATILE STRICT;
Note: See TracBrowser for help on using the browser.