Extracting POINT from LINE/MULTILINE-STRING

Question

I'd like to create a vector layer 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.

1. Is there any PostGIS function to do that?
2. 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 duplicates from http://wiki.postgresql.org/wiki/Deleting_duplicates

Remember that you need to have one unique id for duplicate removing. If you don't 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;

Answer 4

To dump only unique points, you could use a few subqueries with a SELECT DISTINCT ON expression:

SELECT geom, gid, path
FROM (
  SELECT DISTINCT ON (geom) geom, path, gid
  FROM (
    SELECT (ST_DumpPoints(the_geom)).geom, (ST_DumpPoints(the_geom)).path, gid
    FROM my_linestrings_table
  ) f
  ORDER BY geom, path, gid
) f
ORDER BY gid, path;

Answer 5

Just an example that includes an answer to the question and some more useful info:

ST_GeneratePoints - Generates random MULTIPOINT inside a given geometry (12 dimensional in this case, 1996 is just a random seed)

ST_DumpPoints - Generates geometry collection

ST_AsText - takes the .geom property and generates POINT collection in this case.

DISTINCT ON is a powerful PostgreSQL method - more here: https://www.geekytidbits.com/postgres-distinct-on/

4326 is the coordinate system

SELECT DISTINCT ON (point) ST_AsText(
    (
        ST_DumpPoints(
            ST_GeneratePoints(poly, 12, 1996)
        )
    ).geom
) as point
FROM
(
    SELECT ST_GeomFromText('POLYGON((23.328909873962402 42.68814533000658,
                                  23.335561752319336 42.696598850545385,
                                  23.329596519470215 42.70426280177124,
                                  23.31174373626709 42.70527197588727,
                                  23.308696746826172 42.69514795493774,
                                  23.328909873962402 42.68814533000658))', 4326
    ) as poly
) as s;

And the result is:

                point                
------------------------------------------
 POINT(23.3153923922854 42.6981985750813)
 POINT(23.322609078916 42.6955123492145)
 POINT(23.3325814445457 42.6940645417387)
 POINT(23.3304303573151 42.6986413369009)
 POINT(23.3222841853423 42.6945454176055)
 POINT(23.3206530451184 42.6992103204114)
 POINT(23.3183617673849 42.7044894224324)
 POINT(23.3156071811902 42.69278639295)
 POINT(23.3129420749729 42.6973062262054)
 POINT(23.3232822960367 42.7008505594882)
 POINT(23.3337204984442 42.6981065003279)
 POINT(23.327087530614 42.6941171969645)