CODEWITHSUNDEEP
sqlpostgresqlpostgisintersectiongeos
Luffydude
Luffydude

Reputation: 772

Postgresql how to work around GEOSIntersects error?

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?

Upvotes: 2

Views: 828

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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

Further reading:

Upvotes: 2

Related Questions