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 '18 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 '18 at 23:12

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, privacy policy and cookie policy

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