Changes between Version 17 and Version 18 of Meuse dbeel

Timestamp:

Jun 16, 2014 2:55:40 PM (11 years ago)

Author:

laura

Comment:

--

Meuse dbeel

@@ -1244 +1244 @@
 
 }}}
+
+
+{{{#!sql
+
+---------------------------------------------------------------------------------------------------------------
+---------------- Table station_CCM_500 france_Belge_Meuse----------------
+---------------------------------------------------------------------------------------------------------------
+
+ALTER TABLE onema.station_onema RENAME COLUMN st_id TO site;
+ALTER TABLE belge.stationdbeel ALTER COLUMN site TYPE bigint;
+ALTER TABLE onema.station_onema ALTER COLUMN site TYPE character varying;
+
+DROP TABLE IF EXISTS dbeel.meuse_stations_ccm_500;
+CREATE TABLE dbeel.meuse_stations_ccm_500 as (
+         SELECT n.site, n.gid, n.wso1_id, min(n.distance) as distance, n.the_geom FROM 
+                (  
+                SELECT site, gid, wso1_id, distance, s.the_geom 
+                FROM dbeel.observation_places_CCM_500 s
+                join belge.stationdbeel o ON o.op_id=s.op_id --516 lignes
+                union ALL
+                        SELECT p.site, gid, m.wso1_id, m.distance, p.the_geom FROM (
+                               select o.gid, o.wso1_id, o.op_id,  o.distance from dbeel.observation_places_CCM_500 o
+                               JOIN europe.wso1 e ON o.wso1_id=e.wso1_id where area='Meuse') m
+                join onema.station_onema p ON p.op_id=m.op_id ) n --901 lignes
+                
+         WHERE n.the_geom IS NOT NULL  
+         GROUP BY n.site, n.gid, n.wso1_id, distance, n.the_geom -- 901
+         order by n.wso1_id, n.site 
+);
+select * from dbeel.meuse_stations_ccm_500
+       
+alter table dbeel.meuse_stations_ccm_500 add column id serial;
+-- mise à jour de la table geometry_columns
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'dbeel', 'meuse_stations_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM dbeel.meuse_stations_ccm_500 LIMIT 1;
+
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table dbeel.meuse_stations_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table dbeel.meuse_stations_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table dbeel.meuse_stations_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table dbeel.meuse_stations_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexmeuse_FB_ccm_500 ON dbeel.meuse_stations_ccm_500
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
+}}}