1

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)

  • Are you trying to do this in QGIS, or on the command line? Do you want to modify the table with the linestrings or create a new table? Can you show the schema for the two tables (at least what the geometry columns are called) – BradHards Feb 6 '15 at 9:19
  • The goal is to do it in a Windows bat file using OGR2OGR and OGRINFO. There's nothing unusual about the tables. One has a LINESTRING geom column and the other has a POLYGON geom column. – Damien Feb 6 '15 at 9:25
  • Please edit the question, and remember to answer the question about whether you want to modify existing table (perhaps saying what they are called) or create a new table. – BradHards Feb 6 '15 at 10:38
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.

  • Very nice. I tried to find a simple answer to your original question, but nothing surfaced. Thanks for posting this. – Micha Feb 13 '15 at 11:16
1

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

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.