PostGIS – Rows with NULL Geometry or Null After Running PGR_CreateTopology

pgroutingpostgis

After running the code below on a line shapefile that I imported into my postgis database it tells me I have many rows with NULL geometry. I think this might be related to the split with lines operation I performed on it in QGIS before importing. How can this be fixed so that I can run my pgr_Dijkstra properly without error.

SELECT pgr_createTopology('streams', 0.00001, 'geom', 'gid');

NOTICE:  PROCESSING:
NOTICE:  pgr_createTopology('streams', 1e-05, 'geom', 'gid', 'source', 'target', rows_where := 'true', clean := f)
NOTICE:  Performing checks, please wait .....
NOTICE:  Creating Topology, Please wait...
NOTICE:  1000 edges processed
NOTICE:  -------------> TOPOLOGY CREATED FOR  1944 edges
NOTICE:  Rows with NULL geometry or NULL id: 5740
NOTICE:  Vertices table for table public.streams is: public.streams_vertices_pgr
NOTICE:  ----------------------------------------------

Successfully run. Total query runtime: 934 msec.
1 rows affected.

Best Answer

The problem was caused by using the Fix Geometries tool in QGIS after performing the Split with lines operation. It appears the Fix Geometries tool removes the newly formed geometries created by the Split with lines tool causing those edges to have NULL geometries.

Related Solutions

[GIS] pgr_createTopology with large datasets

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

Best Answer

Related Solutions

[GIS] pgr_createTopology with large datasets

Related Question