pgr_extractVertices to Proposed

[TimMcCauley]

Lately I have been using the createTopology quite heavily and noticed that you have implemented a newer approach using extractVertices to create the topology. The differences in performance are phenomenal which I wanted to share here.

Using PostGIS v.3.2.1 my input is a table consisting of approx 60k linestrings.

gis=# \d public.ls_test
          Table "public.ls_test"
 Column |       Type       | Collation | Nullable | Default
--------+------------------+-----------+----------+---------
 id     | integer          |           |          |
 geom   | geometry         |           |          |
 gid    | integer          |           |          |
 source | integer          |           |          |
 target | integer          |           |          |
 x1     | double precision |           |          |
 y1     | double precision |           |          |
 x2     | double precision |           |          |
 y2     | double precision |           |          |
Indexes:
    "ls_test_geom_idx" gist (geom)
    "ls_test_source_idx" btree (source)
    "ls_test_target_idx" btree (target)
    "gid_idx" btree (gid)

createTopology

NOTICE:  -------------> TOPOLOGY CREATED FOR  62426 edges
NOTICE:  Rows with NULL geometry or NULL id: 0
NOTICE:  Vertices table for table public.depth_intersections_test is: public.depth_intersections_test_vertices_pgr
NOTICE:  ----------------------------------------------
 pgr_createtopology
--------------------
 OK
(1 row)

Time: 68428.509 ms (01:08.429)

extractVertices (as described here)

UPDATE 62426
Time: 1849.968 ms (00:01.850)

I have validated the results which are correct; I am not sure how much impact the tolerance factor has in the createTopology function, for my use case it actually isn't required as I already know that the vertices lie on top of each other that should be treated as a vertex - @cvvergara @dkastl I recommend highlighting this approach for the next major version :-)

[cvvergara]

@TimMcCauley
Thanks for testing on a large data set.
1 hour vs 1 minute its quite a difference.
Right now its experimental, and can be moved up to proposed on version 3.3
and moved to official on version 4.0.

[TimMcCauley]

@cvvergara it is 1 minute vs 1 second, even better (for both approaches :))

[cvvergara]

I need glasses :-)