25

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) ));
Brad Koch
  • 475
  • 5
  • 23
Rémi B.
  • 1,103
  • 2
  • 10
  • 12

3 Answers3

25

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.

Sergio
  • 1,097
  • 9
  • 17
20

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.

dbaston
  • 13,048
  • 3
  • 49
  • 81
8

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.

gemo1011
  • 115
  • 2
  • 6