Can pgr_dijkstra use a boolean column for routing?

Question

I want to use Dijkstra algorithm. I would like to have the algorithm find a route based on a boolean traffic column, using only roads where the boolean column value is true.

The code below does not return anything.

CREATE TABLE routeWithTraf AS SELECT seq, id1 AS node, id2 AS edge, cost, b.geom FROM pgr_dijkstra('
                SELECT gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                         FROM roads WHERE traffic != false',
                67931, 5602, false, false) a LEFT JOIN roads b ON (a.id2 = b.gid);

Answer 1

The first argument of a pgRouting shortest path query is an SQL statement. You can provide any query there, which returns:

• id
• source::integer
• target::integer
• cost::double precision

What does the query

SELECT gid AS id,
    source::integer,
    target::integer,
    length::double precision AS cost
    FROM roads WHERE traffic != false;

return?

If there is a result, then this result must contain also start and end ID. For routing only this selected network data will be used.

About your WHERE traffic != false part, why don't you write WHERE traffic, if you are looking for all roads, where traffic is true.

Answer 2

Is the traffic field going to be dynamically updated, or is it static ? (eg. is it to avoid congestion ?)

I have used similar properties before, but you risk that the integrity of your network graph becomes compromised, if no rout from A-B can be found. If traffic is a static field, and you are sure you have good connectivity in your graph, then it is no problem to do in the SQL.

In the setup i did, bridges with congestion became bottlenecks that the graph could not use, therefor the query returned some quite interesting suggestions...