pgr_nodeNetwork deprecation on v3.8.0

[cvvergara]

pgr_nodeNetwork has many open issues:

• pgr_nodeNetwork returns old_id as int and not bigint #2628
• pgr_nodenetwork on very long lines misses some nodes #1882
• pgr_nodeNetwork bug #692
• Orthogonal lines to road network are not noded with pgr_nodeNetwork #623
• ’pgr_nodeNetwork' sub_id starts from other number rather than 1 #540
• switched id and geom in notice by pgr_nodeNetwork #423
• pgr_nodeNetwork #419
• pgr_nodeNetwork, change to support views #330
• pgr_nodeNetwork does not correctly split line segments #280
• Couple of bugs after pgr_nodeNetwork applying #240

What I can see:

• Creates a table based on the table name: edges_noded
  • only control of the table name is the suffix
• User does not have control on the columns and types of those columns that are created:
  • Columns in table: id, old_id, sub_id, source, target
  • Potentially missing columns: cost, reverse_cost, length, capacity, reverse_capacity, tags from OSM, etc.
    • The columns that are needed depend on the application that is been developed
• If the user does not have the rights to create a table the function is futile
• The code involves checks on edges table
  • if the table exists or not
  • If it has the columns (id, the_geom) mentioned in the parameters
    • if it has or not indexes: creates indexes in case of not having
    • BTW the_geom is a deprecated name in PostGIS
  • If the SRID of the geometry can be determined
• About the edges_noded
• If it does not exists it creates it with fixed columns: id, old_id, sub_id, source, target
  • Creates index with predetermined names.
• If it exists, it truncates the table (and all work done before is lost)
  • Drops and recreates the_geom column
• It still checks if PostGIS is 2.1 or less
• To build the queries it does not use the more modern PostgreSQL format function
• The result is an OK or FAIL text message
• Very difficult to handle the rows_where parameter
  • User is unable to test before hand. Or after testing needs to rewrite in terms of parameters

Never the less it is a useful function when there are problematic segments in the graph.

The deprecation should be done in steps as follows:

Step 1

New utility functions:

• Creation of pgr_separateCrossing(edges SQL, tolerance, dryrun) - Breaks the edges that cross each other
• Creation of pgr_separateTouching(edges SQL, tolerance, dryrun) - breaks the edges that touch each other or the dead end is very close to an edge

Example execution (using the Sample Data of the documentation):
pgr_separateCrossing

SELECT seq, id, sub_id, ST_AsText(geom)
FROM pgr_separateCrossing('SELECT id, geom FROM edges');
 seq | id | sub_id |         st_astext         
-----+----+--------+---------------------------
   1 | 13 |      1 | LINESTRING(3 3,3.5 3)
   2 | 13 |      2 | LINESTRING(3.5 3,4 3)
   3 | 18 |      1 | LINESTRING(3.5 2.3,3.5 3)
   4 | 18 |      2 | LINESTRING(3.5 3,3.5 4)
(4 rows)

pgr_separateTouching

SELECT seq, id, sub_id, ST_AsText(geom)
FROM pgr_separateTouching('SELECT id, geom FROM edges');
 seq | id | sub_id |             st_astext              
-----+----+--------+------------------------------------
   1 | 14 |      1 | LINESTRING(2 3,1.999999999999 3.5)
   2 | 14 |      2 | LINESTRING(1.999999999999 3.5,2 4)
(2 rows)

Assuming the edges table already has:

• A columns: old_id
• Columns as needed from the application
• indexes as needed from the application
• etc

The user, for example can add the new segments to the edges table.

INSERT INTO edges (old_id, geom)
SELECT id, geom
FROM pgr_separateCrossing('SELECT id, geom FROM edges');

The rest, like updating the new segments 's costs, source, and target columns, tags, etc will depend on the application requirements.

Step 2

Rewrite pgr_nodeNetwork

• Internally building the SQL query
• Instead of checking all the little details:
  • Create a query based on the information
  • Do not check for indexes and/or creating indexes on edges and/or edges_noded
  • Not dropping the geometry column of edges_noded when the table exists
  • Use the pgr_separateCrossing and pgr_separateTouching internally
  • All existing pgtap tests should pass
  • Add new pgtap tests based on the open issues

Step 3

Deprecate pgr_nodeNetwork on v3.8.0

• The migration is to use pgr_separateCrossing and pgr_separateTouching

Step 4

On v4.0.0 remove the pgr_nodeNetwork code