Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

3 Answers 3

up vote 2 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 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

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.

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

Your Answer

 
discard

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.