Changes between Version 12 and Version 13 of Meuse dbeel

Timestamp:

Jun 6, 2014 4:12:46 PM (11 years ago)

Author:

laura

Comment:

--

Meuse dbeel

@@ -593 +593 @@
  [[Image(source:data/Docs/trac/Meuse/projection_op_meuse.jpg)]] [[BR]]
  [[Image(source:data/Docs/trac/Meuse/projection_op_meuse_zoom.jpg)]] [[BR]]
+
+{{{#!sql
+
+-----------------------------------------------------------------------------
+-- Creation de la table stations_ccm_500 avec l'id station et l'id de la CCM
+-----------------------------------------------------------------------------
+
+-- Table pour les stations
+-- Juste la Meuse belge car on doit attendre les données de pêches pour la hollande et la France
+  DROP TABLE IF EXISTS dbeel.stations_ccm_500;
+CREATE TABLE belge.stations_ccm_500 as (
+        SELECT distinct (site) site, gid, wso1_id, min(distance) as distance, 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 
+               WHERE s.the_geom IS NOT NULL
+               ORDER BY site) AS sub 
+               GROUP BY site, gid, gid, wso1_id,  distance,the_geom -- 516 lignes pour Meuse
+);
+
+alter table belge.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 '', 'belge', 'stations_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM oria.oria_ccm_500 LIMIT 1;
+
+-- creation d'index, clé primaire, et constraintes qui vont bien
+alter table belge.stations_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table belge.stations_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table belge.stations_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table belge.stations_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexbelge_ccm_500 ON belge.stations_ccm_500
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+  
+-- select * from belge.stations_ccm_500
+
+
+}}}