Changes between Version 5 and Version 6 of OriaEda

Timestamp:

Nov 23, 2010 9:14:40 AM (14 years ago)

Author:

cedric

Comment:

--

OriaEda

@@ -12 +12 @@
 -- Une table pour les vues dans Qgis
 create table ccm21.riversegments_spain as (
-        select * from ccm21.riversegments where wso_id in ((select wso_id from europe.wso where area='Spain'
+        select * from ccm21.riversegments where wso_id in ((select wso_id from europe.wso where area='Spain'
 except (select  wso_id  from europe.wso where area='France')) --2339
 intersect (select wso_id from ccm21.seaoutlets where area_cd='A1'))); --348);
@@ -34 +34 @@
 drop table if exists oria.estaciones_st;
 create table oria.estaciones_st (  
-        st_id character varying(10),
-        st_ecoregion character varying(10),
-        st_river character varying(40),
-        st_name text,
-        st_x numeric,
-        st_y numeric,
-        st_z numeric,
-        st_pk_des numeric,
-        st_date date,
-        st_21fpcomun_num integer,
-        st_nha numeric);
+        st_id character varying(10),
+        st_ecoregion character varying(10),
+        st_river character varying(40),
+        st_name text,
+        st_x numeric,
+        st_y numeric,
+        st_z numeric,
+        st_pk_des numeric,
+        st_date date,
+        st_21fpcomun_num integer,
+        st_nha numeric);
 alter table oria.estaciones_st add constraint  pk_st_id primary key (st_id);
 set client_encoding to 'latin1';
@@ -51 +51 @@
 drop table if exists oria.operation_op;
 create table oria.operation_op (
-        op_date date,
-        op_st_id character varying(10),
-        op_op_id character varying(10) primary key,
-        st_x numeric,
-        st_y numeric,
-        st_z numeric,
-        st_pk_des numeric,
-        op_surf numeric,
-        op_methodest character varying(30),
-        op_fishingobj character varying(30),
-        op_nbpas integer,
-        op_effort numeric,
-        op_densite numeric,
-        op_nbtot numeric,
-        op_nbp1 numeric,
-        op_nbp2 numeric,
-        op_nbp3 numeric,
-        op_nb150 numeric,
-        op_nb150_300 numeric,
-        op_nb300_450 numeric,
-        op_nb_450_600 numeric,
-        op_nb_600 numeric,
-        op_glass_eel_transport boolean);
+        op_date date,
+        op_st_id character varying(10),
+        op_op_id character varying(10) primary key,
+        st_x numeric,
+        st_y numeric,
+        st_z numeric,
+        st_pk_des numeric,
+        op_surf numeric,
+        op_methodest character varying(30),
+        op_fishingobj character varying(30),
+        op_nbpas integer,
+        op_effort numeric,
+        op_densite numeric,
+        op_nbtot numeric,
+        op_nbp1 numeric,
+        op_nbp2 numeric,
+        op_nbp3 numeric,
+        op_nb150 numeric,
+        op_nb150_300 numeric,
+        op_nb300_450 numeric,
+        op_nb_450_600 numeric,
+        op_nb_600 numeric,
+        op_glass_eel_transport boolean);
 alter table oria.operation_op add constraint  fk_op_st_id foreign key (op_st_id) references oria.estaciones_st(st_id);
 copy oria.operation_op  from 'c:/base/operation.csv' with csv delimiter as ';' header; 
+alter table oria.operation_op drop column  st_x;
+alter table oria.operation_op drop column         st_y ;
+alter table oria.operation_op drop column         st_z ;
+alter table oria.operation_op drop column         st_pk_des ;
+-- projection sur le SRID 3035
+SELECT AddGeometryColumn('oria', 'estaciones_st','the_geom', 3035,'POINT',2); 
+UPDATE oria.estaciones_st   SET the_geom=ST_Transform(PointFromText('POINT(' || st_x || ' ' || st_y || ')',23030),3035);
+ALTER TABLE oria.estaciones_st SET WITH OIDS;
+
+CREATE INDEX indexestaciones_st ON oria.estaciones_st
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+
+-- capture des stations de pêche
+DROP TABLE IF EXISTS oria.oria_ccm_500;
+CREATE TABLE oria.oria_ccm_500 as (
+        SELECT distinct on (st_id ) st_id , gid, wso1_id, min(distance) as distance, the_geom FROM (
+               SELECT st_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
+               FROM oria.estaciones_st As s
+               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500)
+               WHERE s.the_geom IS NOT NULL
+               ORDER BY st_id) AS sub 
+        GROUP BY st_id, gid, wso1_id,  distance,the_geom
+);
+alter table oria.oria_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 '', 'oria', 'oria_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 oria.oria_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+alter table oria.oria_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
+alter table oria.oria_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+alter table oria.oria_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
+CREATE INDEX indexoria_ccm_500 ON oria.oria_ccm_500
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
 }}}