Converting multilinestring and multipolygon to multipoint in PostGIS?

Question

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.

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.