back to first page..
back to RHT, RHT PATE, PATE Anguille

Station hydrographique Banque HYDRO

---Creation du schéma Pate
create schema pate;

---Création de la table station_hydro pour récupérer ensuite les id_drain correspondants
drop table if exists pate.station_hydro;
create table pate.station_hydro (
 nom character varying(80),
 X_lambertII numeric,
 Y_lambertII numeric,
 code_station character varying(30),
 nom_station character varying(80)
);
set client_encoding to 'latin1';
copy pate.station_hydro from 'D:/CelineJouanin/PATE/Station_Hydro.csv' with csv header delimiter as ';' NULL AS 'NA';--20 lines

select * from pate.station_hydro;  ---problèmes tous les noms ne sont pas écrits

update pate.station_hydro set code_station='J9300610' where nom_station='Vilaine_(Rieux)';

---Changement de projections
---Lambert II étendut (srid 27572) --> srid 3035
SELECT AddGeometryColumn('pate', 'station_hydro','the_geom', 3035,'POINT',2); 
UPDATE pate.station_hydro SET the_geom=ST_Transform(PointFromText('POINT(' || x_lambertii || ' ' || y_lambertii || ')',27572),3035) ;

-- Attribution des id_drains 
alter table pate.station_hydro add column id_drain integer;
UPDATE pate.station_hydro SET id_drain=sub.id_drain from (select r.id_drain, s.code_station from pate.station_hydro s inner join rht.rhtvs2 r 
on st_dwithin(s.the_geom,r.the_geom,300)) as sub where station_hydro.code_station=sub.code_station;

UPDATE pate.station_hydro SET id_drain='308381' where code_station='I5221010';
UPDATE pate.station_hydro SET id_drain='105760' where code_station='P8462510';
UPDATE pate.station_hydro SET id_drain='20375' where code_station='Y2142010';
UPDATE pate.station_hydro SET id_drain='121008' where code_station='Q7002910';
UPDATE pate.station_hydro SET id_drain='119550' where code_station='Q7412910';
UPDATE pate.station_hydro SET id_drain='209735' where code_station='J8502310';
UPDATE pate.station_hydro SET id_drain='122242' where code_station='Q6332510';

-- Récupération des attributs Rht --
drop table if exists pate.station_hydro_rhtvs2;
create table pate.station_hydro_rhtvs2 as (
        select s.*, a.module_icinf, a.module, a.module_icsup, a.surf_bv, r.dmer/1000 as dmer, r.dsource/1000 as dsource from pate.station_hydro s 
        join rht.rhtvs2 r on s.id_drain=r.id_drain
        join rht.attributs_rht_fev_2011_vs2 a on s.id_drain=a.id_drain);

alter table pate.station_hydro_rhtvs2 add column drelative numeric;
update pate.station_hydro_rhtvs2 set drelative=dmer/(dmer+dsource)*100;

COPY pate.station_hydro_rhtvs2 TO 'D:/CelineJouanin/PATE/Station_Hydro_rhtvs2.csv' with csv header delimiter as ';'