-- removes overlapping parts. Always cuts smaller polys out of bigger ones.

WITH
intersected as (
	SELECT 
		osm_id,
    	type,
		name,
    	-- intersected geometries
    	CASE
    		-- Heidelberg  8.6,49.33,8.8,49.49
    		WHEN 
    			nat.the_geom && ST_MakeEnvelope( 8.6,49.33,8.8,49.49 ,4326) -- a && b : a and b's bbox intersect
    			AND
    			nat.the_geom @ ST_MakeEnvelope( 8.6,49.33,8.8,49.49,4326) -- a @ b : a completly contained by b
    		THEN ST_MakeValid(nat.the_geom)
    		-- only do the real intersection work if polygons are not completely contained in bbox
			ELSE ST_Multi(
      				ST_Intersection(ST_MakeValid(nat.the_geom), ST_MakeEnvelope( 8.6,49.33,8.8,49.49 ,4326) )
      			 )
    	END as the_geom
    FROM public.naturals nat
    WHERE ST_Intersects(nat.the_geom, ST_MakeEnvelope(8.6,49.33,8.8,49.49 ,4326))
    ),
dirty as    (
select 	osm_id, 
		type,
		name,
		COALESCE(ST_Difference(
					ST_Multi(intersected.the_geom),
					(
					  SELECT ST_Union(b.the_geom)
						FROM intersected b
						WHERE intersected.osm_id != b.osm_id 
								AND ST_Intersects(intersected.the_geom, b.the_geom)
								AND ST_Area(b.the_geom::geography) <= ST_Area(intersected.the_geom::geography)
					) 
				)
		
		, ST_Multi(intersected.the_geom)  ) as the_geom
FROM intersected )

SELECT osm_id, type, name, ST_Multi(ST_Union(the_geom)) as the_geom FROM dirty
WHERE ST_IsEmpty(dirty.the_geom) = false
GROUP BY osm_id, type, name
 ;