back to first page ..
back to River basin district

Download and load EMU

Data in ETRS 1989 LAEA srid=3035

d:
cd D:\CelineJouanin\EMU\wise_rbds_f1v3_shp
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -I RBD_F1v3.shp emu > emu.sql

• We have to convert it into utf8 (as the database is posgis), in notepad, open the uga.sql file and convert it to utf8

        Encodage>convertir en utf8
  

And save the file as : emuutf8

--Céline
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f emuutf8.sql 
--Cédric
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5433 -f emuutf8.sql 

• Create schema emu et changement de schéma de la table uga (qui est sous public)

        --- In sql editor 
        CREATE SCHEMA rbd_f1v3;
        ALTER TABLE emu SET SCHEMA rbd_f1v3;
  

CREATE INDEX bassin_index ON  ccm21.riversegments USING GIST(the_geom);
	
DROP TABLE IF EXISTS bassin_pose CASCADE;
CREATE TABLE bassin_pose AS
SELECT DISTINCT bassin.wso_id, 'Western' as EMU FROM ccm21.riversegments as bassin, (SELECT * FROM rbd_f1v3.emu WHERE name_engl='Western') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom AND (within(bassin.the_geom, rbd_selection.the_geom));

ALTER TABLE bassin_pose ADD CONSTRAINT bassin_pose_pkey PRIMARY KEY (wso_id);
	
DROP VIEW IF EXISTS bassin_pose_complet CASCADE;
CREATE VIEW bassin_pose_complet AS
SELECT "CATCHMENT_W".*, EMU
FROM ccm21.riversegments, bassin_pose
WHERE bassin_pose.wso_id = riversegments.wso_id;
	
INSERT INTO bassin_pose
SELECT DISTINCT bassin.wso_id, 'Anglian' as EMU  FROM ccm21.riversegments as bassin, (SELECT * FROM rbd_f1v3.emu WHERE name_engl='Anglian') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom AND (within(bassin.the_geom, rbd_selection.the_geom)); --117 lines

INSERT INTO bassin_pose
SELECT DISTINCT bassin.wso_id, 'Rhone' as EMU FROM ccm21.riversegments as bassin, (SELECT * FROM rbd_f1v3.emu WHERE name_engl='Rhone and Coastal Mediterranean') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom AND (within(bassin.the_geom, rbd_selection.the_geom))  AND NOT (bassin.wso_id = 291111 OR bassin.wso_id = 291126 OR bassin.wso_id = 291110 OR bassin.wso_id = 442353 OR bassin.wso_id = 129487); --67 lines
	
INSERT INTO bassin_pose
SELECT  DISTINCT bassin.wso_id, 'Elbe' as EMU FROM ccm21.riversegments as bassin, (SELECT * FROM rbd_f1v3.emu WHERE name_engl='Elbe') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom AND (within(bassin.the_geom, rbd_selection.the_geom )) AND NOT (bassin.wso_id = 7 OR bassin.wso_id = 1 OR bassin.wso_id = 98); -- 7 lines

INSERT INTO bassin_pose
SELECT  DISTINCT bassin.wso_id, 'Sardinia' as EMU FROM ccm21.riversegments as bassin, (SELECT * FROM rbd_f1v3.emu WHERE name_engl='Sardinia') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom AND (within(bassin.the_geom, rbd_selection.the_geom)); --204 lines

INSERT INTO bassin_pose
SELECT  DISTINCT bassin.wso_id, 'Basque' as EMU FROM ccm21.riversegments as bassin, (SELECT * FROM rbd_f1v3.emu WHERE name_engl='Basque County internal basins'  or name_engl='Northern Spain') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom AND (within(bassin.the_geom, rbd_selection.the_geom) ) AND NOT (bassin.wso_id = 291194 OR bassin.wso_id = 292665); --148 lines

INSERT INTO bassin_pose
SELECT  DISTINCT bassin.wso_id, 'Swedish West Coast' as EMU FROM ccm21.riversegments as bassin, (SELECT * FROM rbd_f1v3.emu WHERE name_engl='Skagerrak and Kattegat') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom AND (within(bassin.the_geom, rbd_selection.the_geom)) AND NOT (bassin.wso_id = 1034752OR bassin.wso_id = 1034724); -- 115 lines
	
INSERT INTO bassin_pose
SELECT  DISTINCT bassin.wso_id, 'Britanny' as EMU FROM ccm21.riversegments as bassin, (SELECT *, ST_Transform(the_geom,3035) as the_geom2 FROM uga2010.uga  WHERE libelle='Bretagne') as rbd_selection WHERE bassin.the_geom && rbd_selection.the_geom2 AND (within(bassin.the_geom, rbd_selection.the_geom2)) AND NOT bassin.wso_id =291111; -- 164 lines

ALTER TABLE bassin_pose SET SCHEMA rbd_f1v3;

Nombre de ligne pour la Bretagne différent de celui des UGA : 164 au lieu de 166 lignes.
Il manque deux wso_id : 353376 et 354061 which are on a island --> ok