Version 2 (modified by celine, 14 years ago) (diff) |
---|
back to first page..
back to RHT
drop table if exists uga2010.id_bdcarthage; CREATE TABLE uga2010.id_bdcarthage ( id serial PRIMARY KEY, id_bdcarth integer, uga varchar(25) ); --Bretagne insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Bretagne' where uga IS NULL -- 27250 lines --Adour insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Adour' where uga IS NULL -- 23181 lines --Garonne insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Garonne' where uga IS NULL -- 92831 lines --Loire insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Loire' where uga IS NULL -- 109437 lines --Seine-Normandie insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Seine-Normandie' where uga IS NULL -- 55365 lines --Corse insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Corse' where uga IS NULL -- 12594 lines --Artois-Picardie insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Artois-Picardie' where uga IS NULL -- 12040 lines --Rhone-Mediterranee insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Rhone-Mediterranee' where uga IS NULL -- 147091 lines --Rhin insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Rhin' where uga IS NULL -- 24355 lines --Meuse insert into uga2010.id_bdcarthage(id_bdcarth) select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique 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_bdcarthage set uga='Meuse' where uga IS NULL -- 5539 lines