Is there any way in SQL to supress errors in PostGIS functions, for instance if some of your geometries are invalid but you want to report a null value for the output or something?

To give an example, I have a table with a structure:

id, geom1, geom2

And I would like to run a query like select id, st_split(geom1, geom2) as geom from mytable, but whenever there is a problem with the geometry, I get ERROR: Splitter line has linear intersection with input and the process stops. What if I want to discard this geometry and carrying on, giving a null result for the geometry?

I presume you can do this in PL-pgSQL, and I will post a solution here if I figure that out, but is there no way of doing it in plain SQL?

  • 2
    Not in SQL; it's an elementary property of data integrity safety. You can see if your client allows for silent auto-rollback, but to actually catch errors and continue, you need to wrap the functionality within a PL/pgSQL function. However, for your specific scenario, you could limit the rows to split to those where all intersections of its geometries are points; check e.g. ST_Relate – ThingumaBob Nov 28 '19 at 19:13

Your Answer

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

Browse other questions tagged or ask your own question.