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.
Best Answer
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:
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.