[geos-devel] Postgis 2 and TopologyExceptions (Martin Davis)

[Lars Aksel Opsahl]

Hi

Thanks a lot for your help, you guys are doing a great job for the open-source community.

I have tested your suggestion and it seems to work, but I had to change the value 1e-8 to handle some other cases.

I have made a function called Fussy_Difference where I use buffer when I get an Excepetion.

When I run the script now it runs through I see the messages when when it have to try the buffer operation.
NOTICE: Failed to run Difference, try buffer with 1e-08. Area difference area for g1 0.99999031027232 and for g2 0.999995189146681


We see the TopologyException errors quite often and different people seem to handle them in different ways.

If we in POSTGIS could have a way to handle this problem in a more common way that could help us a lot.

For instance if we in POSTGIS could have settings where we could configure what to do when we get 
TopologyException from ST_Difference or any other POSTGIS function.

Then we could configure the Postgis enviroment to handle the TopologyException in a way that is best for our case. 

What to do depends of course on what kind data you have and the usage, but after a while we will get the 
experience that buffer with 1e-8 seems to work ok on data in degrees for Norway when use ST_Difference .

We can of course wrap the Postgis functions into our own functions like Fussy_Difference, but if we use Geoserver or 
other tools against the database, it may not be possible to use your own functions.

I have also tested with reduced precision but that causes to many polygons to collaps and we get incorrect results, 
so we also need some tests on the changed data to verify that the changes are inside the tolrances we can accept in our case.

Her is the Fussy_Difference function that I use.


CREATE OR REPLACE FUNCTION test_sql.Fussy_Difference(g1 geometry,g2 geometry) RETURNS geometry AS $$DECLARE

newg geometry;

-- helper variables for use when EXCEPTION

g1_area_before_buffer float;

g2_area_before_buffer float;

-- TODO maybe add this a parameter

-- buffer_value float = 1e-9;

-- NOTICE: Failed to run Difference, try buffer with 1e-09. Change in area for g1 0.999999029080412 and for g2 0.99999951469369

-- CONTEXT: SQL statement "INSERT INTO intersect_geo_list(flatenr, ftema, geo)

buffer_value float = 1e-8;

BEGIN

BEGIN

newg = ST_Difference(g1,g2);

EXCEPTION WHEN internal_error THEN

-- if get a excpetion try buffer with a small value

-- TODO maybe add max area change tolerance as a parameter

g1_area_before_buffer = ST_Area(g1);

g2_area_before_buffer = ST_Area(g2);

g1 = St_Buffer(g1,buffer_value);

g2 = St_Buffer(g2,buffer_value);

RAISE NOTICE 'Failed to run Difference, try buffer with %. Area difference area for g1 % and for g2 %', buffer_value, test_sql.fraction_area(g1_area_before_buffer,ST_Area(g1)), test_sql.fraction_area(g2_area_before_buffer,ST_Area(g2));

newg = ST_Difference(g1,g2);

END;

RETURN newg;

END;

$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_sql.fraction_area(a1 float, a2 float) RETURNS float8 AS $$DECLARE

fractioin float8 = 0.0;

BEGIN

IF a2 != 0 THEN

IF (a1 < a2) THEN

fractioin = a1/a2;

ELSE

fractioin = a2/a1;

END IF;

END IF;

RETURN fractioin;

END;


Lars