1

I have a PostGIS query that counts the length of individual lines in each buffer:

SELECT 
   buffered.name, 
   ST_LENGTH(ST_Intersection(buffered.buffer, lines.wkb_geometry)) AS total_length 
 FROM 
   buffered, lines  
WHERE ST_Intersects(buffered.buffer, lines.wkb_geometry) 
GROUP BY buffered.name, buffered.buffer, lines.wkb_geometry

On one table of lines, the query runs successfully, but with a different one, psql throws the following error:

ERROR:  Error performing intersection: InterruptedException: Interrupted!

I have tried everything, I validated geometries on both tables with ST_MakeValid() and dropped lines from the table that have 0 length.

If both geometries are valid, how is it possible to get an error performing an intersection and what could be going wrong? Is there a way to skip the errors? Note: buffered.buffer is of type geometry

Other important info: the query seems to run endlessly in psql: after about an hour, I cancelled the query and this is the message that was returned:

Cancel request sent
ERROR:  Error performing intersection: InterruptedException: Interrupted!

If I cancel the request after 5 or 10 minutes, no such error occurs and the log only shows

Cancel request sent ERROR: canceling statement due to user request

  • 2
    I have never seen this error -- which almost certainly comes from GEOS. You might have to post the actual geometries, so that people can test, and also state your OS, results of SELECT PostGIS_Full_Version(); and anyting else that might be relevant. – John Powell Nov 3 '17 at 11:49
  • The error is from psql. The queries ran for about an hour, then I decided to cancel it and the following was returned: ^Cancel request sent ERROR: Error performing intersection: InterruptedException: Interrupted! – the_darkside Nov 3 '17 at 12:06
  • POSTGIS="2.2.2 r14797" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 1.11.2, released 2015/02/10 GDAL_DATA not found" LIBXML="2.9.2" LIBJSON="0.12" RASTER and Operating System is macOS 10.12.1 - the geometries are too large to post. Is there a way to log on which record the error is happening? ? – the_darkside Nov 3 '17 at 12:11
  • So this error is pretty much unrelated to your problem? Please edit the question to clarify that the issue is a long-running query; please also include the query plan specified by EXPLAIN, and an indication of which indexes are available. – Vince Nov 3 '17 at 12:14
  • Please do not add critical information as comments. Those details belong within the question. It's not fair to those who would answer to need to mine the comments for information. – Vince Nov 3 '17 at 12:16
1

It looks like postgis is getting into endless loop because of some weird geom and after while is just canceling query to not kill your machine. How many rows you got in each table?

Here's what you can do:

  • run basic select query that involve geometry on each table separately. Could be something like:

SELECT ST_BUFFER(buffered.buffer, 5) buffer FROM buffered

  • If one query run forever you will identify on which table you got problem.
  • If both selection works fine check EPSG of both geometry.
  • Try to split your query into smaller one to catch issue. You can run for example:

SELECT ST_Intersects(buffered.buffer, lines.wkb_geometry) FROM buffered, lines

  • If you got big tables you can try to split table into chunks and add spatial indexes

Without the sample data is hard to guess what is causing the error.

  • Edit your query so that result contains fids and run it with "limit" by increasing "offset" and you may find the geometries which make trouble. – user30184 Nov 3 '17 at 15:02
  • SELECT ST_Intersects(buffered.buffer, lines.wkb_geometry) FROM buffered, lines ran with no errors – the_darkside Nov 3 '17 at 15:31
  • SELECT ST_Length(ST_Intersection(buffered.buffer, lines.wkb_geometry)) FROM buffered, lines runs endlessly – the_darkside Nov 3 '17 at 16:36
  • There are 1371 records in lines and 2303 records in buffered – the_darkside Nov 3 '17 at 16:42
  • try to do it without st_length , combaing this 2 methods looks weird as st_intersection - returns a geometry that represents the shared portion of geomA and geomB. And what you trying to achive is "counts the length of individual lines in each buffer: " So what you really counting is the length of new geometry of shared portion of line and buffer – Losbaltica Nov 3 '17 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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