How to convert error to null in postgres (postgis) query?

user_45525 0
Viewed 271

I have the following postgis query:

select st_pointonsurface(geometry), geometry, other, important, columns 
from my_polygons;

After ~2.5m results there's a record where the function fails.

ERROR: GEOSPoint OnSurface: TopologyException: 
Input geom 1 is invalid: Self-intersection at or near 
point -9083598.0378282126 3646413.3039207752 at -9083598.0378282126 3646413.3039207752

Looking at the geometry, I can see why this fails. It's a bad polygon.

I would like to either

  1. ignore the row (filter it from the result set); or
  2. ignore the column (convert it to null and return the other columns).

How can I get either of these outcomes?

postgis postgresql
1 Answers

This function will "try/catch" the function, returning null on any exception.

CREATE OR replace FUNCTION polylabel(arg geometry)
RETURNS geometry LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        RETURN st_pointonsurface(arg);
    EXCEPTION WHEN OTHERS THEN
        RETURN null;
    end;
END $$;
user_455250
Related