In PostGIS I have 2 layers:'lines' (multilinestring, public, gid, geom) and 'polygons' (multipolygon, public, gid, geom). I try to find how to convert them to multipoint (or point). I suspect that ST_CollectionExtract (https://postgis.net/docs/ST_CollectionExtract.html) can do that, but I didn't find any sql example; I tried sth like:

SELECT ST_CollectionExtract (public.polygons as geom,1)

but it doesn't work.

I'm not good at SQL. I usually try to find some sql example that uses all those things like gid, geom, table_name, column_name etc.

1 Answer 1


You want ST_DumpPoints which returns the a path (the indices of the point in the geometry) and a geometry. You can extract your points like so:

SELECT (ST_DumpPoints(geom)).geom 
FROM table

This returns rows with a point geometry in each one. (Here, the inner geom is the geometry column of your source table. The trailing .geom is the geometry member of the elements of the returned set: a point.)

The extra set of parentheses around the function call are necessary. ST_DumpPoints(geom).geom wouldn't work.

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.