I'm designing a system where there is a set of polygons representing different postal codes (not overlapping with each other). An user can either draw his own polygon OR fully select/deselect particular postal code. That means that each postal code can be included in the final area fully or partially.

I will use example (brown - user drawn area, light blue - postal code)

I found that when user draws some area, selects a postal code and then deselects it, the output geometry isn't the same as if he would deselect the postal code right away (g1 and g2 columns in below example). Mathematically it doesn't make sense. The g1 figure have weird spikes on the edges of the deselected postal code. How to fix it?

I found out that to fix it I can pass grid size (eg. 1e-9) to the st_union / st_difference, but then st_touches doesn't work correctly, because I can't pass grid size to this function. I can pass it to st_intersection but it is less efficient.

You can see it there:

WITH source AS (
    SELECT st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.92333,40.63875],[-85.92333,40.63392],[-85.91539,40.63492],[-85.91467,40.63875],[-85.92333,40.63875]]]]}') area,
           st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.82047, 40.65114],[-85.82046, 40.6297],[-85.92046, 40.6297],[-85.92068, 40.63807],[-85.92047, 40.65114],[-85.82047, 40.65114]]]]}') postalcode
),
difference AS (
SELECT st_difference(st_union(postalcode, area), postalcode)             g1,
       st_difference(area, postalcode)                                   g2,
       st_difference(st_union(postalcode, area, 1e-9), postalcode, 1e-9) g3,
       area,
       postalcode
FROM source
)
SELECT *, st_touches(g1, postalcode), st_touches(g2, postalcode), st_touches(g3, postalcode)
FROM difference

I understand that this can be because of how float is represented in binary and when the postgis calculates points of intersection it can be some irrational number which can't be saved in binary form exactly as it should. Also I found that both of those points from g1 and g2 are the same (POINT (-85.92066907421577 40.63875)).

Can you help me?

EDIT: also forgot to mention, does switching to different srid (some without floating operations) would help me?

I'm designing a system where there is a set of polygons representing different postal codes (not overlapping with each other). An user can either draw his own polygon OR fully select/deselect particular postal code. That means that each postal code can be included in the final area fully or partially.

I will use example (brown - user drawn area, light blue - postal code)

I found that when user draws some area, selects a postal code and then deselects it, the output geometry isn't the same as if he would deselect the postal code right away (g1 and g2 columns in below example). Mathematically it doesn't make sense. The g1 figure have weird spikes on the edges of the deselected postal code. How to fix it?

I found out that to fix it I can pass grid size (eg. 1e-9) to the st_union / st_difference, but then st_touches doesn't work correctly, because I can't pass grid size to this function. I can pass it to st_intersection but it is less efficient.

You can see it there:

WITH source AS (
    SELECT st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.92333,40.63875],[-85.92333,40.63392],[-85.91539,40.63492],[-85.91467,40.63875],[-85.92333,40.63875]]]]}') area,
           st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.82047, 40.65114],[-85.82046, 40.6297],[-85.92046, 40.6297],[-85.92068, 40.63807],[-85.92047, 40.65114],[-85.82047, 40.65114]]]]}') postalcode
),
difference AS (
SELECT st_difference(st_union(postalcode, area), postalcode)             g1,
       st_difference(area, postalcode)                                   g2,
       st_difference(st_union(postalcode, area, 1e-9), postalcode, 1e-9) g3,
       area,
       postalcode
FROM source
)
SELECT *, st_touches(g1, postalcode), st_touches(g2, postalcode), st_touches(g3, postalcode)
FROM difference

I understand that this can be because of how float is represented in binary and when the PostGIS calculates points of intersection it can be some irrational number which can't be saved in binary form exactly as it should. Also I found that both of those points from g1 and g2 are the same (POINT (-85.92066907421577 40.63875)).

What should I do?

EDIT: Also forgot to mention, would switching to different srid (some without floating operations) help me?

I'm designing a system where there is a set of polygons representing different postal codes (not overlapping with each other). An user can either draw his own polygon OR fully select/deselect particular postal code. That means that each postal code can be included in the final area fully or partially.

I will use example (brown - user drawn area, light blue - postal code)

I found that when user draws some area, selects a postal code and then deselects it, the output geometry isn't the same as if he would deselect the postal code right away (g1 and g2 columns in below example). Mathematically it doesn't make sense. The g1 figure have weird spikes on the edges of the deselected postal code. How to fix it?

I found out that to fix it I can pass grid size (eg. 1e-9) to the st_union / st_difference, but then st_touches doesn't work correctly, because I can't pass grid size to this function. I can pass it to st_intersection but it is less efficient.

You can see it there:

WITH source AS (
    SELECT st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.92333,40.63875],[-85.92333,40.63392],[-85.91539,40.63492],[-85.91467,40.63875],[-85.92333,40.63875]]]]}') area,
           st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.82047, 40.65114],[-85.82046, 40.6297],[-85.92046, 40.6297],[-85.92068, 40.63807],[-85.92047, 40.65114],[-85.82047, 40.65114]]]]}') postalcode
),
difference AS (
SELECT st_difference(st_union(postalcode, area), postalcode)             g1,
       st_difference(area, postalcode)                                   g2,
       st_difference(st_union(postalcode, area, 1e-9), postalcode, 1e-9) g3,
       area,
       postalcode
FROM source
)
SELECT *, st_touches(g1, postalcode), st_touches(g2, postalcode), st_touches(g3, postalcode)
FROM difference

I understand that this can be because of how float is represented in binary and when the postgis calculates points of intersection it can be some irrational number which can't be saved in binary form exactly as it should. Also I found that both of those points from g1 and g2 are the same (POINT (-85.92066907421577 40.63875)).

Can you help me?

I'm designing a system where there is a set of polygons representing different postal codes (not overlapping with each other). An user can either draw his own polygon OR fully select/deselect particular postal code. That means that each postal code can be included in the final area fully or partially.

I will use example (brown - user drawn area, light blue - postal code)

I found that when user draws some area, selects a postal code and then deselects it, the output geometry isn't the same as if he would deselect the postal code right away (g1 and g2 columns in below example). Mathematically it doesn't make sense. The g1 figure have weird spikes on the edges of the deselected postal code. How to fix it?

I found out that to fix it I can pass grid size (eg. 1e-9) to the st_union / st_difference, but then st_touches doesn't work correctly, because I can't pass grid size to this function. I can pass it to st_intersection but it is less efficient.

You can see it there:

WITH source AS (
    SELECT st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.92333,40.63875],[-85.92333,40.63392],[-85.91539,40.63492],[-85.91467,40.63875],[-85.92333,40.63875]]]]}') area,
           st_geomfromgeojson('{"type":"MultiPolygon","coordinates":[[[[-85.82047, 40.65114],[-85.82046, 40.6297],[-85.92046, 40.6297],[-85.92068, 40.63807],[-85.92047, 40.65114],[-85.82047, 40.65114]]]]}') postalcode
),
difference AS (
SELECT st_difference(st_union(postalcode, area), postalcode)             g1,
       st_difference(area, postalcode)                                   g2,
       st_difference(st_union(postalcode, area, 1e-9), postalcode, 1e-9) g3,
       area,
       postalcode
FROM source
)
SELECT *, st_touches(g1, postalcode), st_touches(g2, postalcode), st_touches(g3, postalcode)
FROM difference

I understand that this can be because of how float is represented in binary and when the postgis calculates points of intersection it can be some irrational number which can't be saved in binary form exactly as it should. Also I found that both of those points from g1 and g2 are the same (POINT (-85.92066907421577 40.63875)).

Can you help me?

EDIT: also forgot to mention, does switching to different srid (some without floating operations) would help me?