[pgrouting-users] compare routes between full network and subset

pgRouting pgrouting-users
1

Hi,

I am looking to compare the directness of routes between the entirety of a
network and a subset of the same network.

I see in pgr_createTopology that there is a rows_where argument to filter
the network.

When I use pgr_dijkstra to calculate routes between node pairs, if I've
created two separate topologies, one with the full network and another with
the subset using "rows_where". Can i somehow specify which topology the
dijkstra algorithm should use?

It occurred to me that I could just include a where clause in the "edges
sql" that I provide pgr_dijkstra but I can't find an example of that being
done anywhere.

In previous work I've simply made two tables, network and network_subset
and then just created topologies for each and calculated routes for each
network separately but it seems like I'm just not seeing exactly how to do
the same thing in a simpler manner with the "rows_where" argument or some
other option.

Thanks for any solutions or suggestions!

Hugh

2

pgr_dijstra just takes an SQL query. The only distinction about the SQL vs. other SQL you might write is that id, source, target, cost are columns. You can add more columns and extra columns will be ignored and can use any WHERE clause you like. You can even use fancy things like CTEs and functions

For example:

SELECT * FROM pgr_Dijkstra(
  $sql$SELECT id, source, target, cost, reverse_cost FROM edges
WHERE subgraph = 'train' $sql$,
  6, ARRAY[10, 17]);

I also like using PostgreSQL dollar quoting if my where clause has strings. That way you don’t have to escape for example ‘train’.

The above could also be written without dollar quoting as follows:

SELECT * FROM pgr_Dijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM edges
WHERE subgraph = ''train'' ',
  6, ARRAY[10, 17]);
1 Like
3

pgr_dijstra just takes an SQL query. The only distinction about the SQL vs. other SQL you might write is that id, source, target, cost are columns. You can add more columns and extra columns will be ignored and can use any WHERE clause you like. You can even use fancy things like CTEs and functions

For example:

SELECT * FROM pgr_Dijkstra(

  $sql$SELECT id, source, target, cost, reverse_cost FROM edges

WHERE subgraph = 'train' $sql$,

  6, ARRAY[10, 17]);

I also like using PostgreSQL dollar quoting if my where clause has strings. That way you don’t have to escape for example ‘train’.

The above could also be written without dollar quoting as follows:

SELECT * FROM pgr_Dijkstra(

  'SELECT id, source, target, cost, reverse_cost FROM edges

WHERE subgraph = ''train'' ',

  6, ARRAY[10, 17]);

From: Pgrouting-users <pgrouting-users-bounces@lists.osgeo.org> On Behalf Of Hugh Kelley via Pgrouting-users
Sent: Thursday, June 6, 2024 11:37 AM
To: pgRouting users mailing list <pgrouting-users@lists.osgeo.org>
Subject: [pgrouting-users] compare routes between full network and subset

Hi,

I am looking to compare the directness of routes between the entirety of a network and a subset of the same network.

I see in pgr_createTopology that there is a rows_where argument to filter the network.

When I use pgr_dijkstra to calculate routes between node pairs, if I've created two separate topologies, one with the full network and another with the subset using "rows_where". Can i somehow specify which topology the dijkstra algorithm should use?

It occurred to me that I could just include a where clause in the "edges sql" that I provide pgr_dijkstra but I can't find an example of that being done anywhere.

In previous work I've simply made two tables, network and network_subset and then just created topologies for each and calculated routes for each network separately but it seems like I'm just not seeing exactly how to do the same thing in a simpler manner with the "rows_where" argument or some other option.

Thanks for any solutions or suggestions!

Hugh

1 Like