Cookbook CCM21_Region – EDA

Context Navigation

back to first page..
back to France departement

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

--Ile de France
insert into france.wsoreg(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); --1 lines

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

--Champagne Ardenne
insert into france.wsoreg(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); --3 lines

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

--Picardie
insert into france.wsoreg(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); --11 lines

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

--Haute-Normandie
insert into france.wsoreg(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); --23 lines

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

--Centre
insert into france.wsoreg(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); --2 lines

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

--BasseNormandie
insert into france.wsoreg(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); --60 lines

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

--Bourgogne
insert into france.wsoreg(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); --3 lines

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

--Nord-Pas-de-Calais
insert into france.wsoreg(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); --19 lines

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

--Lorraine
insert into france.wsoreg(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); --8 lines

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


--Alsace
insert into france.wsoreg(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); --2 lines

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

--Franche-Comté
insert into france.wsoreg(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); --2 lines

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

--Pays-de-la-Loire
insert into france.wsoreg(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); --34 lines

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

--Bretagne
insert into france.wsoreg(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); --139 lines

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

--Poitou-Charentes
insert into france.wsoreg(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); --27 lines

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

--Aquitaine
insert into france.wsoreg(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); --49 lines

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

--Midi-Pyrénées
insert into france.wsoreg(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); --6 lines

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

--Limousin
insert into france.wsoreg(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); --3 lines

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

--Rhône-Alpes
insert into france.wsoreg(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); --3 lines

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

--Auvergne
insert into france.wsoreg(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); --4 lines

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

--Languedoc-Roussillon
insert into france.wsoreg(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); --58 lines

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

--Provence Alpes Côte d'Azur
insert into france.wsoreg(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); --71 lines

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

--Corse
insert into france.wsoreg(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); --175 lines

UPDATE france.wsoreg set area='Corse' where area IS NULL

Certains riversegments n'ont pas été affectés à des régions

INSERT INTO france.wsoreg (wso_id) (select wso_id from france.wsoreg group by wso_id having count(wso_id)=1);
-- Pour chaque wso_id insertion de l'area correspondant
UPDATE france.wsoreg
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.wsoreg
SET area='Corse'
WHERE wso_id in ('244266','263660') and area is null;
UPDATE france.wsoreg
SET area='PACA'
WHERE wso_id in ('130439','233456','238201','230142','229971') and area is null;

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


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

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

D'autres riversegments ne doivent pas être rattachés à la France

delete from france.wsoreg where wso_id=291495;
delete from france.wsoreg where wso_id=442529;

Last modified 13 years ago Last modified on May 25, 2012 1:57:49 PM

Download in other formats:

Plain Text