pgr_createTopology uses too much memory and "connection to the server has been lost"

Question

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.

Answer 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');