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 Answer
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
;
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