6

I have polygons defined in KML files just like this one, which I can easily parse. Example:

<Polygon><outerBoundaryIs><LinearRing><coordinates>
-91.463940,40.372660,0
-91.483790,40.379440,0
-91.493780,40.392410,0
</coordinates></LinearRing></outerBoundaryIs></Polygon>

I would like to run queries on a postgis table and clip all lines that intersect this polygon. Segments of the lines within the polygon should be deleted. Something like this:

    ____
___|poly|___line
___|    |___line
   |____|

Here's the table structure:

    Column    |     Type
--------------+---------------
 ogc_fid      | integer
 wkb_geometry | geometry 
 id           | numeric(8,0)  
 ele          | numeric(12,3)

Is this possible?

What would a query look like?

I assume I need to express the polygon in WKT format and use the documented ST_Intersects method, but I'm not sure how to put this together and modify the geometries.

Can this be done with PyQGIS?

I've loaded my kml file as a QgsVectorLayer, I can access the polygon feature and I can check if it intersects a rectangle:

l = QgsVectorLayer(sys.argv[1], "REGION", "ogr")
r = QgsRectangle(x1,y1,x2,y2)
for feature in l.getFeatures():
    if (feature.geometry().intersects(r)):
        return True
Improve this question
4
  • 1
    What do you mean by clear but not deleted? That sounds like a question about style rather than geometry?
    – John Powell
    Dec 14, 2014 at 12:15
  • Rephrased the question for clarity. This is not about style, this is about clipping and deleting geometries in the table.
    – kontextify
    Dec 14, 2014 at 16:04
  • 1
    Does HeyOverThere's answer not do what you are looking for?
    – John Powell
    Dec 14, 2014 at 16:27
  • Please decide whether you are looking for an SQL or PyQGIS answer to your question. I suggest editing it to be SQL so as not to strand the current answer and then asking a new question about PyQGIS.
    – PolyGeo ♦
    Aug 23, 2019 at 18:54

1 Answer 1

Reset to default
5

You need ST_Difference which returns the the geometry of a feature that does not intersect with another feature. In your case something like this:

SELECT l.id, ST_Difference(l.geom, p.wkb_geometry) As diff_geom
FROM
lines l, polygons p

Modify for your actual table and field names.

Improve this answer
4
  • Thanks, that's a good start. That SELECT would give me tens of gigs of data though, inserting that into a new table and then dropping the original table is not an option. I guess I need the exact opposite of ST_Difference, so I could do a DELETE. Also: how exactly can I use my set of points as the polygon in the query you suggested?
    – kontextify
    Dec 14, 2014 at 18:16
  • 1
    You are likely to have problems if you are short of space (something you should have said in the original post). You can't do a direct delete, as you are splitting lines (where they intersect polygons) and creating new smaller lines, so this is essentially a delete plus an insert operation. You could do it as an update, if you were prepared for each split LineString to become a MultiLinestring. However, whether it is a delete + insert, or an update, you are still likely to need a fair bit of space, as Postgres needs to save the previous state, in case the transaction needs to be rolled back.
    – John Powell
    Dec 14, 2014 at 19:11
  • @JohnBarça I see. I will try to find space. MultiLinestrings are fine. How would an update query look? Also, how can I create the polygon in the query from my set of points? Thanks.
    – kontextify
    Dec 16, 2014 at 18:15
  • 1
    Here's the PostgreSQL doc for UPDATE postgresql.org/docs/current/static/sql-update.html. You could also first run a DELETE on the table to remove any lines completely contained by a polygon
    – raphael
    Dec 27, 2014 at 3:05

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