pgRouting

Forum #18 - Topic #110 - Message List

Using a WHERE clause

Hi,

After some tests with pgRouting, now it is time to start tackling it serious heh :)

What I need to do is to have some Roads set to invalid every now and then (for example there is a road accident)

To complete this I am creating a new column in the streets layer : STATUS default to 1, when it is not working, I set it to 0.

First Question : Should I set to unavailable (status = 0) the whole road or the segments? If just the segments how can I query for all the segments that form part of a road please?

Second Question : Using the Shooting * Alogrithm is it possible to do a WHERE clause ( WHERE status = 1 ) so as to eliminate from the road topology the roads (or you will be eliminating segments) which are temporarily unavailable.

If the WHERE clause is impossible, do you suggest I make a second copy of the table (table_2) , I change the data on the second copy, then re-index it all together? After indexing finishes I rename the 1st table to table_3, and table_2 to table, and table_3 to table_2. Should I still use COPY or there is something else that duplicates the whole table (including indexes) ?

Thanks alot and hope I have some nice answers :)

yancho
01/29/08 19:00:30
Tree View Flat View (newer first) Flat View (older first)
  • Message #409

    No problem here! Just look inside routing_core_wrappers.sql file. It contains wrapper functions for the core functions.

    Look at the shootingstar_sp function. It has a query inside which selects the data from the table you specified by name. You just need to add your WHERE clause to that query and that's it!

    anton
    01/31/08 10:17:08
    • Message #410

      Thanks alot :)

      Should I edit this line :

      query := 'SELECT gid,the_geom FROM '

      'shortest_path_shooting_star(SELECT gid as id, source::integer, '

      'target::integer, 'cost_column'::double precision as cost, ' 'x1::double precision, y1::double precision, x2::double ' 'precision, y2::double precision, rule::varchar, ' 'to_cost::double precision ';

      to :

      query := 'SELECT gid,the_geom FROM '

      'shortest_path_shooting_star(SELECT gid as id, source::integer, '

      'target::integer, 'cost_column'::double precision as cost, ' 'x1::double precision, y1::double precision, x2::double ' 'precision, y2::double precision, rule::varchar, ' 'to_cost::double precision ' WHERE status = 1 ';

      ?

      yancho
      01/31/08 10:25:05
      • Message #411

        Yeah, but without the quotation mark between 'precision' and 'WHERE' :)

        anton
        01/31/08 10:31:18
        • Message #412

          hehe yep :) stupid me :D just there and it should work? coz there are other queries .. should I update all? btw is it ok if I make a new function and name it shootingstar_sp2 ? so i don't bork shootingstar_sp ?

          or then I need to update other functions?

          yancho
          01/31/08 10:34:33
          • Message #413

            Yes, this query selects edges to make a graph.

            You can try, for example, to create the new function which will accept one more parameter - status, so you can pass the status value.

            If you need some assistance - I'm at #pgrouting channel now.

            anton
            01/31/08 10:39:10
            • Message #417

              Thanks alot anton .. I fixed it :)

              I had to change another place : this is how it is now : http://yancho.pastebin.com/f74c4d7db basically I added where status = 1 in this line : query := query 'FROM ' quote_ident(geom_table) ' where status = 1 AND setSRID('BOX3D('

              I will try to make it accept a query string incase some others (/ me aswell heh) might want to use it .. but till now it seems fixed :)

              Proof : http://thesis.solutions-lab.net/disabling_road.jpg

              Red line - Status = 1 ... Blue dotted line : Status = 0 :D

              yancho
              02/01/08 04:56:03
Tree View Flat View (newer first) Flat View (older first)