3

Here is my code:

SELECT cluster_id, st_centroid(st_union(points_geom)) as cluster_centroid
FROM clustering 
GROUP by cluster_id

Should I replace st_union with st_collect? Because the coordinates for centroids I am getting with my current code do not match the expected results. This could be for a variety of reasons, but I want to see if my wrong usage of st_union could be a contributing factor.

3
  • Why are you asking? Sep 29, 2022 at 11:38
  • Because the coordinates for centroids I am getting with my current code do not match the expected results.
    – analyst92
    Sep 29, 2022 at 11:39
  • 3
    Please add that to your question and include what results you expect. Only you know that and unless you ask specifically, you won't be able to get an answer that fits your problem ;) Sep 29, 2022 at 11:40

2 Answers 2

4

ST_Collect aggregates input sets into the minimally possible higher dimensional representation (either a MULTI geometry or a GEOMETRYCOLLECTION) without changing the structure of the input geometries. This is a comparably fast operation.

ST_Union is far more complex as it attempts to remove overlaps and repeating vertices from the geometric input structures - thus the output may be heavily altered, and the vertex count reduced significantly. This is a comparably costly operation.

Now, ST_Centroid calculates the arithmetric mean of all vertices of the input geometry; any overlap in an ST_Collection adds bias to the arithmetic mean, compared to its ST_Union.

Example:

WITH
  vals(geom) AS (
    VALUES
        -- geometries have overlaps - this is a possible result of ST_Collect
        ('SRID=4326;MULTIPOINT((0 0), (1 0), (1 0), (1 0), (2 0), (3 0), (4 0), (5 0))'),
        ('SRID=4326;MULTILINESTRING((0 0, 1 0, 2 0), (1 0, 2 0, 3 0, 4 0, 5 0))')
  )
SELECT
  ST_AsText(ST_Centroid(geom)) AS "ST_Collect",
  ST_AsText(ST_Centroid(ST_Union(geom))) AS "ST_Union"
FROM
  vals
GROUP BY
  geom
;


         ST_Collect         |   ST_Union   
----------------------------+--------------
 POINT(2.125 0)             | POINT(2.5 0)
 POINT(2.333333333333334 0) | POINT(2.5 0)

In other words, if you are certain to have no overlaps both functions will lead to the same ST_Centroid (and then ST_Collect is to be preferred for performance reason) - but for input geometries with overlaps, the ST_Centroid will differ!

3
  • 1
    I wouldn't say coincident points were "adding bias" - if you have 10 events at the same location and one event at another location, you probably want the centroid to be closer to the 10 events than the one event.
    – Spacedman
    Sep 29, 2022 at 13:09
  • 1
    @Spacedman Hm, but I also said "compared to its ST_Union" - where then I think "bias" makes some sense, no?
    – geozelot
    Sep 29, 2022 at 13:19
  • I'm probably using "bias" in the statistical sense of a systematic error in an estimation, I guess its not likely to be misinterpreted here.
    – Spacedman
    Sep 29, 2022 at 19:32
3

st_union will merge coincident points, st_collect will preserve them. So a centroid computed with st_collect will give more weight to coincident points, with st_union any location with coincident points gets the same weight as a single point at that location.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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