WITH 
intersected as (
	SELECT 
    	type,
    	-- 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 nat.the_geom
    		-- only do the real intersection work if polygons are not completely contained in bbox
			ELSE ST_Multi(
      				ST_Intersection(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))
    -- LIMIT 100
),
classes AS(
    SELECT 
        CASE 
            WHEN type IN ('water', 'riverbank', 'reservoir', 'basin') THEN 'water_bodies'
            WHEN type IN ('greenhouse_horticulture', 'greenhouse', 'farmland', 'farm', 'farmyard') THEN 'arable_land'
			WHEN type IN ('stadium', 'recreation_ground', 'golf_course', 'water_park', 'common', 'sports_center', 'playground', 'pitch', 'village_green', 'allotments', 'cemetery', 'park', 'zoo', 'marina', 'track', 'garden') THEN 'artif_non_agri_vegetated'
			WHEN type IN ('conservation', 'forest', 'wood') THEN 'forests'
			WHEN type IN ('railway', 'military', 'aerodrome', 'industrial', 'commercial', 'retail', 'harbour', 'port') THEN 'ind_com'
			WHEN type IN ('quarry', 'construction', 'landfill', 'brownfield') THEN 'mine_dump_constr'
			WHEN type IN ('fell', 'sand', 'scree', 'beach', 'mud', 'glacier', 'rock') THEN 'open_spaces_no_veg'
			WHEN type IN ('meadow') THEN 'pastures'
			WHEN type IN ('vineyard', 'orchard') THEN 'permanent_crops'
			WHEN type IN ('grass', 'greenfield', 'scrub', 'heath', 'grassland') THEN 'scrub_herbs'
			WHEN type IN ('garages', 'residential') THEN 'urban_fabric'
			WHEN type IN ('marsh', 'wetland') THEN 'wetlands'
			WHEN type IN ('salt_pond', 'tidal') THEN 'coastal_wetlands'		
            ELSE 'other' 
        END as class,
        ST_Area(the_geom::geography) as area
        FROM intersected
        -- LIMIT 10
    )
   
SELECT class, SUM(area) as sum_area 
	FROM classes 
    GROUP BY class
    ORDER BY sum_area DESC;
