How to extract POINT from a [LINE|MULTILINE]STRING

Question

I'd like to create a vectorLayer in QGIS to display all the points that form a LINESTRING or a MULTILINESTRING stored in a PostGIS DB.

I think that I need to transform all points of LINESTRING (or MULTILINESTRING) to POINT.

First Question:

Is there any PostGIS function to do that?

Second Question:

If I want to exclude duplicate points from the result POINT table, how can I do that?

Answer 1

To get point in order and link to orginal geometry use
SELECT (ST_DumpPoints(the_geom)).path as path, id, (ST_DumpPoints(the_geom)).geom FROM linestrings )

and remove dublicates from http://wiki.postgresql.org/wiki/Deleting_duplicates

remember that you need to have one unique id for dublicate removing, if you dont have one you need to create it

Answer 2

As far as the first question is concerned, there is a PostGIS function just for that, ST_DumpPoints.

The first example in the docs page is exactly what you need. It's a set-returning function, so it doesn't just dump the points, but also some info (the path array) that relates them to the original geometry. Since you only care about the points, you could try something like this:

SELECT (dp).geom FROM (
  SELECT ST_DumpPoints(geom) AS dp
    FROM linestrings
)

Answer 3

ad 1.

CREATE TABLE points AS
SELECT ST_PointFromWKB(ST_AsEWKB(linestring_geom)) AS point_geom FROM linestring_table;

ad 2. Select Duplicate Points PostGIS

CREATE TEMPORARY TABLE temp AS 
SELECT *
FROM points AS a, points AS b
WHERE ST_Equals(a.geom, b.geom) AND a.id <> b.id;

DROP FROM points USING temp WHERE points.id=temp.id;