6

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.

0

4 Answers 4

6

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: enter image description here

1
4

Use an EXISTS expression:

DELETE
FROM   <lines> AS ln
WHERE  EXISTS (
  SELECT 1
  FROM   <poly> 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).

3

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.

0
3

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.