I have a table of point geometries like pointID, village and geom. I want to group the point by village and create a centeroid representing the village.

How do I write that query in PostgreSQL?

I am using PostgreSQL 9.6 with PostGIS 2.2.

1 Answer 1


An easy way would be to collect (group them) all the points having the same village name attribute, create the polygons that enclose each group, then return the centroid of that polygon. For example:

SELECT village, ST_Centroid(ST_ConvexHull(ST_Collect(geom))) AS geom FROM your_table GROUP BY village;
  • 2
    I'd suggest omitting the ST_ConvexHull piece, which makes this solution very sensitive to outliers, and considering ST_GeometricMedian as an alternative to ST_Centroid.
    – dbaston
    Commented Jan 30, 2018 at 13:41

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.