Changes between Version 60 and Version 61 of CLC Join

Timestamp:

Jun 1, 2010 3:33:42 PM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -146 +146 @@
          * seawater_52
 
-Importantly this request will provide repeated lines with the same content it is this necessary to add a ''' DISTINCT ON ''' clause.
-
-{{{
-#!sql
-SELECT gid,code_00, id,round(area) as area FROM clc.clipped_Bretagne1 order by gid, code_00 limit 10;
--- here I'm keeping the surface so that we will b able to calculate for any basin
--- or group of basins a percentage of surface according to the basin surface
+Importantly this request will provide repeated lines with the same content it is this necessary to add a ''' DISTINCT ON ''' clause. I had forgotten to include the sum() and group by clause, for instance urban will correspond to the sum of lines whith code_00 111 112 121 122 123 124 131 132 133. 
+
+{{{
+#!sql
+
 DROP TABLE IF EXISTS clc.surf_area;
 CREATE TABLE clc.surf_area AS (
@@ -170 +168 @@
          FROM (
         SELECT  gid from clc.clipped_bretagne1  ) as init        
-        FULL OUTER JOIN (SELECT gid,area AS urban_11_13,code_00 FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,sum(area) AS urban_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' ) AS urban
+                        OR substring(code_00 from 1 for 2)='13' 
+                        GROUP BY gid) AS urban
                        on (init.gid) =(urban.gid)               
-        FULL OUTER JOIN (SELECT gid,area AS green_urban_14,code_00  FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='14') AS green_urban
+        FULL OUTER JOIN (SELECT gid,sum(area) AS green_urban_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
-        FULL OUTER JOIN (SELECT gid,area AS arable_land_21,code_00   FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='21') AS arable_land
+        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,area AS plantations_22, code_00  FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='22') AS plantations
+        FULL OUTER JOIN (SELECT gid, sum(area) AS plantations_22 FROM clc.clipped_bretagne1 WHERE 
+                        substring(code_00 from 1 for 2)='22'
+                        GROUP BY gid) AS plantations
                         on plantations.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS pastures_23 ,code_00 FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='23') AS pastures
+        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,area AS crops_natural_24 ,code_00  FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='24') AS crops_natural
+        FULL OUTER JOIN (SELECT gid, sum(area) AS crops_natural_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
-        FULL OUTER JOIN (SELECT gid,area AS forest_31,code_00   FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='31') AS forest
-                        on forest.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS natural_32_33 ,code_00  FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,sum(area) AS forest_31 FROM clc.clipped_bretagne1 WHERE 
+                        substring(code_00 from 1 for 2)='31'
+                        GROUP BY gid) AS forest
+                        ON forest.gid =init.gid
+        FULL OUTER JOIN (SELECT gid,sum(area) AS natural_32_33 FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='32'
-                        OR  substring(code_00 from 1 for 2)='33') AS nature
-                        on nature.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS wetlands_4 ,code_00  FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 1)='4') AS wetlands
+                        OR  substring(code_00 from 1 for 2)='33'
+                        GROUP BY gid) AS nature
+                        ON nature.gid =init.gid
+        FULL OUTER JOIN (SELECT gid, sum(area) AS wetlands_4  FROM clc.clipped_bretagne1 WHERE 
+                        substring(code_00 from 1 for 1)='4'
+                        GROUP BY gid) AS wetlands
                         on wetlands.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS water_51 ,code_00  FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='51') AS waterbodies
+        FULL OUTER JOIN (SELECT gid,sum(area) AS water_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,area AS seawater_52 ,code_00  FROM clc.clipped_bretagne1 WHERE 
-                        substring(code_00 from 1 for 2)='52') AS seawater
+        FULL OUTER JOIN (SELECT gid,sum(area) AS seawater_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
-ALTER TABLE clc.surf_area ADD CONSTRAINT c_pk_gid_surf_area PRIMARY KEY (gid);
 }}}
 {{{