1

I am trying to find the shortest path between buildings and any electric plants on a network.

The thing is, when I use the following query, I overload my RAM (network_nodes and network_edges each cointains a bit more than 120 000 rows and 5 columns) and therefore do not get any results.

SELECT start_vid as building, end_vid as plant, agg_cost as junction_length from pgr_dijkstraCost
(
'SELECT gid as id, i as source , j as target , length as cost from   network_edges'
, array(SELECT id from "network_nodes" where buildingID IS NOT NULL)
, array(SELECT id from "network_nodes" where plant_name IS NOT NULL)
, false
)

To avoid that, I thought of trying to use pgr_dijkstraCost() One-to-Many instead. In order to do it, I created a table with 2 columns and as many rows as there are buildings nodes in my network_nodes table (around 85 000). The first column contains the ID of each building and the second contains an array of ids which are the closest plants to the corresponding building.

Now, my problem is : how to write a query which repeat pgr_dijkstraCost() One-to-Many using each time as start_vid the first column of this table (integer) and as end_vid the second columns (array of integers) ?

I suspect that I'll have to do some kind of loop but I do not know how to do that in SQL.

1 Answer 1

1

I wouldn't use a loop because you'll still have the issue that any plpgsql you write has to commit as a transaction so you'd probably still run out of memorty.

However, psql has a nice feature called \watch since PostgreSQL 9.3.

https://www.depesz.com/2013/04/07/waiting-for-9-3-add-watch-sec-command-to-psql/

that allows you to repeat a query over and over again.

You could use it to run things in sequence and commit on each run. I use it a lot for things like this.

First thing you want to do, is create a table to hold the answers. I'll assume you have done this and the table is called bp_dijkstra and has fields building, plant, junction_length.

Note this is a revision to my original answer This version will always return at least one record for building even if no path and set the solution to -1 cost. It does so using LEFT JOIN LATERAL that will guarantee at least one record per building even if pgr_dijikstra returns no answers.

then in psql run this:

WITH next_building AS (SELECT id from network_nodes 
     WHERE buildingID IS NOT NULL 
        AND id NOT IN(SELECT building FROM bp_dijkstra) 
   LIMIT 1 )
INSERT INTO bp_dijkstra(building, plant,junction_length)
SELECT next_building.id as building, end_vid as plant, 
     COALESCE(agg_cost,-1) as junction_length 
FROM next_building LEFT JOIN LATERAL pgr_dijkstraCost
    (
    'SELECT gid as id, i as source , j as target, length as cost from  network_edges'
    , next_building.id
    , array(SELECT id from network_nodes where plant_name IS NOT NULL)
    , false
    ) ON true \watch 1
4
  • Thanks for helping. I think it could work but without the array on buildings and rather a LIMIT 1 in order not to run out of memory.
    – Florian L.
    Commented Apr 26, 2017 at 8:33
  • I'm facing another problem that I did not expect. I made the query with success but the thing is, when the source and the target are the same (I have many of these in my network) pgr_dijkstra() returns an empty set. So it run into infinite loop with the condition NOT IN...
    – Florian L.
    Commented Apr 26, 2017 at 9:25
  • Same thing when there is no path between to points. I would have expected something like a cost of -1 but it is not the case.
    – Florian L.
    Commented Apr 26, 2017 at 13:28
  • ah yes. Sorry forgot about the limit clause. I'll revise my answer to handle the case when empty results
    – Regina Obe
    Commented Apr 26, 2017 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.