extract separate nodes in Postgis equivalent QGIS

Question

I want to extract nodes from polygon in Postgis like it QGIS "extract nodes" tool does. I've red the answered question about this in What's the PostGIS equivalent to Extract Nodes in fTools and used ST_DumpPoints(geom) function. But the result contains both the geometry and a path telling where in the polygon the point belongs. (ST_DumpPoints(the_geom)).geom gives the geometry (points), OK. The problem is that I can't address to single node from geometries, because tool ST_DumpPoints return integer indexes (path) of the polygon the point belongs to. In short, when I visualize my dump points in QGIS, I have no possibility to select one single node - all nodes of polygon have been filtered (picture 2), but selected is only one. As a consequence, I can't implement any spatial analysis with every separate node (e.g. verification about other layer contains any of this nodes).

How I can simply get the points without connection to the polygons?

Answer 1

You can query the geometry of the point dump. The example in the doc returns two columns, one for the path and one for the geometry, but you can do it in a single query. You will get all points (one by row) sorted by feature.

select mylayer.id, (st_dumpPoints(the_geom)).geom  from mylayer order by mylayer.id;

** EDIT **

An option to display the nodes in QGIS is to create a spatial view. You would query the polygons, get the points and wrap that in another query to be able to construct a unique ID. It has the advantage of being linked to your polygons, reflecting any edits. However it is slow.... if too slow, it might be easier to create a new layer with the vertices.

CREATE OR REPLACE VIEW public.sv_myLayerNodes AS 
  SELECT ROW_NUMBER() OVER(ORDER BY  myLayerID, ptnode) AS V_UID, 
    myLayerID, 
    ptnode
  FROM (SELECT 
        myLayerID,
        (st_dumpPoints(the_geom)).geom as ptnode
    FROM public.myLayer
)b;