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 lignes


 --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 lignes

 --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 lignes


 --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 lignes

 --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 lignes

 --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 lignes

 --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 lignes


 --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 lignes

 --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 lignes

 --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 lignes

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

insert into uga2010.id_drain(gid,id_drain,uga) values ('3067','3224','RhoneMediterranee'),('11638','11467','RhoneMediterranee'),('22455','22419','RhoneMediterranee'),('24629','24470','Corse'),('26919','26772','Corse'),('71671','501772','Rhin');   -- 6 lignes
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='SeineNormandie' where uga IS NULL; --28 lignes

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

alter table uga2010.id_drain rename to id_drain_uga

#TODO voir pb doublons

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