Log or skip row causing error in PostGIS

Question

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?

Answer 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.