I have asked about exporting geometry as a table (Link) and I got a very nice answer about how to do it with an SQL instruction.

Is there a way to select only the inerior of line type data? i.e. only the vertex that are not the ends. enter image description here

The following instruction will produce all vertex

SELECT sub.name AS "Pipe",
               ST_X(sub.geom) AS "X-Coord",
               ST_Y(sub.geom) AS "Y-Coord"
        FROM (
            SELECT name,
                   (ST_DumpPoints(geom)).geom AS geom
            FROM conduits
        ) AS sub

Here is the output:


I would like to get only:

  • This makes no sense whatsoever, topologically speaking. The gap between interior and boundary is infinitely small. It is the boundary which defines the interior, so they cannot be separated. An infinite number of features could be created between the gap between the first and second vertices, and a like number between N-1 and N.
    – Vince
    Jan 25, 2018 at 22:47
  • 1
    I think I did it! @Vince you are right if we were talking about real numbers (R^2), but we are somewhere else. I had to diggest the Dimensionally Extended 9 Intersection Model (DE-9IM) slowly to write down an SQL command. I took the hints and the picture from here: workshops.boundlessgeo.com/postgis-intro/de9im.html
    – Marco
    Jan 25, 2018 at 23:12

1 Answer 1


Dimensionally Extended 9 Intersection Model (DE-9IM)

So far, it works for the example.

SELECT subpoints.name AS "Pipe",
    ST_X(subpoints.geom) AS "X-Coord",
    ST_Y(subpoints.geom) AS "Y-Coord"
             (ST_DumpPoints(geom)).geom AS geom
      FROM conduits
      )  AS subpoints,
      (SELECT name,
              geom AS geom
      FROM conduits
      )  AS sublines
WHERE subpoints.name=sublines.name
AND  ST_Relate(subpoints.geom, sublines.geom, '0FF******')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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