Adding Nodes to Linestrings at Overlapping Points with Polygon Layer in Spatialite

Question

I have two Spatialite tables.

1. A table called 'lines' which contains a PK column called OGC_FID and a LINESTRING column called GEOM.

2. A table called polys which contains a PK column called OGC_FID and a POLYGON column called GEOM.

How do I update the 'lines' table GEOM so that the intersection points with any POLYGONS in the polys table are added to the LINESTRING geometries?

(Preferably using SQL queries only. Temp tables are fine) (Postgis answer acceptable)

Answer 1

This is the method that I eventually used to solve the problem. This works for basic LINESTRINGS AND POLYGONS. MULTILINESTRINGS and MULTIPOLYGONS would require a more complex function.

/*
Create a temporary table that contains the ID of each line and two
geometry columns.  The first contains any line portions that are
outside of all of the polygons.  The second is a collection of any
intersections with the polygons.
 */
CREATE TABLE temp_geoms AS
SELECT l.OGC_FID as ID
      ,ST_Difference(l.GEOM, ST_Union(p.GEOM)) AS diff
      ,ST_Collect(ST_Intersection(l.GEOM, p.GEOM)) AS inter
FROM lines AS l
  JOIN polys AS p
    ON ST_Intersects(l.GEOM, p.GEOM) = 1
GROUP BY l.OGC_FID

/*
Update the original lines table by merging the two temporary geoms.
There are two scenarios and two UPDATE statements required -:
  1.  Both differences and intersections with the polygons.
  2.  Only interesections
 */
UPDATE lines
SET GEOM = (SELECT ST_LineMerge(ST_Collect(diff,inter))
            FROM temp_geoms
            WHERE ID = lines.OGC_FID)
WHERE OGC_FID IN
  (SELECT ID
   FROM temp_geoms
   WHERE diff IS NOT NULL
     AND inter IS NOT NULL)

UPDATE lines
SET GEOM = (SELECT ST_LineMerge(inter)
            FROM temp_geoms
            WHERE ID = lines.OGCFID)
WHERE OGC_FID IN
  (SELECT ID
   FROM temp_geoms
   WHERE diff IS NULL)

The original 'lines' table now contains LINESTRINGS that have additional nodes at the intersection points with any POLYGONS in the 'polys' table. Lines that are completely outside of the polygons will remain untouched.

Answer 2

This is a much simpler method..

    UPDATE lines
    SET GEOM = ST_LineMerge(ST_Difference(GEOM,(SELECT ST_Union(ST_Boundary(GEOM))
                                                FROM polys
                                                WHERE ST_Intersects(GEOM,lines.GEOM) = 1)))
    WHERE (SELECT geometry
           FROM polys
           WHERE ST_Intersects(GEOM, lines.GEOM) = 1
           LIMIT 1) IS NOT NULL