10

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?

  • thanks to all. All your answers have helped me a lot. I have to think a little bit in order to decide which should be marked as the correct one :) – Heisenbug Nov 23 '12 at 14:43
7

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

| improve this answer | |
  • Hi there is a typo, you should remove the last bracket. The right syntax should be SELECT (ST_DumpPoints(the_geom)).path as path, id, (ST_DumpPoints(the_geom)).geom FROM linestrings – aborruso Mar 9 '17 at 15:07
5

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
)
| improve this answer | |
2

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;
| improve this answer | |
  • This does not work, it produces empty geometries and PostgreSQL issues warnings for each row processed: OGC WKB expected, EWKB provided - use GeometryFromEWKB() for this. You could just skip ST_AsEWKB() altogether, but it still wouldn't make any sense, see the last example in ST_PointFromWKB – kprist Nov 23 '12 at 12:13
  • NULL is returned if the input bytea does not represent a POINT geometry. – Vladimir Nov 23 '12 at 12:29
2

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;
| improve this answer | |
0

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.