How to get the centroid of a set of points in PostGIS?

Question

I use PostgreSQL with the PostGIS extension.

I have a set of points in the_geom column from a table myschema.myobjects. I want to create a select statement to get the centroid of this cluster, so from a select statement like this:

SELECT the_geom FROM myschema.myobjects

I need to find the correct syntax for a statement like:

SELECT ST_AsText(ST_Centroid( (SELECT the_geom FROM myshema.myobjects) ));

Answer 1

you have to use the union function like this

SELECT att1, st_centroid(st_union(geom)) as geom
FROM schema.table
GROUP BY att1;

so you can obtain centroid of point that have same attribute.

Answer 2

PostGIS has two functions for combining multiple geometries into a single geometry that you can use as an input to ST_Centroid.

ST_Collect simply combines a set of geometries without modifying them.

The alternative,ST_Union, will "dissolve" multiple geometries and remove redundant components. This is probably not what you want for this application.

To see the difference, compare:

 SELECT ST_AsText(ST_Centroid(ST_Union(geom))) FROM 
 (VALUES ('POINT (0 0)'), ('POINT (2 2)'), ('POINT (2 2)')) sq (geom);

 -- POINT(1 1)

and

 SELECT ST_AsText(ST_Centroid(ST_Collect(geom))) FROM 
 (VALUES ('POINT (0 0)'), ('POINT (2 2)'), ('POINT (2 2)')) sq (geom);

 -- POINT(1.33333333333333 1.33333333333333)

In this case, ST_Union has removed the duplicated point, while ST_Collect has retained it.

Answer 3

If you're looking for performance use this query:

SELECT avg(ST_X(the_geom)) as lon, avg(ST_Y(the_geom)) as lat FROM table

it gives you more or less the same output as:

SELECT ST_AsText(ST_Centroid(ST_Collect(the_geom))) FROM table

like in @dbaston answer, but is faster and doesn't use as much memory.