Changes between Version 56 and Version 57 of CLC Join

Timestamp:

Jun 1, 2010 2:38:52 PM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -206 +206 @@
 
 }}}
+
+{{{
+SELECT 
+        r.gid,
+        area/1e6 as catchment_area,
+        CASE WHEN urban_11_13 IS NOT NULL THEN urban_11_13/1e6
+        ELSE 0
+        END AS urban_11_13,
+        CASE WHEN green_urban_14 IS NOT NULL THEN green_urban_14/1e6 
+        ELSE 0
+        END AS green_urban_14,
+        CASE WHEN arable_land_21 IS NOT NULL THEN arable_land_21/1e6 
+        ELSE 0
+        END AS arable_land_21,
+        CASE WHEN plantations_22 IS NOT NULL THEN plantations_22/1e6 
+        ELSE 0
+        END AS plantations_22,
+        CASE WHEN pastures_23 IS NOT NULL THEN pastures_23/1e6 
+        ELSE 0
+        END AS pastures_23,
+        CASE WHEN crops_natural_24 IS NOT NULL THEN crops_natural_24/1e6
+        ELSE 0
+        END AS crops_natural_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 water_51 IS NOT NULL THEN water_51 /1e6 
+        ELSE 0
+        END AS water_51,
+        CASE WHEN  seawater_52 IS NOT NULL THEN seawater_52/1e6 
+        ELSE 0
+        END AS seawater_52      
+          FROM clc.pourc_area p
+JOIN ccm21.catchments c ON c.gid=p.gid
+JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id;
+
+SELECT 
+        gid,
+        catchment_area,
+        urban_11_13+
+         green_urban_14+
+         arable_land_21+
+         plantations_22+
+         pastures_23+
+         crops_natural_24+
+         forest_31+
+         natural_32_33+
+         wetlands_4+
+         water_51 +
+         seawater_52 as sum_clc_area FROM(
+SELECT 
+        r.gid,
+        area/1e6 as catchment_area,
+        CASE WHEN urban_11_13 IS NOT NULL THEN urban_11_13/1e6
+        ELSE 0
+        END AS urban_11_13,
+        CASE WHEN green_urban_14 IS NOT NULL THEN green_urban_14/1e6 
+        ELSE 0
+        END AS green_urban_14,
+        CASE WHEN arable_land_21 IS NOT NULL THEN arable_land_21/1e6 
+        ELSE 0
+        END AS arable_land_21,
+        CASE WHEN plantations_22 IS NOT NULL THEN plantations_22/1e6 
+        ELSE 0
+        END AS plantations_22,
+        CASE WHEN pastures_23 IS NOT NULL THEN pastures_23/1e6 
+        ELSE 0
+        END AS pastures_23,
+        CASE WHEN crops_natural_24 IS NOT NULL THEN crops_natural_24/1e6
+        ELSE 0
+        END AS crops_natural_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 water_51 IS NOT NULL THEN water_51 /1e6 
+        ELSE 0
+        END AS water_51,
+        CASE WHEN  seawater_52 IS NOT NULL THEN seawater_52/1e6 
+        ELSE 0
+        END AS seawater_52      
+          FROM clc.pourc_area p
+JOIN ccm21.catchments c ON c.gid=p.gid
+JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id) AS sub;
+}}}
+
 = A whole country =
 {{{