Changes between Version 7 and Version 8 of Cookbook CCM21_Region
- Timestamp:
- Sep 4, 2010 12:16:41 PM (15 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Cookbook CCM21_Region
v7 v8 4 4 5 5 {{{ 6 drop table if exists france.wso 1;7 CREATE TABLE france.wso 1(6 drop table if exists france.wsoreg; 7 CREATE TABLE france.wsoreg ( 8 8 id serial PRIMARY KEY, 9 9 wso_id integer, … … 11 11 ) 12 12 13 insert into france.wso1(wso_id)14 select distinct on (wso_id) wso_id from ccm21.riversegments r15 join (SELECT ST_Union(f.the_geom) as singlegeom16 FROM france.departement As f) as sub17 ON ST_Intersects(sub.singlegeom,r.the_geom); --170828 lignes (avec st_contains 170703 lines)18 19 UPDATE france.wso1 set area='France' where area IS NULL --693 sea nodes20 21 22 -- pour augmenter le nombre de23 ALTER TABLE france.wso1 ALTER COLUMN area TYPE varchar(25)24 13 --Ile de France 25 insert into france.wso 1(wso_id)14 insert into france.wsoreg(wso_id) 26 15 select distinct on (wso_id) wso_id from ccm21.riversegments r 27 16 join (SELECT the_geom … … 29 18 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 30 19 31 UPDATE france.wso 1set area='IleDeFrance' where area IS NULL20 UPDATE france.wsoreg set area='IleDeFrance' where area IS NULL 32 21 33 22 --Champagne Ardenne 34 insert into france.wso 1(wso_id)23 insert into france.wsoreg(wso_id) 35 24 select distinct on (wso_id) wso_id from ccm21.riversegments r 36 25 join (SELECT the_geom … … 38 27 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 39 28 40 UPDATE france.wso 1set area='ChampagneArdenne' where area IS NULL29 UPDATE france.wsoreg set area='ChampagneArdenne' where area IS NULL 41 30 42 31 --Picardie 43 insert into france.wso 1(wso_id)32 insert into france.wsoreg(wso_id) 44 33 select distinct on (wso_id) wso_id from ccm21.riversegments r 45 34 join (SELECT the_geom … … 47 36 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 48 37 49 UPDATE france.wso 1set area='Picardie' where area IS NULL38 UPDATE france.wsoreg set area='Picardie' where area IS NULL 50 39 51 40 --Haute-Normandie 52 insert into france.wso 1(wso_id)41 insert into france.wsoreg(wso_id) 53 42 select distinct on (wso_id) wso_id from ccm21.riversegments r 54 43 join (SELECT the_geom … … 56 45 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 57 46 58 UPDATE france.wso 1set area='HauteNormandie' where area IS NULL47 UPDATE france.wsoreg set area='HauteNormandie' where area IS NULL 59 48 60 49 --Centre 61 insert into france.wso 1(wso_id)50 insert into france.wsoreg(wso_id) 62 51 select distinct on (wso_id) wso_id from ccm21.riversegments r 63 52 join (SELECT the_geom … … 65 54 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 66 55 67 UPDATE france.wso 1set area='Centre' where area IS NULL56 UPDATE france.wsoreg set area='Centre' where area IS NULL 68 57 69 58 --BasseNormandie 70 insert into france.wso 1(wso_id)59 insert into france.wsoreg(wso_id) 71 60 select distinct on (wso_id) wso_id from ccm21.riversegments r 72 61 join (SELECT the_geom … … 74 63 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 75 64 76 UPDATE france.wso 1set area='BasseNormandie' where area IS NULL65 UPDATE france.wsoreg set area='BasseNormandie' where area IS NULL 77 66 78 67 --Bourgogne 79 insert into france.wso 1(wso_id)68 insert into france.wsoreg(wso_id) 80 69 select distinct on (wso_id) wso_id from ccm21.riversegments r 81 70 join (SELECT the_geom … … 83 72 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 84 73 85 UPDATE france.wso 1set area='Bourgogne' where area IS NULL74 UPDATE france.wsoreg set area='Bourgogne' where area IS NULL 86 75 87 76 --Nord-Pas-de-Calais 88 insert into france.wso 1(wso_id)77 insert into france.wsoreg(wso_id) 89 78 select distinct on (wso_id) wso_id from ccm21.riversegments r 90 79 join (SELECT the_geom … … 92 81 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 93 82 94 UPDATE france.wso 1set area='NordPasdeCalais' where area IS NULL83 UPDATE france.wsoreg set area='NordPasdeCalais' where area IS NULL 95 84 96 85 --Lorraine 97 insert into france.wso 1(wso_id)86 insert into france.wsoreg(wso_id) 98 87 select distinct on (wso_id) wso_id from ccm21.riversegments r 99 88 join (SELECT the_geom … … 101 90 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 102 91 103 UPDATE france.wso 1set area='Lorraine' where area IS NULL92 UPDATE france.wsoreg set area='Lorraine' where area IS NULL 104 93 105 94 106 95 --Alsace 107 insert into france.wso 1(wso_id)96 insert into france.wsoreg(wso_id) 108 97 select distinct on (wso_id) wso_id from ccm21.riversegments r 109 98 join (SELECT the_geom … … 111 100 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 112 101 113 UPDATE france.wso 1set area='Alsace' where area IS NULL102 UPDATE france.wsoreg set area='Alsace' where area IS NULL 114 103 115 104 --Franche-Comté 116 insert into france.wso 1(wso_id)105 insert into france.wsoreg(wso_id) 117 106 select distinct on (wso_id) wso_id from ccm21.riversegments r 118 107 join (SELECT the_geom … … 120 109 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 121 110 122 UPDATE france.wso 1set area='FrancheComte' where area IS NULL111 UPDATE france.wsoreg set area='FrancheComte' where area IS NULL 123 112 124 113 --Pays-de-la-Loire 125 insert into france.wso 1(wso_id)114 insert into france.wsoreg(wso_id) 126 115 select distinct on (wso_id) wso_id from ccm21.riversegments r 127 116 join (SELECT the_geom … … 129 118 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 130 119 131 UPDATE france.wso 1set area='PaysdelaLoire' where area IS NULL120 UPDATE france.wsoreg set area='PaysdelaLoire' where area IS NULL 132 121 133 122 --Bretagne 134 insert into france.wso 1(wso_id)123 insert into france.wsoreg(wso_id) 135 124 select distinct on (wso_id) wso_id from ccm21.riversegments r 136 125 join (SELECT the_geom … … 138 127 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 139 128 140 UPDATE france.wso 1set area='Bretagne' where area IS NULL129 UPDATE france.wsoreg set area='Bretagne' where area IS NULL 141 130 142 131 --Poitou-Charentes 143 insert into france.wso 1(wso_id)132 insert into france.wsoreg(wso_id) 144 133 select distinct on (wso_id) wso_id from ccm21.riversegments r 145 134 join (SELECT the_geom … … 147 136 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 148 137 149 UPDATE france.wso 1set area='PoitouCharentes' where area IS NULL138 UPDATE france.wsoreg set area='PoitouCharentes' where area IS NULL 150 139 151 140 --Aquitaine 152 insert into france.wso 1(wso_id)141 insert into france.wsoreg(wso_id) 153 142 select distinct on (wso_id) wso_id from ccm21.riversegments r 154 143 join (SELECT the_geom … … 156 145 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 157 146 158 UPDATE france.wso 1set area='Aquitaine' where area IS NULL147 UPDATE france.wsoreg set area='Aquitaine' where area IS NULL 159 148 160 149 --Midi-Pyrénées 161 insert into france.wso 1(wso_id)150 insert into france.wsoreg(wso_id) 162 151 select distinct on (wso_id) wso_id from ccm21.riversegments r 163 152 join (SELECT the_geom … … 165 154 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 166 155 167 UPDATE france.wso 1set area='MidiPyrenees' where area IS NULL156 UPDATE france.wsoreg set area='MidiPyrenees' where area IS NULL 168 157 169 158 --Limousin 170 insert into france.wso 1(wso_id)159 insert into france.wsoreg(wso_id) 171 160 select distinct on (wso_id) wso_id from ccm21.riversegments r 172 161 join (SELECT the_geom … … 174 163 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 175 164 176 UPDATE france.wso 1set area='Limousin' where area IS NULL165 UPDATE france.wsoreg set area='Limousin' where area IS NULL 177 166 178 167 --Rhône-Alpes 179 insert into france.wso 1(wso_id)168 insert into france.wsoreg(wso_id) 180 169 select distinct on (wso_id) wso_id from ccm21.riversegments r 181 170 join (SELECT the_geom … … 183 172 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 184 173 185 UPDATE france.wso 1set area='RhoneAlpes' where area IS NULL174 UPDATE france.wsoreg set area='RhoneAlpes' where area IS NULL 186 175 187 176 --Auvergne 188 insert into france.wso 1(wso_id)177 insert into france.wsoreg(wso_id) 189 178 select distinct on (wso_id) wso_id from ccm21.riversegments r 190 179 join (SELECT the_geom … … 192 181 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 193 182 194 UPDATE france.wso 1set area='Auvergne' where area IS NULL183 UPDATE france.wsoreg set area='Auvergne' where area IS NULL 195 184 196 185 --Languedoc-Roussillon 197 insert into france.wso 1(wso_id)186 insert into france.wsoreg(wso_id) 198 187 select distinct on (wso_id) wso_id from ccm21.riversegments r 199 188 join (SELECT the_geom … … 201 190 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 202 191 203 UPDATE france.wso 1set area='Languedoc-Roussillon' where area IS NULL192 UPDATE france.wsoreg set area='Languedoc-Roussillon' where area IS NULL 204 193 205 194 --Provence Alpes Côte d'Azur 206 insert into france.wso 1(wso_id)195 insert into france.wsoreg(wso_id) 207 196 select distinct on (wso_id) wso_id from ccm21.riversegments r 208 197 join (SELECT the_geom … … 210 199 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 211 200 212 UPDATE france.wso 1set area='PACA' where area IS NULL201 UPDATE france.wsoreg set area='PACA' where area IS NULL 213 202 214 203 --Corse 215 insert into france.wso 1(wso_id)204 insert into france.wsoreg(wso_id) 216 205 select distinct on (wso_id) wso_id from ccm21.riversegments r 217 206 join (SELECT the_geom … … 219 208 ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines 220 209 221 UPDATE france.wso 1set area='Corse' where area IS NULL210 UPDATE france.wsoreg set area='Corse' where area IS NULL 222 211 }}} 223 212 … … 226 215 Certains riversegments n'ont pas été affectés à des régions 227 216 {{{ 228 INSERT INTO france.wso 1 (wso_id) (select wso_id from france.wso1group by wso_id having count(wso_id)=1);217 INSERT INTO france.wsoreg (wso_id) (select wso_id from france.wsoreg group by wso_id having count(wso_id)=1); 229 218 -- Pour chaque wso_id insertion de l'area correspondant 230 UPDATE france.wso 1219 UPDATE france.wsoreg 231 220 SET area='Bretagne' 232 221 WHERE wso_id in ('342863','343606','341602','349389','345159','347405','230184','347654','339707', … … 234 223 '351382','338608','353376','343371','342099') and area is null; 235 224 236 UPDATE france.wso 1225 UPDATE france.wsoreg 237 226 SET area='Corse' 238 227 WHERE wso_id in ('244266','263660') and area is null; 239 UPDATE france.wso 1228 UPDATE france.wsoreg 240 229 SET area='PACA' 241 230 WHERE wso_id in ('130439','233456','238201','230142','229971') and area is null; 242 231 243 UPDATE france.wso 1232 UPDATE france.wsoreg 244 233 SET area='Aquitaine' 245 234 WHERE wso_id in ('389002','389536','390418','390989','386642','391572') and area is null; 246 235 247 236 248 UPDATE france.wso 1237 UPDATE france.wsoreg 249 238 SET area='BasseNormandie' 250 239 WHERE wso_id in ('334139','342310','335137','338013','338383','341736','339762','336831') and area is null; 251 240 252 UPDATE france.wso 1241 UPDATE france.wsoreg 253 242 SET area='PoitouCharentes' 254 243 WHERE wso_id in ('388638','387835','386521') and area is null;
