Delete lines within polygon

Question

I have two layers, one with lines, one with polygons. I want to delete lines that fully fall (contain) within the polygons. It seems really simple, but I can't figure this one out in either PostgreSQL/PostGIS or QGIS.
For the SQL I am trying:
delete from lines_test2
 where ( select a.geom, b.geom
 from lines_test2 a, straten b
 where st_intersects(a.geom, b.geom));

But it gives me the error: 'subquery must return only one column'. Clipping in QGIS isn't a viable option either, because it should only delete full lines not parts of them.

BERA · Accepted Answer

You can use Select By location then delete selected rows in QGIS or join by within:
delete from ok_jl_riks_copy where id in (

select sub1.id from
(select *
from ok_jl_riks_copy jl) sub1

inner join

(select wkb_geometry from ok_ak_riks ak) sub2

on st_within(sub1.wkb_geometry, sub2.wkb_geometry)
)

All lines within the polygons are deleted:

Ian Turton · Answer

As the error message says you should only return a single column, so try:
delete from lines_test2
 where id = ( select a.id
 from lines_test2 a, straten b
 where st_intersects(a.geom, b.geom));

This assumes you have a unique ID column in your lines_test2 data table.

geozelot · Answer

Use an EXISTS expression: DELETE FROM AS ln WHERE EXISTS ( SELECT 1 FROM AS pl WHERE ST_Within(ln.geom, pl.geom) ); If the ST_Within check hits the first TRUE (selecting a truthy 1), the sub-query terminates for the current row (no matter if there were more than one hit). This is among the most efficient ways for when a table has to be traversed by row (as in an UPDATE/DELETE), or otherwise compared against a pre-selection (of e.g. ids).

Cyril Mikhalchenko · Answer

Here's another recipe, try maybe like it ?
SELECT ST_Difference(a.geom, b.geom) AS geom FROM line AS a CROSS JOIN LATERAL (SELECT ST_Collect(geom) AS geom FROM polygon WHERE  ST_Intersects(a.geom, geom)) AS b;

Delete lines within polygon

4 Answers

Add your own answers!

Ask a Question