When running the pgr_dijkstraCostMatrix() on a PostGIS database with 41000 id's I get only 144000 rows in the matrix. I would have expected much more. What am I missing?

I parsed the OSM data with Osm2po, getting these fields:


The name of my db: ma_2po_4pgr

The SQL I used:

SELECT * FROM pgr_dijkstraCostMatrix(
    'SELECT id,  source, target, cost, reverse_cost FROM ma_2po_4pgr',
    (SELECT array_agg(id) FROM  ma_2po_4pgr )

The data looks like this;

The data

I am on a win7 machine, using PostGIS 2.5.2 on PostgreSQL 11, pgRouting 2.6.2 .

  • is your db named ma_2po_4pgr or your edges table? additionally, do you have a table with your nodes? – vagvaf Jul 17 '19 at 11:19
  • The ma_2po_4pgr is my edges table. I didn't have the nodes/vertices. But Osm2PO can create that, as I now have learned. THanks! – Maas Jul 21 '19 at 13:45

If i'm correct you are trying to get the distance from each node in the network to each other node, correct?

enter image description here

(Image source)

Your subquery (SELECT array_agg(id) FROM ma_2po_4pgr ) returns each unique edge in the network, while'you want this query to return each unique node. To get all the unique nodes you have to select them form the source and target columns. You can do this via SELECT DISTINCT((SELECT DISTINCT source FROM ma_2po_4pgr) UNION (SELECT DISTINCT target FROM ma_2po_4pgr))

You have 41000 ID's, but this does not mean that you expect 41000**2 results, as this table is an edge table, not a node table. One node might be connected to multiple edges. You would expect the amount equal to COUNT('subquery with the union from previous paragraph') squared. In other words, from each node to each other node.

So your total query will be like this (not tested):

SELECT * FROM pgr_dijkstraCostMatrix(
    'SELECT id,  source, target, cost, reverse_cost FROM ma_2po_4pgr',
    (SELECT array_agg(distinct nodes.id) from 
         (SELECT DISTINCT source as id FROM ma_2po_4pgr)
         (SELECT DISTINCT target as id FROM ma_2po_4pgr)
       ) as nodes
  • Thanks! In de mean while, I discovered that OSM2PO also is able to supply the vertices table. In config I can add that table. – Maas Jul 21 '19 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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