9

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
6

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

  • 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
4

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
)
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;
  • 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
1

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;

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.