2

Let's assume a table of connected linestrings that look something like a street network with longer corridors composed of individual features broken at intersections:

       A     B

       |    /
       |   /
o------o--o--o    C
       | /
       |/
o------o-----o    D
      /|
     / |

I want to create an aggregate function make_corridorthat would allow me to group all features into their respective "corridors". In the example above, the corridors are labeled A-D and can be thought of as a group of connected linestrings where each component line is roughly parallel to its neighbor.

The function should allow for a query structure like:

SELECT make_corridor(id, geom, 30) AS corr_ids
FROM   mytable

and return an array of feature ids that make up each corridor

------------------
|row| corr_ids  |
------------------
| 1 | {1,5,10,15} |      <- corridor A
| 2 | {2,6,11,16} |      <- corridor B
| 3 | {3,7,12,17} |      <- corridor C
etc...

I came across a set of recursive queries that I can adapt to identify the corridor, but I'm confused about how to translate the recursive queries into a state transition function and final aggregate function. Can someone point me to an example of incorporating a recursive query into an aggregate function, or suggest some strategies to consider?

spencerrecneps
  • 1,888
  • 1
  • 17
  • 22
  • check out st_clusterwithin (or st_clusterintersecting). not sure if I get this right, but if your linesrtrings have unique identifier for their respective corridors, the cluster function family could be a good core for yours. – geozelot Oct 12 '17 at 17:24

0 Answers0