Changes between Version 2 and Version 3 of EMU Download and load

Timestamp:

Sep 14, 2010 12:25:19 PM (15 years ago)

Author:

celine

Comment:

--

EMU Download and load

@@ -32 +32 @@
 }}}
 
+{{{
+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);
+
+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);
+        
+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 "BDCarthage_2009"."UGA2010_L93v2" 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;
+        
+
+}}}