# using st_collect vs st_union to calculate centroids

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.

• 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. Sep 29, 2022 at 11:39
• 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

`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_Collect`ion 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!

• 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. Sep 29, 2022 at 13:09
• @Spacedman Hm, but I also said "compared to its `ST_Union`" - where then I think "bias" makes some sense, no? 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. Sep 29, 2022 at 19:32

`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.