Changes between Version 11 and Version 12 of electrofishing data

Timestamp:

Jan 19, 2012 11:35:09 AM (13 years ago)

Author:

cedric

Comment:

--

electrofishing data

@@ -160 +160 @@
 }}}
 
+
+{{{
+
+/*
+CHECKING FOR SPATIAL DUPLICATES
+*/
+
+
+--select * from wrbd.stationdbeel s1 join ireland.stations S2 on st_dwithin(s1.the_geom,s2.the_geom,100);
+--select  distinct on (locationcode) op_id, locationcode from wrbd.stationdbeel s1 join ireland.stations S2 on st_dwithin(s1.the_geom,s2.the_geom,20);
+-- first step I copy the code of the stations from wrbd.stationdbeel where the stations are less than 20 m apart. These are doubles
+alter table ireland.stations add column dbeel_op_id uuid;
+update ireland.stations set dbeel_op_id=op_id from (
+        select  distinct on (locationcode) op_id, locationcode from wrbd.stationdbeel s1 join ireland.stations S2 on st_dwithin(s1.the_geom,s2.the_geom,20))as sub
+where sub.locationcode=stations.locationcode; --86
+
+--select t.dbeel_op_id, surveyid from ireland.stations t join ireland.survey s on s.locationcode=t.locationcode where t.dbeel_op_id is not null;
+-- second step I copy the same code but to the survey file, I'll know that I have a survey located on a station for which I have already entered data
+alter table ireland.survey add column dbeel_op_id uuid;
+update ireland.survey set dbeel_op_id=sub.dbeel_op_id from (
+        select t.dbeel_op_id, surveyid from ireland.stations t join ireland.survey s on s.locationcode=t.locationcode where t.dbeel_op_id is not null) as sub
+where sub.surveyid=survey.surveyid;--298
+
+select max(op_year) from wrbd.operation_op where op_st_id like '%MI%'; --2009 there might be double indeed
+
+/*
+TODO for the stations that have duplicates, check whether the year has been entered or if there is something we can reckon about the code
+*/
+}}}