Timeline for Grouping connected linestrings in PostGIS?
Current License: CC BY-SA 3.0
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 20, 2018 at 22:17 | vote | accept | dbaston | ||
| Oct 13, 2017 at 10:10 | comment | added | zakaria mouqcit | This is a good solution, so now how can we store the geometries in a table so that we can see the connected lines? | |
| Apr 26, 2014 at 13:26 | comment | added | dbaston |
Adding this to the test data will cause duplicate IDs in the grouplist array: insert into lines (id, geom) values ( 15, 'LINESTRING(0 0, 10 10)');. Changing array_agg(id) in the function return to array_agg(DISTINCT id) seems to resolve the issue.
|
|
| Apr 26, 2014 at 13:05 | comment | added | dbaston | This is a really awesome approach. I'm noticing some odd results as I apply it to a larger test set; I'll see if I can reduce the issue to a simple example. 100 lines: 85 clusters, largest cluster=3, 0.03 s //// 200 lines: 144 clusters, largest cluster=9, 0.08 s //// 300 lines: 180 clusters, largest cluster=51, 0.16 s //// 400 lines: 188 clusters, largest cluster=41, 0.27 s //// 500 lines: 176 clusters, largest cluster=112, 0.56 s //// 600 lines: 143 clusters, largest cluster=449, 1.0 s //// 650 lines: 133 clusters, largest cluster=7601, 6.8 s | |
| Apr 25, 2014 at 23:41 | comment | added | Paul Ramsey | I think this code could be simpler, if the geometry type supporting hashing in PostgreSQL (when you write a simpler RCTE that doesn't involve accumulating arrays of ids, you get an error "All column datatypes must be hashable"), so there's a little enhancement request for me. | |
| Apr 25, 2014 at 23:31 | history | answered | Paul Ramsey | CC BY-SA 3.0 |