I am fairly new to all of this but in simplistic terms I have the following problem: I need to create lines from points, and from said points group all of those within n distance of one another. From this grouping I need to return the concave hull nice and tight. This bit is easy but I need to retain an understanding of the attribution of the points I originally manipulated.

In the table I have the following:

point_1, point_2, ID_1, ID_2

from this I have designed the query for the aggregation as follows:

            ST_MakeLine(table.point_1, table.point_2),0.00010008999
) AS aggr
FROM tb1 AS table

This appears to meet my requirements however how could I also return a list of all ID_1's and ID_2's whose geometries have gone through the aggregation process for example:

aggr            |    ID_1s    |    ID_2's    |

POLYGON(123...) | 1,2,3,4,5.. |  66,33,45...
POLYGON(566...) | 9,3,5,0,2.. |  21,53,65...

I have struggled to ask the question almost as much as I have to solutionize my problem (without success).


Use ST_ClusterDBSCAN (follow the provided links for further details on the algorithm and the impact of the function arguments); as a Window function, it operates on the input rows within a defined frame, where it assigns cluster ids as column values:

        ARRAY_AGG("ID_2") AS "ID_2's",
        ) AS aggr
FROM    (
    SELECT  "ID_1", "ID_2",   -- identifiers are lower-case unless wrapped with ""
            ST_ClusterDBSCAN(line, 0.0001, 1) OVER() AS _clst,
    FROM    <your_table>,
            LATERAL ST_MakeLine(point_1, point_2) AS line
) q

A note on your calculated distance value:

Unless you are working with a nano tube mesh here, a precision of 6 decimal places is absolutely sufficient, and I even rounded it up to only 4 here; read one of the boards most prominent answers.

  • Wow, I would not have come up with this given the time I have given myself thank you. I am testing it through now, there are some mildly erroneous results in comparison to my previous query but that issue is likely related to the data. I will test and feed back. Thank very much! – Phish Dec 2 '20 at 13:26
  • thank you. I believe that the distance and min points are the wrong way round in ST_ClusterDBScan. – Phish Dec 2 '20 at 13:47
  • @Phish oooh, cr**, you are right, my mistake. I corrected that. – geozelot Dec 2 '20 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.