Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
up vote 4 down vote accepted

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

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

share|improve this answer

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
share|improve this answer

ad 1.

SELECT ST_PointFromWKB(ST_AsEWKB(linestring_geom)) AS point_geom FROM linestring_table;

ad 2. Select Duplicate Points PostGIS

FROM points AS a, points AS b
WHERE ST_Equals(a.geom, b.geom) AND <>;

share|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

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

SELECT geom, gid, path
  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;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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