back to first page..
back toCookBook Eda
back to RHT

drop table if exists uga2010.wso1;
drop table if exists uga2010.wso;

drop table  if exists uga2010.id_drain;
     CREATE TABLE uga2010.id_drain (
     id serial PRIMARY KEY,
     gid integer,
     id_drain integer,
     uga varchar(25)
     );

 --Bretagne
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Bretagne') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Bretagne' where uga IS NULL --6468 lines


 --Loire
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Loire') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Loire' where uga IS NULL --27746 lines

 --Garonne
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Garonne') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Garonne' where uga IS NULL --19651 lines


 --Adour
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Adour') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Adour' where uga IS NULL --3891 lines

 --Artois-Picardie
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Artois-Picardie') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Artois-Picardie' where uga IS NULL --4061 lines

 --Rhône-Méditerranée
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Rhone-Mediterranee') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='RhoneMediterranee' where uga IS NULL --24999 lines

 --Corse
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Corse') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Corse' where uga IS NULL --1772 lines


 --Seine-Normandie
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Seine-Normandie') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Seine-Normandie' where uga IS NULL --19525 lines

 --Rhin
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Rhin') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Rhin' where uga IS NULL --4773 lines

 --Meuse
    insert into uga2010.id_drain(gid, id_drain) 
    select distinct on (id_drain, gid) gid, id_drain from rht.rht r
    join (SELECT ST_Union(f.the_geom) as singlegeom
        FROM uga2010.uga As f where libelle='Meuse') as sub
    ON ST_Intersects(sub.singlegeom,r.the_geom);

    UPDATE uga2010.id_drain set uga='Meuse' where uga IS NULL --1630 lines

CREATE INDEX indexugaid_drain
ON uga2010.id_drain
USING btree (id_drain);

insert into uga2010.id_drain(gid,id_drain,uga) values ('3067','3224','Rhone-Mediterranee'),('11638','11467','Rhone-Mediterranee'),('22455','22419','Rhone-Mediterranee'),('24629','24470','Corse'),('26919','26772','Corse'),('71671','501772','Rhin');   -- 6 lines
insert into uga2010.id_drain(gid,id_drain) values ('51479','301049'),('53501','303129'),('53521','303155'),('53471','303111'),('53481','303125'),('53531','303174'),('53571','303204'),('53695','303128'),('53710','303381'),('54108','303716'),('55321','304896'),('56267','305902'),('56268','305940'),('57509','307077'),('57623','307258'),('57860','307371'),('61605','311335'),('61606','311248'),('61639','311340'),('61759','311471'),('61766','311476'),('61767','311394'),('61885','311500'),('61904','311604'),('61922','311676'),('61924','311490'),('61925','311640'),('61939','311667');
UPDATE uga2010.id_drain set uga='Seine-Normandie' where uga IS NULL; --28 lines

delete from uga2010.id_drain where id_drain in ('30032','30036','30035','30040','6019','5959','2246');  -- 7 lines

alter table uga2010.id_drain rename to id_drain_uga

#TODO voir pb doublons

select count(id_drain), id_drain from uga2010.id_drain_uga group by id_drain order by count(id_drain);

delete from uga2010.id_drain_uga where id_drain in ('18683','24071','24081') and uga='Garonne';
delete from uga2010.id_drain_uga where id_drain in ('100206','100205') and uga='Loire';
delete from uga2010.id_drain_uga where id_drain in ('108801','108802','109121','109162','109163','109218') and uga='Adour';
delete from uga2010.id_drain_uga where id_drain in ('109156','109157') and uga='Garonne';
delete from uga2010.id_drain_uga where id_drain in ('110413') and uga='Garonne';
delete from uga2010.id_drain_uga where id_drain in ('110778','113250','113080','113105') and uga='Adour';
delete from uga2010.id_drain_uga where id_drain in ('119935','116117') and uga='Rhone-Mediterranee';
delete from uga2010.id_drain_uga where id_drain in ('113427') and uga='Garonne';
delete from uga2010.id_drain_uga where id_drain in ('201486','207681') and uga='Seine-Normandie';
delete from uga2010.id_drain_uga where id_drain in ('319461') and uga='Loire';
delete from uga2010.id_drain_uga where id_drain in ('212760') and uga='Loire';
delete from uga2010.id_drain_uga where id_drain in ('213176') and uga='Bretagne';
delete from uga2010.id_drain_uga where id_drain in ('121758') and uga='Garonne';
delete from uga2010.id_drain_uga where id_drain in ('228477') and uga='Garonne';
delete from uga2010.id_drain_uga where id_drain in ('234124') and uga='Rhone-Mediterranee';
delete from uga2010.id_drain_uga where id_drain in ('222020') and uga='Rhone-Mediterranee';
delete from uga2010.id_drain_uga where id_drain in ('311576') and uga='Bretagne';
delete from uga2010.id_drain_uga where id_drain in ('402982') and uga='Artois-Picardie';
delete from uga2010.id_drain_uga where id_drain in ('503639') and uga='Rhin';

UPDATE uga2010.id_drain_uga set uga='Adour' where id_drain in ('108801','108802');
UPDATE uga2010.id_drain_uga set uga='Garonne' where id_drain in ('100194','100151','100147','100123','100124','100102','109173','109156','109157');

RHT par UGA

drop table if exists rht.rhtvs2_Adour;
create table rht.rhtvs2_Adour as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Adour')); ---lines
CREATE INDEX indexid_drainAdour
ON rht.rhtvs2_Adour
USING btree (id_drain);

drop table if exists rht.rhtvs2_Bretagne;
create table rht.rhtvs2_Bretagne as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Bretagne')); ---6466lines
CREATE INDEX indexid_drainBretagne
ON rht.rhtvs2_Bretagne
USING btree (id_drain);

drop table if exists rht.rhtvs2_Garonne;
create table rht.rhtvs2_Garonne as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Garonne')); ---lines
CREATE INDEX indexid_drainGaronne
ON rht.rhtvs2_Garonne
USING btree (id_drain);

drop table if exists rht.rhtvs2_Rhin;
create table rht.rhtvs2_Rhin as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Rhin')); ---lines
CREATE INDEX indexid_drainRhin
ON rht.rhtvs2_Rhin
USING btree (id_drain);

drop table if exists rht.rhtvs2_Meuse;
create table rht.rhtvs2_Meuse as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Meuse')); ---lines
CREATE INDEX indexid_drainMeuse
ON rht.rhtvs2_Meuse
USING btree (id_drain);

drop table if exists rht.rhtvs2_Loire;
create table rht.rhtvs2_Loire as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Loire')); ---lines
CREATE INDEX indexid_drainLoire
ON rht.rhtvs2_Loire
USING btree (id_drain);

drop table if exists rht.rhtvs2_SeineNormandie;
create table rht.rhtvs2_SeineNormandie as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Seine-Normandie')); ---lines
CREATE INDEX indexid_drainSeineNormandie
ON rht.rhtvs2_SeineNormandie
USING btree (id_drain);

drop table if exists rht.rhtvs2_Corse;
create table rht.rhtvs2_Corse as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Corse')); ---lines
CREATE INDEX indexid_drainCorse
ON rht.rhtvs2_Corse
USING btree (id_drain);

drop table if exists rht.rhtvs2_ArtoisPicardie;
create table rht.rhtvs2_ArtoisPicardie as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Artois-Picardie')); ---lines
CREATE INDEX indexid_drainArtoisPicardie
ON rht.rhtvs2_ArtoisPicardie
USING btree (id_drain);

drop table if exists rht.rhtvs2_RhoneMediterranee;
create table rht.rhtvs2_RhoneMediterranee as (
select * from rht.rhtvs2 where id_drain in (select id_drain from uga2010.id_drain_uga where uga='Rhone-Mediterranee')); ---lines
CREATE INDEX indexid_drainRhoneMediterranee
ON rht.rhtvs2_RhoneMediterranee
USING btree (id_drain);