Changes between Version 54 and Version 55 of CLC Join

Timestamp:

Jun 1, 2010 2:37:23 PM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -152 +152 @@
 DROP TABLE IF EXISTS clc.pourc_area;
 CREATE TABLE clc.pourc_area AS (
-SELECT   init.gid,
-         urban_11_13,
+SELECT DISTINCT ON (init.gid) init.gid,
+        urban_11_13,
          green_urban_14,
          arable_land_21,
@@ -164 +164 @@
          water_51 ,
          seawater_52
+        -- SELECT * 
          FROM (
-        SELECT distinct ON (gid) gid from clc.clipped_bretagne1 ) as init        
-        FULL OUTER JOIN (SELECT gid,area AS urban_11_13 FROM clc.clipped_bretagne1 WHERE 
+        SELECT  gid from clc.clipped_bretagne1  ) as init        
+        FULL OUTER JOIN (SELECT gid,area,code_00 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
-                       on init.gid =urban.gid           
-        FULL OUTER JOIN (SELECT gid,area AS green_urban_14  FROM clc.clipped_bretagne1 WHERE 
+                       on (init.gid) =(urban.gid)               
+        FULL OUTER JOIN (SELECT gid,area,code_00 AS green_urban_14  FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='14') AS green_urban
                         on green_urban.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS arable_land_21  FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,area,code_00 AS arable_land_21  FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='21') AS arable_land
                         on arable_land.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS plantations_22  FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,area,code_00 AS plantations_22  FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='22') AS plantations
                         on plantations.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS pastures_23  FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,area,code_00 AS pastures_23  FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='23') AS pastures
                         on pastures.gid =init.gid
-        FULL OUTER JOIN (SELECT gid,area AS crops_natural_24  FROM clc.clipped_bretagne1 WHERE 
+        FULL OUTER JOIN (SELECT gid,area,code_00 AS crops_natural_24  FROM clc.clipped_bretagne1 WHERE 
                         substring(code_00 from 1 for 2)='24') AS crops_natural
                         on crops_natural.gid =init.gid
@@ -202 +203 @@
                         substring(code_00 from 1 for 2)='52') AS seawater
                         on seawater.gid =init.gid); --375 ms
+ALTER TABLE clc.pourc_area ADD CONSTRAINT c_pk_gid_pourc_area PRIMARY KEY (gid);
+
 }}}
 = A whole country =