2

I have the large roads network (urban walking paths). I'm trying create topology in my database but when query reaches 175 thousands of edges, begins to increase memory usage. In the same time CPU usage is decreasing. After some time, I get an error:

"Connection to the server has been lost."

I read these topics:

  1. pgr_createTopology() 'Connection to the server has been lost'

  2. pgr_createTopology with large datasets

But the first has not been explained and in the second - I do not know Python.

I tried to upgrade and downgrade PgAdmin Version. Nothing works.

I also tried to simplify the geometry. Unfortunately, it didn't help either.

I use this query:

select pgr_createTopology ('roads', 0.0001, 'geom','id');

PgAdmin Version 4.13 with PostGIS 2.5.2 and pgRouting 2.6.2.

I have 8GB RAM.

1

This problem is a very common one when using huge graphs with pgrouting and a limited amount of RAM.

One solution (which may be automated with Python - but not necessarily) would be to use the rows_where parameter that is available in the function (see documentation at Selecting rows using rows_where parameter).

With this parameter you can split up the topology creation process into multiple queries which can be run consecutively avoiding the problem of insufficient RAM. For example, a workflow for a dataset with one million rows could be split up into two (or more queries):

SELECT  pgr_createTopology('mytable', 0.001, 'mygeom', 'gid', 'src', 'tgt', rows_where:='gid < 5000000');
SELECT  pgr_createTopology('mytable', 0.001, 'mygeom', 'gid', 'src', 'tgt', rows_where:='gid >= 5000000');
  • Wouldn't this cause a problem with connectivity of edges with edges from another set? – wfgeo Oct 3 at 13:17
  • No it does not because the function is respecting existing topology information. Just test it with the pgrouting test network. – root676 Oct 3 at 13:25
  • It works! Thanks for your idea. – Michal De Oct 3 at 15:09

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.