2

I have a table like this:

id|      shape [geometry]     |  centroid [geomety]
---------------------------------------------------
 1|MULTIPOLYGON(((-70, etc...)|
 2|MULTIPOLYGON(((-71, etc...)|

I need to create the centroid of each shape, how can I do this? The final result should be this table:

id|      shape [geometry]     |  centroid [geomety]
---------------------------------------------------
 1|MULTIPOLYGON(((-70, etc...)|POINT(something)
 2|MULTIPOLYGON(((-71, etc...)|POINT(something_else)

Edit: should be some update with st_centroid(shape) I guess

1 Answer 1

6
ALTER TABLE my_table ADD COLUMN centroid GEOMETRY;
UPDATE my_table SET centroid = ST_Centroid(geom);

If you need to be certain that the point is within the polygon however, use ST_PointOnSurface():

ALTER TABLE my_table ADD COLUMN centroid GEOMETRY;
UPDATE my_table SET centroid = ST_PointOnSurface(geom);

Further reading:

3
  • Thanks, didn't know about st_PointOnSurface()
    – pmiranda
    Commented May 28, 2019 at 19:46
  • I don't see in the documentation that ST_PointOnSurface will consistently return a centroid within the polygon, How do you know that this is the case? Commented Jul 19, 2019 at 12:55
  • It is not a centroid, it is a point that is guaranteed to be on the polygon. Those definitions are fundamentally different.
    – wfgeo
    Commented Jul 19, 2019 at 14:52

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.