0

I am running the following query on Postgres 10.3/Postgis 2.4:

create table table_c as (
    select A.*, B.area_name, B.id
    from table_a A
    left join table_b B
    on ST_Contains(A.geom, B.geom)
);

that fails with a cryptic:

ERROR:  GEOSContains: TopologyException: side location conflict at -2.0889721121124643 53.528652265475735

due to Postgis (and relative used libraries).

Is there a way to skip exceptional cases and keeping the query running until finished?

  • Unrelated, but: the parentheses around the select are totally useless. – a_horse_with_no_name Apr 24 '18 at 13:47
  • I know it thanks :) but I think they are more readable – Randomize Apr 24 '18 at 13:48
2

You can wrap the PostGIS function ST_Contains() in a plpgsql (or other PL) function of your own and trap the exception there. Like:

CREATE OR REPLACE FUNCTION f_ST_Contains(geometry, geometry)
  RETURNS boolean AS
$func$
BEGIN
   RETURN ST_Contains($1,$2);

EXCEPTION WHEN OTHERS THEN
   RETURN FALSE;  -- Return FALSE instead of raising an exception
END
$func$  LANGUAGE plpgsql IMMUTABLE;

This returns FALSE instead of raising an error. You might want return NULL instead. Same effect on your query.

And use that instead of ST_Contains() in your query:

...
ON f_ST_Contains(A.geom, B.geom)

Schema-qualify the function name in the function definition (and in the call) to remove dependency on the search path:

...
RETURN public.ST_Contains($1,$2);  -- actual schema of extension here
...

Not sure about implications on performance (especially index usage); didn't test.

  • Thank you, it is a nice solution. Unfortunately the query planner in "explain" looks like it is skipping the indexes. – Randomize Apr 24 '18 at 16:10
  • let's try with geography columns – Randomize Apr 24 '18 at 16:23
  • I have added my answer – Randomize Apr 27 '18 at 10:38
1

I think the best way is filtering the query or prefixing the data using commands like:

ST_IsValidReason, ST_IsValid, ST_MakeValid.

http://postgis.refractions.net/docs/ST_IsValidReason.html

  • Yes, ST_IsValid() should serve well! Wrap (only!) the table with (possibly) violating values in a subquery with WHERE ST_IsValid(geom)), then the index on the other table should still be usable. – Erwin Brandstetter Apr 27 '18 at 14:06

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.