back to first page[..][[BR]]
back to ["France departement"][[BR]]


{{{
drop table  if exists france.wso1;
 CREATE TABLE france.wso1 (
 id serial PRIMARY KEY,
 wso_id integer,
 area varchar(25)
 )

insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT ST_Union(f.the_geom) as singlegeom
    FROM france.departement As f) as sub
ON ST_Intersects(sub.singlegeom,r.the_geom); --170828 lignes   (avec st_contains 170703 lines)

UPDATE france.wso1 set area='France' where area IS NULL --693 sea nodes


-- pour augmenter le nombre de 
ALTER TABLE france.wso1 ALTER COLUMN area TYPE varchar(25)
--Ile de France
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='11') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='IleDeFrance' where area IS NULL

--Champagne Ardenne
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='21') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='ChampagneArdenne' where area IS NULL

--Picardie
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='22') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Picardie' where area IS NULL

--Haute-Normandie
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='23') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='HauteNormandie' where area IS NULL

--Centre
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='24') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Centre' where area IS NULL

--BasseNormandie
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='25') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='BasseNormandie' where area IS NULL

--Bourgogne
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='26') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Bourgogne' where area IS NULL

--Nord-Pas-de-Calais
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='31') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='NordPasdeCalais' where area IS NULL

--Lorraine
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='41') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Lorraine' where area IS NULL


--Alsace
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='42') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Alsace' where area IS NULL

--Franche-Comté
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='43') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='FrancheComte' where area IS NULL

--Pays-de-la-Loire
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='52') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='PaysdelaLoire' where area IS NULL

--Bretagne
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='53') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Bretagne' where area IS NULL

--Poitou-Charentes
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='54') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='PoitouCharentes' where area IS NULL

--Aquitaine
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='72') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Aquitaine' where area IS NULL

--Midi-Pyrénées
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='73') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='MidiPyrenees' where area IS NULL

--Limousin
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='74') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Limousin' where area IS NULL

--Rhône-Alpes
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='82') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='RhoneAlpes' where area IS NULL

--Auvergne
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='83') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Auvergne' where area IS NULL

--Languedoc-Roussillon
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='91') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Languedoc-Roussillon' where area IS NULL

--Provence Alpes Côte d'Azur
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='93') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='PACA' where area IS NULL

--Corse
insert into france.wso1(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='94') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso1 set area='Corse' where area IS NULL
}}}

Et c'est qu'une fois fini que tu te rends compte qu'il y avait un moyen plus rapide[[BR]]

Certains riversegments n'ont pas été affectés à des régions
{{{
INSERT INTO france.wso1 (wso_id) (select wso_id from france.wso1 group by wso_id having count(wso_id)=1);
-- Pour chaque wso_id insertion de l'area correspondant
UPDATE france.wso1
SET area='Bretagne'
WHERE wso_id in ('342863','343606','341602','349389','345159','347405','230184','347654','339707',
'345148','348787','341432','343029','339468','347168','342131','343171','346622','347181','341378',
'351382','338608','353376','343371','342099') and area is null;

UPDATE france.wso1
SET area='Corse'
WHERE wso_id in ('244266','263660') and area is null;
UPDATE france.wso1
SET area='PACA'
WHERE wso_id in ('130439','233456','238201','230142','229971') and area is null;

UPDATE france.wso1
SET area='Aquitaine'
WHERE wso_id in ('389002','389536','390418','390989','386642','391572') and area is null;


UPDATE france.wso1
SET area='BasseNormandie'
WHERE wso_id in ('334139','342310','335137','338013','338383','341736','339762','336831') and area is null;

UPDATE france.wso1
SET area='PoitouCharentes'
WHERE wso_id in ('388638','387835','386521') and area is null;