pgr_extractVertices to Proposed #2087
Description
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 :-)