Let's assume, I have a mixed geometry type table in PostgreSQL The multipart geometries don't intersect themselves. I would like to generate a center point collection from every geometry. I know how to create centroids from singlepart geoms. But is it possible to create singlepart geoms from multipart geoms without making a new table (ST_DUMP)? And in the memory take every converted geom, and get its center point. Is it possible?

  • 1
    ST_Dump doesn't create a new table, it returns a set of results. Look at the 1st example in the doc and apply the centroid function instead of ST_AsEWKT
    – JGH
    Commented Dec 1, 2021 at 19:01

1 Answer 1


With a LATERAL table function expression (as implicit CROSS JOIN) instead of record notation selection ((ST_Dump()).*) - flexible, fast and elegant:

SELECT t.<id>,
       ST_Centroid(dmp.geom) AS geom    -- ST_PointOnSurface(dmp.geom)
FROM   <table> AS t,
       LATERAL ST_Dump(t.<geom>) AS dmp

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.