Changes between Version 11 and Version 12 of Import impact data to dbeel

Timestamp:

Jan 3, 2019 5:37:56 PM (6 years ago)

Author:

cedric

Comment:

--

Import impact data to dbeel

@@ -21 +21 @@
 
 This is the most complete table we have for spain, it's been sent by the ministry, and we will import it first and then import other data.
-One of the problem we must solve it that this layer has several points nearly on top of each other (e.g. one per "dike" on each dam). So when calculated multiple impact we might run into problems https://trello.com/c/IIMjWVkX
-
-{{{#!sql
--- !!! some duplicate inside the spain_obstruction_in_spain table
--- eg SO_288 and SO_874 which have different ALT_CAU !!!
--- internal duplicate here
-WITH duplicate AS
-(
-        SELECT sp1.id AS id1, sp2.id AS id2 FROM sudoang.spain_obstruction_in_spain AS sp1, sudoang.spain_obstruction_in_spain AS sp2
-        WHERE substring(sp1.id from 4) < substring(sp2.id from 4) AND ST_Distance(sp1.geom, sp2.geom)<0.01
-) SELECT *, rank() OVER(PARTITION BY id1 ORDER BY id2) FROM duplicate ORDER BY substring(id1 from 4)::integer
-;
-}}}
-||id1||id2||rank||
-||SO_14||SO_15||1||
-||SO_14||SO_17||3||
-||SO_14||SO_16||2||
-||SO_15||SO_16||1||
-||SO_15||SO_17||2||
-||SO_16||SO_17||1||
-||SO_42||SO_44||1||
-
 
 
@@ -87 +65 @@
 ) sub --415
 }}}
+
+
+
+
+
+
+
+One of the problem we must solve it that this layer has several points nearly on top of each other (e.g. one per "dike" on each dam). So when calculated multiple impact we might run into problems https://trello.com/c/IIMjWVkX
+
+{{{#!sql
+-- !!! some duplicate inside the spain_obstruction_in_spain table
+-- eg SO_288 and SO_874 which have different ALT_CAU !!!
+-- internal duplicate here
+WITH duplicate AS
+(
+        SELECT sp1.id AS id1, sp2.id AS id2 FROM sudoang.spain_obstruction_in_spain AS sp1, sudoang.spain_obstruction_in_spain AS sp2
+        WHERE substring(sp1.id from 4) < substring(sp2.id from 4) AND ST_Distance(sp1.geom, sp2.geom)<0.01
+) SELECT *, rank() OVER(PARTITION BY id1 ORDER BY id2) FROM duplicate ORDER BY substring(id1 from 4)::integer
+;
+}}}
+||id1||id2||rank||
+||SO_14||SO_15||1||
+||SO_14||SO_17||3||
+||SO_14||SO_16||2||
+||SO_15||SO_16||1||
+||SO_15||SO_17||2||
+||SO_16||SO_17||1||
+||SO_42||SO_44||1||
+
+We have also reprojected all data and created a unique id for all tables, the id is
+
+v
+
+|| prefix || table ||
+|| SO || Spain_obstruction &  spain_obstruction_in_spain ||
+|| CO || Catalonia_obstruction (deprecated) ||
+|| COD || catalonia_obstruction_dams ||
+|| COH || catalonia_obstruction_hpp ||
+|| MIO  || minho_obstruction ||
+|| MIO  || minho_obstruction ||
+|| AFR || Amber France ||
+|| AFS || Amber Spain ||
+|| FC || fcul_obstruction ||
+
+
+To join the data COD and COH it's a bit complex, see the script, instead of indentifying the mother table,  the daughter table is idenditied. This was done with a list of coma separated value, first we had to correct it (remove space, replace `;` with `,`). To join the two tables we had to create an intermediate table `catalonia_obstruction_join_dams_hpp`
+source:eda/EDAcommun/sql/sudoang/dbeel_sudoang_obstruction.sql  [[BR]]
+
 
 == Create the dbeel database ==