15

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) ));
19

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.

6

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.

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.

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.