WITH
intersected as (
	SELECT 
		osm_id,
    	type,
		name,
    	-- intersected geometries
    	CASE
    		-- Europe -29.900003,33.325714,45.258668,71.908448
    		WHEN 
    			naturals.the_geom && ST_MakeEnvelope( -29.900003,33.325714,45.258668,71.908448, 4326) -- a && b : a and b's bbox intersect
    			AND
    			naturals.the_geom @ ST_MakeEnvelope( -29.900003,33.325714,45.258668,71.908448, 4326) -- a @ b : a completly contained by b
    		THEN ST_MakeValid(naturals.the_geom)
    		-- only do the real intersection work if polygons are not completely contained in bbox
			ELSE ST_Multi(
      				ST_Intersection(ST_MakeValid(naturals.the_geom), ST_MakeEnvelope( -29.900003,33.325714,45.258668,71.908448, 4326) )
      			 )
    	END as the_geom,
		CASE
			WHEN naturals.osm_id > 0 THEN (SELECT ways.tstamp FROM ways WHERE ways.id = naturals.osm_id)
			ELSE (SELECT relations.tstamp FROM relations WHERE relations.id = - naturals.osm_id)
		END AS "timestamp"			
    FROM public.naturals
    WHERE ST_Intersects(naturals.the_geom, ST_MakeEnvelope(-29.900003,33.325714,45.258668,71.908448, 4326))
    AND (type = 'residential' OR type = 'garages' OR type = 'railway' OR type = 'port' OR type = 'harbour' OR type = 'industrial' OR type = 'commercial' OR type = 'retail' OR type = 'lock' OR type = 'marina'
	OR type = 'quarry' OR type = 'landfill' OR type = 'brownfield' OR type = 'construction' OR type = 'allotments' OR type = 'cemetery' OR type = 'garden' OR type = 'raceway' OR type = 'golf_course' OR type = 'park' 
	OR type = 'common' OR type = 'pitch' OR type = 'playground' OR type = 'recreation_ground' OR type = 'sports_centre' OR type = 'stadium' OR type = 'village_green' OR type = 'zoo' OR type = 'track'
	OR type = 'farm' OR type = 'farmland' OR type = 'farmyard' OR type = 'greenhouse' OR type = 'greenhouse_horticulture' OR type = 'vineyard' OR type = 'orchard' OR type = 'meadow' OR type = 'forest' 
	OR type = 'wood' OR type = 'grass' OR type = 'greenfield' OR type = 'scrub' OR type = 'heath' OR type = 'grassland' OR type = 'cliff' OR type = 'sand' OR type = 'scree' 
	OR type = 'beach' OR type = 'mud' OR type = 'glacier' OR type = 'fell' OR type = 'rock' OR type = 'wetland' OR type = 'march' OR type = 'salt_pond' OR type = 'tidal' OR type = 'water' OR type = 'riverbank' 
	OR type = 'reservoir' OR type = 'basin' OR type = 'dock' OR type = 'canal' OR type = 'pond'
)),
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,
		"timestamp"
FROM intersected )

SELECT osm_id, type, name,
		CASE				
			WHEN type ='residential' OR type='garages' THEN '1.1'
			WHEN type = 'railway' OR type = 'port' OR type = 'harbour' OR type = 'industrial' OR type = 'commercial' OR type = 'retail' OR type = 'lock' OR type = 'marina' THEN '1.2'
			WHEN type = 'quarry' OR type = 'landfill' OR type = 'brownfield' OR type = 'construction' THEN '1.3'
			WHEN type = 'allotments' OR type = 'cemetery' OR type = 'garden' OR type = 'raceway' OR type = 'golf_course' OR type = 'park' OR type = 'common' OR type = 'pitch' OR type = 'playground' OR type = 'recreation_ground' OR type = 'sports_centre' OR type = 'stadium' OR type = 'village_green' OR type = 'zoo' OR type = 'track' THEN '1.4'
			WHEN type = 'farm' OR type = 'farmland' OR type = 'farmyard' OR type = 'greenhouse' OR type = 'greenhouse_horticulture' THEN '2.1'
			WHEN type = 'vineyard' OR type = 'orchard' THEN '2.2'
			WHEN type = 'meadow' THEN '2.3'
			WHEN type = 'forest' OR type = 'wood' THEN '3.1'
			WHEN type = 'grass' OR type = 'greenfield' OR type = 'scrub' OR type = 'heath' OR type = 'grassland' THEN '3.2'
			WHEN type = 'cliff' OR type = 'sand' OR type = 'scree' OR type = 'beach' OR type = 'mud' OR type = 'glacier' OR type = 'fell' OR type = 'rock' THEN '3.3'
			WHEN type = 'wetland' OR type = 'march' THEN '4.1'
			WHEN type = 'salt_pond' OR type = 'tidal' THEN '4.2'
			WHEN type = 'water' OR type = 'riverbank' OR type = 'reservoir' OR type = 'basin' OR type = 'dock' OR type = 'canal' OR type = 'pond' THEN '5'
		END as class_code,

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