2

In my simple PostGIS query to find the lengths of lines that fall within buffers (polygons)

SELECT a.name, b.route_id,  ST_LENGTH(ST_Intersection(a.buffer, b.wkb_geometry))
FROM buffered a, lines b 
WHERE ST_Intersects(a.buffer, b.wkb_geometry)

The query runs endlessly and when I send a cancel request, I am returned an error:

ERROR: Error performing intersection: InterruptedException: Interrupted!`

This query has worked with one table of lines, but does not work with another. How can one log the index of the problematic row(s) in order that they be later skipped or removed?

6
  • Can you give a bit of background on the data. How many line features are within table B and how complex are they? How big is your buffer and how many features of table B are likely to be caught up in the buffers (10's, 100's, 1000's)?
    – Liam G
    Commented Nov 5, 2017 at 22:13
  • table a has 2303 records and table b has 1371 records, almost all of the lines are within at least one of the buffers Commented Nov 5, 2017 at 22:31
  • both tables have the same SRID and both geometries are valid, the line features usually have around 100 nodes each Commented Nov 5, 2017 at 22:42
  • does the query run without st_length? if so, what type are the returned geometries (e.g. simple, multi, collections and/or mixed)? if not (or it takes too long), can you get results by increasing return limits (e.g. 1, 10, 100, 1000)? and if so, as above, what type? O think 2000 features with given (not on-the-fly-buffered) polygons shouldn't take 10 mins, even thought intersection IS slow. could you also check query analyze if indexes are indeed used?
    – geozelot
    Commented Nov 6, 2017 at 11:12
  • this is more a guess and also applies only if your wkb geometries are indeed in raw bytea format: those geoms will have SRID 0, as simple WKB doesn´t store SRID information (check via ST_SRID(ST_GeomFromWKB(wkb_geometry))). In that case it might be worth a try to transform your geometries (ST_GeomFromWKB(wkb_geometry, <your_SRID>) AS geom) in advance into a new column, vacuum and reindex on that column and try your query again. I'm not entirely sure though if that affects the usage of indexes (and will have any effect on your issue at all), maybe someone else can elaborate more on this
    – geozelot
    Commented Nov 6, 2017 at 12:06

1 Answer 1

1

You aren't getting an error due to one particular row, it looks like it's just the intersection taking a long time. I'm going to assume both tables have a spatial index. Intersections are pretty slow in PostGIS so try to speed things up with a CASE statement that only performs the intersection if it needs to.

SELECT a.name, 
       b.route_id,  
       CASE 
           WHEN ST_Contains(a.buffer,b.wkb_geometry) THEN ST_LENGTH(b.wkb_geometry)
           ELSE ST_LENGTH(ST_Intersection(a.buffer, b.wkb_geometry))
       END
FROM buffered a, lines b 
WHERE ST_Intersects(a.buffer, b.wkb_geometry)

Here if the line is totally within the buffer it just outputs the length of the line, and then it will compute the intersection if this criteria isn't satisfied.

This isn't a guaranteed time save as it depends on how many lines fit totally within the buffers, but if many of them do then it should result in a noticeable boost. So if it fails you may need to look further into the characteristics of your data and possibly look to try and split some of the lines if they are very long and complex in geometry.

1
  • Thanks for the tip. The thing is I performed the exact same query on a different table of lines which had a fewer number (~900) , and psql gave an output in less than 10 minutes. For some reason, even with spatial indices, this query runs endlessly in psql and only when I hit CNTRL + C to abort does the error message show: ^Cancel request sent ERROR: Error performing intersection: InterruptedException: Interrupted! Otherwise, psql just keeps on running for hours and I am not sure if I should let it. It's as if psql has forgotten to stop running the query even though there is an error. Commented Nov 5, 2017 at 23:26

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.