I am trying to create a new table of linestrings with their existing attribution that are "different" to some polygons and am struggling to get a working PostGIS query.
I have a table of linestrings (~130,000) that intersect polygons (~13,0000 which are valid), I created this table using ST_Intersects.
The query I originally tried to use was
CREATE TABLE new_lines AS SELECT l.id, l.value ST_Difference(l.geom, p.geom)::geom(LineString, 27700) as geom FROM lines l, polygons p;
This query ran for several hours and then failed due to GeometryCollection or MultiStrings, so had to amend my query to:
CREATE TABLE new_lines AS SELECT l.id, l.value, ST_CollectionExtract(ST_Multi(ST_Difference(t.wkb_geometry, p.geom)), 2)::geometry(MultiLineString, 27700) as geom FROM lines l, polygons p;
Whilst that was running I thought I would try the identical task in QGIS using "difference" and that successfully completes in 42 minutes giving me the results I expected.
The problem is that the Shapefile it creates is over the 2Gb limit hence needing to get a working PostGIS query.
I have also tried the other SQL found here: Trouble using ST_Difference to remove overlapping features and here Difference between two layers in PostGIS. So i tried this:
CREATE TABLE new_lines AS SELECT COALESCE(ST_Difference(l.geom, p.geom), l.geom) As geom FROM lines l LEFT JOIN polygons p;
But both give me some good results but they do not seem to apply ST_difference to all the lines which is really confusing.
Am sure this should be easy to do but cannot understand why my first query is taking so long and two why these other solutions only partly work.
So I amended my query to the following:
CREATE TABLE new_lines AS SELECT row_number() over() AS gid, g.* FROM (SELECT l.value ST_CollectionExtract(ST_Multi(ST_Difference(a.geom, b.geom)), 2)::geometry(MultiLineString, 27700) as geom FROM lines a, polygons b where st_intersects(a.geom,b.geom)) g;
Which successfully ran in around 20 minutes, however, not all the lines are clipped to the polygon boundary. Some are some are not.
I have checked that all geometries are valid and that there are no duplicate features in either dataset.
So am very confused.