Context Navigation

shootingstar (#18) - ordered result ? (#285) - Message List

Tree View Flat View (newer first) Flat View (older first)

Message #1014

Which version of pgRouting do you use?
Later versions of pgRouting also return an ordered "id" attribute.

daniel

08/13/09 09:45:04 (15 months ago)
- Message #1015
  
  i use version 1.02 but now i tried this wrappers : http://pgrouting.postlbs.org/browser/sandbox/wrappers/routing_core_smart.sql see on this topic : http://pgrouting.postlbs.org/discussion/topic/252 who return an ordered "id" attribute and trim the starting and ending edge.
  
  it's very well but i got some strange result. For some query a got no result even with a very big "delta" (1 000 000 meters) which take almost all my data. And logically, when you check the data, a result is possible.
  
  Do you had some case like that or there is a fix that i miss ?
  
  marcandre
  
  08/14/09 00:31:26 (15 months ago)
  - Message #1016
    
    I check some new results from the wrappers and is it possible that the turn restrictions are not respected for the extra edges ?
    
    Usually, the first two and last two
    
    marcandre
    
    08/14/09 04:23:27 (15 months ago)
  - Message #1017
    
    This is hard to say without knowing more about your data and how the result is wrong.
    
    In general (personal experience) the reason for something not being correct is the data. Can be that network topology isn't build correctly for example. If you use turn restrictions, is the logic correct, etc..
    
    daniel
    
    08/14/09 08:14:31 (15 months ago)
    - Message #1018
      
      I check some new results from the wrappers and is it possible that the turn restrictions are not respected for the extra edges ? Usually, the first two and last two
      
      This could be a bug in the "smart" wrapper. I don't think it was tested with turn restrictions from me or Anton. If you can provide more details here would be helpful.
      
      daniel
      
      08/14/09 08:15:19 (15 months ago)
      - Message #1020
        
        apparently, because the 'gid' of the second extra edge or penultimate edge is a new one, the column 'rule' does not match anymore for the turn restriction.
        
        is it possible ?
        
        marcandre
        
        08/14/09 23:31:43 (15 months ago)
        
        Message #1021
        
        apparently, because the 'gid' of the second extra edge or penultimate edge is a new one, the column 'rule' does not match anymore for the turn restriction. is it possible ?
        
        I saw in the code, the line where you transforms 'rule' column ... it's Ok.
        
        I will read carefully in the future before posting a stupid question =)
        
        marcandre
        
        08/14/09 23:46:20 (15 months ago)
      - Message #1022
        
        example :
        
        1 2 ----0 0----- | | 4| 5| | | ----0------0----- 6 7 3 and the turn restriction like this gid rule 5 7
        if i want to do '6' to '2', it's gonna be OK and the turn restriction will be respected but if i do '7' to '2', the turn restriction will not be respected
        
        marcandre
        
        08/15/09 00:26:55 (15 months ago)
        
        Message #1025
        
        Hi,
        
        It is difficult to tell what is the reason without seeing the rest of your data. I can try to test it if you send (or post here) a little bit bigger piece of your data.
        
        Anton.
        
        anton
        
        08/17/09 09:45:52 (15 months ago)
        
        Message #1029
        
        hi! i can't post or send my data, but here is the code i wrote. i think it supposed to give a good result. The code goes just after the append of the new edges.
        
        for rec in execute 'select * from '||geom_table||' WHERE rule like ''%'||source.gid||'%''' LOOP new_rule := replace(rec.rule, source.gid::text, extra_edges.gid[1]::text); --raise notice 'new_rule: %', new_rule; query := query || ' UNION ALL SELECT ' || rec.gid::integer || ', ' || rec.source::integer || ', ' || rec.target::integer || ', ' || rec.cost::double precision || ', ' || rec.x1::double precision || ', ' || rec.y1::double precision || ', ' || rec.x2::double precision || ', ' || rec.y2::double precision || ', ''' || quote_literal(new_rule) || ''', ' || rec.to_cost::double precision; IF rc THEN query := query || ' , ' || rec.reverse_cost::double precision; END IF; new_rule := replace(rec.rule, source.gid::text, extra_edges.gid[2]::text); --raise notice 'new_rule: %', new_rule; query := query || ' UNION ALL SELECT ' || rec.gid::integer || ', ' || rec.source::integer || ', ' || rec.target::integer || ', ' || rec.cost::double precision || ', ' || rec.x1::double precision || ', ' || rec.y1::double precision || ', ' || rec.x2::double precision || ', ' || rec.y2::double precision || ', ''' ||quote_literal(new_rule) || ''', ' || rec.to_cost::double precision; IF rc THEN query := query || ' , ' || rec.reverse_cost::double precision; END IF; if rec.gid = target.gid THEN new_rule := replace(rec.rule, source.gid::text, extra_edges.gid[2]::text); query := query || ' UNION ALL SELECT ' || extra_edges.gid[3]::integer || ', ' || extra_edges.source[3]::integer || ', ' || extra_edges.target[3]::integer || ', ' || extra_edges.length[3]::double precision || ', ' || extra_edges.x1[3]::double precision || ', ' || extra_edges.y1[3]::double precision || ', ' || extra_edges.x2[3]::double precision || ', ' || extra_edges.y2[3]::double precision || ', ''' || quote_literal(new_rule) || ''', ' || extra_edges.to_cost[3]::double precision; IF rc THEN query := query || ' , ' || extra_edges.reverse_cost[3]::double precision; END IF; new_rule := replace(rec.rule, source.gid::text, extra_edges.gid[1]::text); query := query || ' UNION ALL SELECT ' || extra_edges.gid[3]::integer || ', ' || extra_edges.source[3]::integer || ', ' || extra_edges.target[3]::integer || ', ' || extra_edges.length[3]::double precision || ', ' || extra_edges.x1[3]::double precision || ', ' || extra_edges.y1[3]::double precision || ', ' || extra_edges.x2[3]::double precision || ', ' || extra_edges.y2[3]::double precision || ', ''' || quote_literal(new_rule) || ''', ' || extra_edges.to_cost[3]::double precision; IF rc THEN query := query || ' , ' || extra_edges.reverse_cost[3]::double precision; END IF; new_rule := replace(rec.rule, source.gid::text, extra_edges.gid[2]::text); query := query || ' UNION ALL SELECT ' || extra_edges.gid[4]::integer || ', ' || extra_edges.source[4]::integer || ', ' || extra_edges.target[4]::integer || ', ' || extra_edges.length[4]::double precision || ', ' || extra_edges.x1[4]::double precision || ', ' || extra_edges.y1[4]::double precision || ', ' || extra_edges.x2[4]::double precision || ', ' || extra_edges.y2[4]::double precision || ', ''' || quote_literal(new_rule) || ''', ' || extra_edges.to_cost[4]::double precision; IF rc THEN query := query || ' , ' || extra_edges.reverse_cost[4]::double precision; END IF; new_rule := replace(rec.rule, source.gid::text, extra_edges.gid[1]::text); query := query || ' UNION ALL SELECT ' || extra_edges.gid[4]::integer || ', ' || extra_edges.source[4]::integer || ', ' || extra_edges.target[4]::integer || ', ' || extra_edges.length[4]::double precision || ', ' || extra_edges.x1[4]::double precision || ', ' || extra_edges.y1[4]::double precision || ', ' || extra_edges.x2[4]::double precision || ', ' || extra_edges.y2[4]::double precision || ', ''' || quote_literal(new_rule) || ''', ' || extra_edges.to_cost[4]::double precision; IF rc THEN query := query || ' , ' || extra_edges.reverse_cost[4]::double precision; END IF; END IF; END LOOP;
        does it tell you something ?
        
        marcandre
        
        08/18/09 06:11:46 (15 months ago)
        
        Message #1077
        
        Hi to every one... I have a similar problem using a map with srid=32632
        
        start point gid: 1375 source 1130 target 1131 stop point gid: 80 source 46 target 42
        
        A query like this: SELECT * from dijkstra_sp('strade_select', 1130, 42) order by id return: id;gid;the_geom 1;1375;"0105000020787F0000010000000102000000020000000000004079A01741000000C0580B53410000006020A2174100000040590B5341" 2;4289;"0105000020787F000001000000010200000002000000000000608AA41741000000A05A0B5341000000005CA61741000000A05B0B5341" 3;4401;"0105000020787F00000100000001020000000200000000000060E0A81741000000E05C0B5341000000C0E8A81741000000E04F0B5341" 4;1390;"0105000020787F0000010000000102000000020000000000006020A2174100000040590B5341000000608AA41741000000A05A0B5341" 5;4320;"0105000020787F000001000000010200000002000000000000C0AAA71741000000C05C0B534100000060E0A81741000000E05C0B5341" 6;80;"0105000020787F000001000000010200000002000000000000C0E8A81741000000E04F0B534100000040F7A81741000000E0400B5341" 7;4288;"0105000020787F000001000000010200000002000000000000005CA61741000000A05B0B5341000000C0AAA71741000000C05C0B5341"
        
        the true order is this: 1 1375 2 1390 3 4289 4 4288 5 4320 6 4401 7 80
        
        The same wrong result is out from SELECT strade_select.*, AsText?(strade_select.the_geom) AS wkt FROM strade_select, shortest_path_shooting_star('SELECT gid as id, source, target, cost,x1, y1, x2, y2, rule, to_cost FROM strade_select', 1375, 80, false, false) as rt WHERE strade_select.gid=rt.edge_id;
        
        and all routing algorithm.
        
        using the latest version of pgrouting (1.3)
        
        carlofrancesco
        
        09/15/09 23:48:46 (14 months ago)
        
        Message #1079
        
        same error with this:
        
        SELECT PRO.*,DIJ.EDGE_ID, AsText?(PRO.the_geom) As wkt
        FROM shortest_path('SELECT gid AS id, source::int4,
        
        target::int4, cost::double precision AS cost,reverse_cost::double
        
        precision
        
        AS reverse_cost FROM strade_select', 1130, 42, true, true) AS DIJ
        
        LEFT JOIN strade_select AS PRO ON (PRO.gid=DIJ.edge_id);
        
        but without joining with strade_select ...
        SELECT * FROM shortest_path('SELECT gid AS id, source::int4,
        
        target::int4, cost::double precision AS cost,reverse_cost::double precision
        
        AS reverse_cost FROM strade_select', 1130, 42, true, true)AS DIJ
        
        ... the algorithm works well !!!
        
        carlofrancesco
        
        09/16/09 01:40:44 (14 months ago)
        
        Message #1078
        
        Another thing is that on the wrong query result, every source is not the target for the next step.
        Example
        
        source;target
        1130;1131
        3569;87
        1138;3569
        87;3583
        3583;46
        1131;1138
        46;42
        
        it have to be:
        source;target
        1130;1131
        1131;1138
        1138;3569
        3569;87
        87;3583
        3583;46
        46;42
        
        Thanks for any advice !
        
        carlofrancesco
        
        09/16/09 01:09:32 (14 months ago)
        
        Message #1081
        
        Hi,
        
        I suspect your topology information to be incorrect. Can you please confirm that you really have same ids for same vertices?
        
        anton
        
        09/16/09 10:11:44 (14 months ago)
        
        Message #1082
        
        Thanks for your reply.
        
        I have just made this query:
        
        select * FROM
        shortest_path('SELECT gid AS id, source::int4,target::int4, cost::double precision AS cost,reverse_cost::double precision
        AS reverse_cost FROM strade_select', 1130, 42, true, true)
        vertex;edge_id
        1130;1375
        1131;1390
        1138;4289
        3569;4288
        87;4320
        3583;4401
        46;80
        42;-1
        The result is correct !! Instead if i do this query:
        
        select * , AsText?(b.the_geom) As wkt FROM shortest_path('SELECT gid AS id, source::int4,
        
        target::int4, cost::double precision AS cost,reverse_cost::double precision
        
        AS reverse_cost FROM strade_select', 1130, 42, true, true) as a
        
        left JOIN strade_select as b on(a.edge_id = b.gid );
        
        OR
        
        select * , AsText?(b.the_geom) As wkt FROM shortest_path('SELECT gid AS id, source::int4,
        
        target::int4, cost::double precision AS cost,reverse_cost::double precision
        
        AS reverse_cost FROM strade_select', 1130, 42, true, true) as a,
        
        strade_select as b where a.edge_id = b.gid ;
        
        Result (wrong) is the same
        
        vertex;edge_id
        1130;1375
        3569;4288
        1138;4289
        87;4320
        3583;4401
        1131;1390
        46;80
        The order is incorrect only when joining with strade_select ! With another DB it works well with these query.
        
        If my topology is incorrect the first query too should be wrong... But is correct. But without join the order is correct. What can i do? One billion of points to the magician that solve this enigma ! :D
        
        carlofrancesco
        
        09/16/09 17:24:30 (14 months ago)
        
        Message #1083
        
        I don't know what is happening but it's very very strange.
        
        If i made:
        
        select a.edge_id , b.nome, b.direzione, b.lunghezza, b.livello , b.n_europeo,b.gid, b.regione, b.provincia, b.sigla_prov, b.toponimo,b.numero_str
        
        FROM
        
        shortest_path('SELECT gid AS id, source::int4, target::int4, cost::double precision AS cost,reverse_cost::double precision AS reverse_cost FROM strade_select', (select source from strade_select where gid=1375), (select target from strade_select where gid=80), true, true) as a
        
        , (select *, AsText?(the_geom) As wkt from strade_select) as b where a.edge_id = b.gid;
        
        it works well... But i can't select one more column that the loose the order. there are 20 things i have to select in the query... But it faults with 12 or more elements. Someone can help me?
        
        carlofrancesco
        
        09/16/09 21:06:44 (14 months ago)
        
        Message #1086
        
        I would recommend you to try one of wrapper functions (i.e. dijkstra_sp()) - they return 'id' column you can sort by.
        
        For example:
        
        SELECT * FROM dijkstra_sp_delta('strade_select', 1130, 42, 0.1);
        
        anton
        
        09/17/09 14:39:27 (14 months ago)
        
        Message #1088
        
        Sure, i have done it... same result !! the order is incorrect, with: ORDER BY id too !!!!
        
        But it should be a postgres bug. Infact if i don't select * (all rows) but 12 or less the order is correct !!! I don't know why !!
        
        carlofrancesco
        
        09/17/09 18:25:50 (14 months ago)
        
        Message #1095
        
        Hello sorry for my bad english.
        
        the order by joining ist not secure in postgresql, wenn you wt an ordered result you musst give "set enable_mergejoin = false" befor your query but it take more execute time
        
        relassal
        
        09/22/09 16:26:17 (14 months ago)
        
        Message #1096
        
        for more information you cann see this site http://tuning.postgresql.de/postgresql_joins_planen
        
        relassal
        
        09/22/09 16:27:44 (14 months ago)
Message #1097

relassal, god bless you !![[BR]]

Thanks, i have made: set enable_hashjoin = false;

and it works!! It takes more execution time, but it works well.

I'll try to tuning it with postgres.conf I will post result

BUT THANKS !!!

carlofrancesco

09/22/09 23:23:09 (14 months ago)

Tree View Flat View (newer first) Flat View (older first)