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', 'basin') THEN 'water_bodies'
            WHEN type IN ('allotments') THEN 'artif_non_agri_vegetated'
            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;

