Changes between Version 36 and Version 37 of CLC Join

Timestamp:

Jun 1, 2010 12:22:22 PM (15 years ago)

Author:

cedric

Comment:

--

CLC Join

@@ -63 +63 @@
 [[Image(source:data/Docs/trac/coupure des couvertures anthropisées.png,600px)]]  [[BR]]
 == Grouping the surface by gid and code_00 ==
-The next steps is to group the different layers, we use the ''' ST_Multi(ST_Collect(()) ''' function to group the POLYGONS into MULTIPOLYGONS (one per line) and before this we do the reverse by transforming all MUTIPOLYGONS into POLYGONS using ''' ST_Dump() '''. 
+The next steps is to group the different layers, we use the ''' ST_Multi(ST_Collect(()) ''' function to group the POLYGONS into MULTIPOLYGONS (one per line) and before this we do the reverse by transforming all MUTIPOLYGONS into POLYGONS using ''' ST_Dump() '''. So finally calculating the area using ''' ST_Area ''' is a very quick and simple step. Note the unicity constraint wich ensures that we have only one code_00 per unit catchment.
+
+{{{
+--------------------------------------
+--------------------------------------
+--REGROUPEMENT
+--------------------------------------
+--------------------------------------
+
+DROP TABLE IF EXISTS clc.clipped_bretagne1;
+CREATE TABLE clc.clipped_bretagne1 AS (
+SELECT gid,code_00,
+           ST_Multi(ST_Collect(f.the_geom)) as the_geom
+         FROM (SELECT gid, code_00,(ST_Dump(the_geom)).geom As the_geom
+                                FROM
+                                 clc.clipped_bretagne
+                                ) As f
+GROUP BY gid,code_00); -- 5s
+ALTER TABLE clc.clipped_bretagne1 add column id serial PRIMARY KEY;
+alter table clc.clipped_bretagne1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table clc.clipped_bretagne1 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
+alter table clc.clipped_bretagne1 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+CREATE INDEX indexclc00clipped_bretagne1 ON clc.clipped_bretagne1
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+ALTER TABLE clc.clipped_bretagne1 add constraint c_ck_uk  UNIQUE(gid,code_00); -- contrainte d'unicité OK !
+
+
+ALTER TABLE clc.clipped_bretagne1 add column area numeric;
+UPDATE clc.clipped_bretagne1 set area=ST_Area(the_geom); -- 9s
+}}}