PostGIS Intersect – How to Identify Intersecting Line Segments in PostGIS

postgisqgisst-intersects

I have a PostGIS table of thousands of linestring segments wherein each line segment has only two nodes, start and end. All of the lines have a start and/or end node that matches another line segment in the table (they are all connected end-to-end). I'm trying to create a table of points representing line intersections where a line segments intersects another line segment in the table, but NOT intersections that are only intersections because they share a start or end node with another line in the table.

The query shown below selects all intersecting start and end nodes, but NOT the crossing segments (which is what I want)

CREATE TABLE intersection_points as
SELECT      
    ST_Intersection(a.geom, b.geom),
    Count(Distinct a.id)
FROM
    public."82n16nw_gsl_paths" as a,
    public."82n16nw_gsl_paths" as b
WHERE
    ST_Touches(a.geom, b.geom)
    AND a.id != b.id
GROUP BY
    ST_Intersection(a.geom, b.geom)
;

graphic of lines and intersection points:
query result showing intersection points

the QGIS 'Line Intersections' tool selects the start/end node intersections AND the segment intersections that I'm after, but there is no way to only identify the segment-only points from this tool.

Best Answer

To find lines which intersect in the interior of both lines, a query is:

CREATE TABLE intersection_points as
SELECT      
    ST_Intersection(a.geom, b.geom) AS intpt
FROM a 
JOIN b ON ST_Intersects(a.geom, b.geom)
WHERE a.id < b.id 
      AND NOT ST_Touches(a.geom, b.geom);

To find intersections where at most one of the lines can intersect at an endpoint, the query has to select line pairs which intersect but where their boundaries do not intersect (the DE-9IM pattern F):

CREATE TABLE intersection_points as
SELECT      
    ST_Intersection(a.geom, b.geom) AS intpt
FROM a 
JOIN b ON ST_Intersects(a.geom, b.geom)
WHERE a.id < b.id 
      AND ST_Relate(a.geom, b.geom, '****F****');
Related Question