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
    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 that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.