3

I have a road network with 8.5 million edges but when I run pgr_createTopology it processes edges at a rate of about 1000 edges per second up until 360,000 edges where it slows down and eventually fails with the following error.

server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

I am running postgres 10.1 postgis 2.4 pgrouting 2.5. What would like cause it to fail like this? Or is there any way that I could process the network in 100,000 edge chunks?

6

The following is what I am using. Some of it is specific to our deployment environment since we are using docker and some bash scripts to deploy and set up the server. You could easily get rid of all the argeparse/os.getenv and hardcode the connection if you wanted.

import argparse
from os import getenv
import psycopg2

parser = argparse.ArgumentParser()
parser.add_argument("-H", "--host", help="host location of postgres database", type=str)
parser.add_argument("-U", "--user", help="username to connect to the database", type=str)
parser.add_argument("-d", "--dbname", help="database name", type=str)
parser.add_argument("-p", "--port", help="port to connect to postgres", type=str)
args = parser.parse_args()
password = getenv('POSTGRES_PASSWORD')

conn = psycopg2.connect(
    f"dbname={args.dbname} user={args.user} host={args.host} port={args.port} password={password}"
)
cur = conn.cursor()
print("connected to database")

cur.execute("SELECT MIN(id), MAX(id) FROM ways;")
min_id, max_id = cur.fetchone()
print(f"there are {max_id - min_id + 1} edges to be processed")
cur.close()

interval = 200000
for x in range(min_id, max_id+1, interval):
    cur = conn.cursor()
    cur.execute(
    f"select pgr_createTopology('ways', 0.000001, 'the_geom', 'gid', rows_where:='id>={x} and id<{x+interval}');"
)
    conn.commit()
    x_max = x + interval - 1
    if x_max > max_id:
        x_max = max_id
    print(f"edges {x} - {x_max} have be processed")

cur = conn.cursor()
cur.execute("""ALTER TABLE ways_vertices_pgr
  ADD COLUMN IF NOT EXISTS lat float8,
  ADD COLUMN IF NOT EXISTS lon float8;""")

cur.execute("""UPDATE ways_vertices_pgr
  SET lat = ST_Y(the_geom),
      lon = ST_X(the_geom);""")

conn.commit()
  • Works like a charm! Although I cranked down the interval to 10000 for acceptable performance. But that depends on your rig. – LMB Apr 13 '18 at 10:53
  • Hi ! I was having the exact same problem. a table with 600k geometries on which select pgr_topology did slow down then crash. Your solutions looks like it would help. However, when i put a try except on the for loop, this is what I get on the final loop : ERROR : column "geom" doesn't exist" LINE 2 : SET lat = ST_Y(geom) Would you have any piece of advice ? thanks a lot ! – Vincent Monteil Apr 26 '18 at 13:04
  • @VincentMonteil is the geometry column for your edges named something like the_geom instead of geom? – James Apr 27 '18 at 13:59
  • Hi @James It is named "geom", which is why I rewrote the code this way. Anyway, I tried this piece of code on another machine (with the same tables) and it worked perfectly. Your solution was very helpful to me ! Thanks a lot :) – Vincent Monteil May 3 '18 at 7:08
  • Hi there. Could anyone please provide me with some workable code that could be used. I not an engineer so I don't know what part I can get rid off or how to get it work. Any help welcome! – hoge6b01 Nov 18 '18 at 0:00
3

Thank you @James for sharing this. It helped a lot.

For those of you who want to rebuild the whole topology: normally, this is achieved by clean:=true.

Since the ways table gets processed step by step (in our case every 200.000 rows) you cannot use the clean flag here because this would reset source and target columns and truncate the vertices table over and over.

See https://github.com/pgRouting/pgrouting/blob/master/sql/topology/pgrouting_topology.sql for more information about clean flag.

Therefore you can reset them manually (modify the schema geo. according to your DB):

UPDATE geo.ways SET source = NULL, target = NULL;
TRUNCATE TABLE geo.ways_vertices_pgr RESTART IDENTITY;

After that, you can use the script provided by @James.

1

Very helpful. Thanks!

For those who may be confused about the password, at the shell before running the code, enter

POSTGRES_PASSWORD=mypassword

substituting the string mypassword with the PostgreSQL password for your user, which creates the environment variable obtained by the getenv line in the script.

Also on CentOS 7 I needed to install python3.6. As root:

yum -y install python36
curl -O https://bootstrap.pypa.io/get-pip.py
python3.6 get-pip.py
pip3 install psycopg2
pip3 install psycopg2-binary

To run with python 3.6:

python36 topology.py -H myhostname -U myusername -d mydbname -p myportnum

substituting myhostname, myusername, mydbname, and myportnum as necessary.

1

I believe that this could be an issue with PostgreSQL 10.

I did the follow tests:

I have also been using pgRouting with a dataset of ~500,000 ways. I have tested it on two different systems:

  • PC #1 (problem PC): Windows 10, 12 GB of RAM, ~20GB free HDD, PostgreSQL 10.1, PostGIS 2.4.2, pgRouting 2.5.2
  • PC #2 (working PC): Windows 10, 8 GB of RAM, ~8GB free HDD, PostgreSQL 9.6, PostGIS 2.5.0, pgRouting 2.6.1

On the problem PC, pgr_createTopology() would get to 90k records processed fairly quickly, but then it would slow to a crawl and eventually run out of memory. I watched Task Manager while this was happening, and the PostgreSQL process' memory usage was constant at between 10-15MB until it hit that 90k mark, and then started to increase rapidly.

On the working PC, pgr_createTopology() finished all~500k ways in one hit and memory usage remained at around 10-15MB throughout.

Thinking that it may have been a problem with the version of pgRouting and/or PostGIS, I upgraded them both on the problem PC to 2.5.0 and 2.6.1. It still seems to have the same issue.

Based on this, I'm guessing it's something unique about the pgRouting implementation on PG 10 that causes this issue. Fortunately the incremental approach is a good workaround, but it is interesting that the problem occurs.

0

I had the same problem using PostgreSQL 11.6 + PostGIS 2.5 + pgRouting 3.0.0 beta on an Ubuntu VPS.

Updating PostGIS to version 3.0.0 fixed the issue.

New contributor
user1544329 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

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.