2

I have two layers. One is MultiLineString and the other is MultiPolygon.

enter image description here

I am trying to erase lines that are on the polygon feature With QGIS difference tool, it worked just fine

This is my code and I do not understand what is wrong with my query. I do not see anything wrong.

select a.objectid, st_difference(a.geom, b.geom) as shape
from line a, mesh b
limit 500

The results return line segments that are also in the mesh. enter image description here

Improve this question
0

1 Answer 1

Reset to default
1

Your query does a cross product between the line layer and the polygon layer. If you have 1 line and 2 polygon, the line being within the first polygon, you would still get two results: one with an empty geometry (line1-polygon1) and one with a full geometry (line1-polygon2).

If your dataset is not too big, you could aggregate all the polygons into one, and then compute the difference. I would also remove the lines that are completely within the polygon, and the polygons that don't intersect with the lines

The query would be similar to the following - untested - one:

SELECT a.objectid, st_difference(a.geom, b.geom) as shape
FROM line a, 
    LATERAL 
        (SELECT ST_COLLECT(m_single.geom) geom
        FROM 
            (SELECT (ST_DUMP(m.geom)).geom geom 
            FROM mesh m
            WHERE ST_Intersects(a.geom,m.geom))m_single) b
WHERE ST_Within(a.geom, b.geom) = false
Improve this answer
1
  • somehow the code gives me self-intersect error. I st-union(ed) my mesh and executed the difference and it worked. Thank you for your advice and comment!
    – Pil Kwon
    Commented Aug 20, 2018 at 4:29

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.