Postgresql how to work around GEOSIntersects error?

Question

So I have a large select query that has over 1700 rows which basically extracts data from various tables (some points, some lines, with various conditions in between them) that intersect with a polygon table

select a.id
(select count(*) from pointtable2 b where st_contains(a.geom,b.geom ) )
(select sum(st_length(st_intersection(a.geometry, r.geometry))) from linetable3 b where st_intersects(a.geom,b.geom ) ),
(select a whole bunch of stuff from several other tables where multitude of conditions..)
from polygontable a
group by a.id, a.geom

One line layer in particular has invalid geometries which initially I dealt with by adding on the where condition

geometrytype(r.geom) <> 'MULTICURVE'::text AND st_isvalid(r.geom) is true

Problem is, even with these conditions, I'm still getting the following error

SQL Error [XX000]: ERROR: GEOSIntersects: IllegalArgumentException: RobustDeterminant encountered non-finite numbers 
ERROR: GEOSIntersects: IllegalArgumentException: RobustDeterminant encountered non-finite numbers 
ERROR: GEOSIntersects: IllegalArgumentException: RobustDeterminant encountered non-finite numbers

Fixing the wrong geometries would be the long term solution but this report view is needed with urgency, what would be the best way of running my query while ignoring failing rows? Or how would I be able to spot which geometries/which lines of code have failed?

Hi Can you also post the geometries that are causing the error? — Jim Jones, Mar 01 '21 at 08:21
@JimJones I do not know which ones, that's why I wanted to know how if it was possible to identify them — Luffydude, Mar 01 '21 at 09:07
I see. Can you tell us which version of PostGIS and PostgreSQL you're using? — Jim Jones, Mar 01 '21 at 09:12
Strange. I'm unable to reproduce the error. Can you try this query and tell us what you get? `WITH j(geom)AS ( VALUES ('MULTICURVE((0 0,5 5))'::GEOMETRY), ('POINT(0 0 42)'::GEOMETRY), ('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))'::GEOMETRY), ('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'::GEOMETRY) ) SELECT ST_AsText(geom),ST_IsValidReason(geom) FROM j WHERE ST_Intersects(geom,'POINT(0 0)'::GEOMETRY) AND ST_IsValid(geom);` — Jim Jones, Mar 01 '21 at 09:33
@JimJones hmm I got SQL Error [XX000]: ERROR: Unknown geometry type: 11 - MultiCurve — Luffydude, Mar 01 '21 at 15:24
I see now that I have postgis 3.0, and not your 2.5... and I do not get any error. Would be an option for you to upgrade it? — Jim Jones, Mar 01 '21 at 15:25

Jim Jones · Accepted Answer · 2021-03-01T17:11:09.890

This issue has been most likely addressed in the latest PostGIS versions. See the example below using PostgreSQL 12 and PostGIS 3.0:

WITH j(geom)AS (
  VALUES ('MULTICURVE((0 0,5 5))'::GEOMETRY),
         ('POINT(0 0 42)'::GEOMETRY),
         ('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))'::GEOMETRY), --invalid polygon
         ('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'::GEOMETRY)
  )
SELECT 
  ST_AsText(geom),ST_IsValidReason(geom), 
  GeometryType(geom),
  ST_Intersection(ST_MakeValid(geom),'POINT(0 0)'::GEOMETRY)
FROM j
WHERE 
  ST_Intersects(geom,'POINT(0 0)'::GEOMETRY) AND 
  GeometryType(geom) <> 'MULTICURVE' 
  AND   ST_IsValid(geom); 

                      st_astext                       | st_isvalidreason | geometrytype |                      st_intersection                       
------------------------------------------------------+------------------+--------------+------------------------------------------------------------
 POINT Z (0 0 42)                                     | Valid Geometry   | POINT        | 0101000080000000000000000000000000000000000000000000004540
 POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)) | Valid Geometry   | POLYGON      | 010100000000000000000000000000000000000000

Postgresql how to work around GEOSIntersects error?

1 Answers1