CREATE VIEW osmlanduse.osmlanduse AS
-- filters according to v4/osm_lulc_1.xml SLD
SELECT 
	*,
	(CASE
                        -- Urban fabric 11
						WHEN type ='residential' OR type='garages' OR type='pedestrian' THEN 11
                        -- Industrial, commercial and transport units 12
                        WHEN 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 = 'aerodrome' OR type = 'rest_area' OR type = 'apron' OR type = 'runway' OR type = 'parking' THEN 12
                        -- Mine, dump and construction sites 13
                        WHEN type = 'quarry' OR type = 'landfill' OR type = 'brownfield' OR type = 'construction' THEN 13
                        -- Artificial, non-agricultural vegetated areas 14
                        WHEN type = 'camp_site' 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' THEN 14
                        -- arable land 21
                        WHEN type = 'farm' OR type = 'farmland' OR type = 'farmyard' OR type = 'greenhouse' OR type = 'greenhouse_horticulture' THEN 21
                        -- Permanent crops 22
                        WHEN type = 'vineyard' OR type = 'orchard' OR type = 'plant_nursery' THEN 22
                        -- Pastures 23
                        WHEN type = 'meadow' THEN 23
                        -- forests
                        WHEN type = 'forest' OR type = 'wood' THEN 31
                        -- Shrub and/or herbaceous vegetation associations
                        WHEN type = 'grass' OR type = 'greenfield' OR type = 'scrub' OR type = 'heath' OR type = 'grassland' OR type = 'logging' THEN 32
                        -- Open spaces with little or no vegetation
                        WHEN 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 = 'desert' OR type = 'bare_rock' THEN 33
                        -- Wetlands 41
                        WHEN type = 'wetland' OR type = 'marsh' THEN 41
                        -- Coastal wetslands 42
                        WHEN type = 'salt_pond' OR type = 'tidal' THEN 42
                        -- waterbodies 5
                        WHEN type = 'water' OR type = 'riverbank' OR type = 'reservoir' OR type = 'basin' OR type = 'dock' OR type = 'canal' OR type = 'pond' THEN 5
                END)::smallint as class_code
FROM public.naturals
WHERE (
 type = 'residential' OR type = 'garages' OR type='pedestrian' 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 = 'aerodrome' OR type = 'rest_area' OR type = 'apron' OR type = 'runway' OR type = 'parking'
        OR type = 'quarry' OR type = 'landfill' OR type = 'brownfield' OR type = 'construction' OR type = 'camp_site' 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 = 'plant_nursery' 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 = 'logging' 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 = 'desert' OR type = 'bare_rock' OR type = 'wetland' OR type = 'marsh' 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'
        );