Changes between Version 61 and Version 62 of CLC Join

Timestamp:

Jun 1, 2010 3:51:49 PM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -154 +154 @@
 CREATE TABLE clc.surf_area AS (
 SELECT DISTINCT ON (init.gid) init.gid,
-        urban_11_13,
-         green_urban_14,
+        artificial_surfaces_11_13,
+        artificial_vegetated_14,
          arable_land_21,
-         plantations_22,
+         permanent_crops_22,
          pastures_23,
-         crops_natural_24,
+         heterogeneous_agricultural_24,
          forest_31,
          natural_32_33,
          wetlands_4,
-         water_51 ,
-         seawater_52
+         inland_waterbodies_51 ,
+         marine_water_52
         -- SELECT * 
          FROM (
         SELECT  gid from clc.clipped_bretagne1  ) as init        
-        FULL OUTER JOIN (SELECT gid,sum(area) AS urban_11_13 FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='11' 
                         OR  substring(code_00 from 1 for 2)='12'
                         OR substring(code_00 from 1 for 2)='13' 
-                        GROUP BY gid) AS urban
-                       on (init.gid) =(urban.gid)               
-        FULL OUTER JOIN (SELECT gid,sum(area) AS green_urban_14 FROM clc.clipped_bretagne1 WHERE 
+                        GROUP BY gid) AS artificial_surfaces
+                       on (init.gid) =(artificial_surfaces.gid)         
+        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_vegetated_14 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='14'
-                        GROUP BY gid) AS green_urban
-                        on green_urban.gid =init.gid
+                        GROUP BY gid) AS artificial_vegetated
+                        on artificial_vegetated.gid =init.gid
         FULL OUTER JOIN (SELECT gid,sum(area) AS arable_land_21 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='21'
                         GROUP BY gid) AS arable_land
                         on arable_land.gid =init.gid
-        FULL OUTER JOIN (SELECT gid, sum(area) AS plantations_22 FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid, sum(area) AS permanent_crops_22 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='22'
-                        GROUP BY gid) AS plantations
-                        on plantations.gid =init.gid
+                        GROUP BY gid) AS permanent_crops
+                        on permanent_crops.gid =init.gid
         FULL OUTER JOIN (SELECT gid,sum(area) AS pastures_23 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='23'
                         GROUP BY gid) AS pastures
                         on pastures.gid =init.gid
-        FULL OUTER JOIN (SELECT gid, sum(area) AS crops_natural_24 FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid, sum(area) AS heterogeneous_agricultural_24 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='24'
-                        GROUP BY gid) AS crops_natural
-                        on crops_natural.gid =init.gid
+                        GROUP BY gid) AS heterogeneous_agricultural
+                        on heterogeneous_agricultural.gid =init.gid
         FULL OUTER JOIN (SELECT gid,sum(area) AS forest_31 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='31'
@@ -207 +207 @@
                         GROUP BY gid) AS wetlands
                         on wetlands.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,sum(area) AS water_51 FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,sum(area) AS inland_waterbodies_51 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='51'
                         GROUP BY gid) AS waterbodies
                         on waterbodies.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,sum(area) AS seawater_52 FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,sum(area) AS marine_water_52 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='52'
-                        GROUP BY gid) AS seawater
-                        on seawater.gid =init.gid); --375 ms
+                        GROUP BY gid) AS marine_water
+                        on marine_water.gid =init.gid); --375 ms
+ALTER TABLE clc.surf_area ADD CONSTRAINT c_pk_gid_surf_area PRIMARY KEY (gid);
 }}}
 {{{
@@ -227 +228 @@
         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 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 plantations_22 IS NOT NULL THEN plantations_22/1e6 
-        ELSE 0
-        END AS plantations_22,
+        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 crops_natural_24 IS NOT NULL THEN crops_natural_24/1e6
-        ELSE 0
-        END AS crops_natural_24,
+        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
@@ -254 +255 @@
         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,
+        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      
@@ -274 +275 @@
 {{{
 #!sql
+DROP TABLE IF EXISTS clc.surf_area_analyse;
 CREATE TABLE clc.surf_area_analyse AS( 
 SELECT 
@@ -279 +281 @@
         wso1_id,
         catchment_area,
-        urban_11_13+
-         green_urban_14+
+        artificial_surfaces_11_13+
+         artificial_vegetated_14+
          arable_land_21+
-         plantations_22+
+         permanent_crops_22+
          pastures_23+
-         crops_natural_24+
+         heterogeneous_agricultural_24+
          forest_31+
          natural_32_33+
          wetlands_4+
-         water_51 +
-         seawater_52 as sum_clc_area ,
-        (urban_11_13+
-         green_urban_14+
+         inland_waterbodies_51 +
+         marine_water_52 as sum_clc_area ,
+        (artificial_surfaces_11_13+
+         artificial_vegetated_14+
          arable_land_21+
-         plantations_22+
+         permanent_crops_22+
          pastures_23+
-         crops_natural_24+
+         heterogeneous_agricultural_24+
          forest_31+
          natural_32_33+
          wetlands_4+
-         water_51 +
-         seawater_52)/catchment_area AS pourc_clc,
+         inland_waterbodies_51 +
+         marine_water_52)/catchment_area AS pourc_clc,
          the_geom
          FROM clc.surf_area1);
@@ -309 +311 @@
 CREATE INDEX indexclc00area_analyse ON clc.surf_area_analyse
   USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
 }}}