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.


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;
| improve this answer | |
  • 1
    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 Jan 30 '18 at 13:41

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.