Problem creating topology on pgrouting

[rmarzocchi84]

I import my OSM data using osm2pgrouting and then I try to create topology with pgrouting topology functions:

1)pgr_createTopology works correctly

SELECT pgr_createTopology('network.ways', 0.001, 'the_geom', 'gid');

2. pgr_createVerticesTable fails

SELECT  pgr_createVerticesTable('network.ways');

Here the error:

PROCESSING:
pgr_createVerticesTable('network.ways','the_geom','source','target','true')
Performing checks, please wait .....
Got cannot truncate a table referenced in a foreign key constraint
ERROR: Initializing vertex table
select count(*) from network.ways WHERE (the_geom IS NULL or source is null or target is null)=true  AND (true)

Platform/versions

• PostgreSQL 13.7 (Ubuntu 13.7-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

• POSTGIS="3.2.1 5fae8e5" [EXTENSION] PGSQL="130" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

• PgRouting Version 3.3.0

[cvvergara]

Recommended reading:

• https://github.com/pgRouting/pgrouting/discussions/2255
• https://docs.pgrouting.org/dev/en/pgr_extractVertices.html#create-a-routing-topology Using this method of creating the topology the user in in charge of creating appropriate indexes to the tables. Please if this new method works for you add a comment here: https://github.com/pgRouting/pgrouting/issues/2308

[rmarzocchi84]

I try to follow the instruction in the https://docs.pgrouting.org/dev/en/pgr_extractVertices.html#create-a-routing-topology page:

NOTES:

1. to drop the vertices table imported with osm2pgrouting DROP TABLE IF EXISTS vertices_table; I need to drop constrains:

ALTER TABLE edge_table DROP CONSTRAINT ways_target_fkey;
ALTER TABLE edge_table DROP CONSTRAINT ways_target_osm_fkey;
ALTER TABLE edge_table DROP CONSTRAINT ways_source_fkey;
ALTER TABLE edge_table DROP CONSTRAINT ways_source_osm_fkey;

2. --Create the vertices table seems to works not correctly: Updated Rows 34701 (not selected !!)

In fact

SELECT  * FROM pgr_extractVertices(
  'SELECT gid, the_geom AS geom FROM edge_table',
  dryrun => true); 

extract 0 row

and

select * from vertices_table;

the result is the following:

in_edges and out_edges are null

Finally

WITH
out_going AS (
  SELECT id AS vid, unnest(out_edges) AS eid, x, y
  FROM vertices_table
)
UPDATE edge_table
SET source = vid, x1 = x, y1 = y
FROM out_going WHERE gid = eid;

--> Updated Rows 0