pgr_createTopology with large datasets

Question

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?

Answer 1

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 been 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()

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

I find solution after few days :) Sorry for my english --'

public.pgr_createtopology call the function public._pgr_pointToId and this is the one that takes too long to complete when the vertice_pgr tale is full enough (~15 MB) on my server. I improved this one and EUREKA it works! I create this:

CREATE OR REPLACE FUNCTION public._pgr_pointtoid2(point geometry, tolerance double precision, vertname text, srid integer)
 RETURNS bigint
 LANGUAGE plpgsql
 STRICT
AS $function$
DECLARE
    rec record;
    pid bigint;

BEGIN
    EXECUTE '
    with w1 as ( 
    select ST_GeomFromText(ST_AsText(' || quote_literal(point::text) ||'),' || srid || ') as geom
    
)
SELECT id,ST_Distance(the_geom,geom) as d
    FROM '||_pgr_quote_ident(vertname)||'
    inner join w1 on ST_DWithin(w1.geom,the_geom,' || tolerance||')
    ORDER BY d
    LIMIT 1' INTO rec ;
    IF rec.id IS NOT NULL THEN
        pid := rec.id;
    ELSE
        execute 'INSERT INTO '||_pgr_quote_ident(vertname)||' (the_geom) VALUES ('||quote_literal(point::text)||')';
        pid := lastval();
END IF;

RETURN pid;

END;
$function$
;

AND i change the public.pgr_createtopology at line 208. Now i call the new function.

source_id := _pgr_pointToId2(points.source, tolerance,vertname,srid);
target_id := _pgr_pointToId2(points.target, tolerance,vertname,srid);