Changes between Version 94 and Version 95 of CLC Join

Timestamp:

Jun 11, 2010 9:01:38 AM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -1083 +1083 @@
 ALTER TABLE clc.surf_area ADD CONSTRAINT c_pk_gid_surf_area PRIMARY KEY (gid);
 }}}
+{{{
+DROP TABLE IF EXISTS clc.surf_area_final;
+CREATE TABLE clc.surf_area_final AS( 
+SELECT 
+        r.gid,
+        area/1e6 as catchment_area,
+        CASE WHEN artificial_surfaces_11_13 IS NOT NULL THEN artificial_surfaces_11_13/1e6
+        ELSE 0
+        END AS artificial_surfaces_11_13,
+        CASE WHEN artificial_vegetated_14 IS NOT NULL THEN artificial_vegetated_14/1e6 
+        ELSE 0
+        END AS artificial_vegetated_14,
+        CASE WHEN arable_land_21 IS NOT NULL THEN arable_land_21/1e6 
+        ELSE 0
+        END AS arable_land_21,
+        CASE WHEN permanent_crops_22 IS NOT NULL THEN permanent_crops_22/1e6 
+        ELSE 0
+        END AS permanent_crops_22,
+        CASE WHEN pastures_23 IS NOT NULL THEN pastures_23/1e6 
+        ELSE 0
+        END AS pastures_23,
+        CASE WHEN heterogeneous_agricultural_24 IS NOT NULL THEN heterogeneous_agricultural_24/1e6
+        ELSE 0
+        END AS heterogeneous_agricultural_24,
+        CASE WHEN forest_31 IS NOT NULL THEN forest_31/1e6 
+        ELSE 0
+        END AS forest_31,
+        CASE WHEN natural_32_33 IS NOT NULL THEN natural_32_33/1e6 
+        ELSE 0
+        END AS natural_32_33,
+        CASE WHEN wetlands_4 IS NOT NULL THEN wetlands_4/1e6 
+        ELSE 0
+        END AS wetlands_4,
+        CASE WHEN inland_waterbodies_51 IS NOT NULL THEN inland_waterbodies_51 /1e6 
+        ELSE 0
+        END AS inland_waterbodies_51,
+        CASE WHEN  marine_water_52 IS NOT NULL THEN marine_water_52/1e6 
+        ELSE 0
+        END AS marine_water_52,
+        c.wso1_id,
+        c.the_geom      
+FROM clc.surf_area p
+JOIN ccm21.catchments c ON c.gid=p.gid
+JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id
+);
+}}}
 saving the result of the request
 {{{