logboek in twee delen:

1. aanvullen van vogelobservaties met tijd. Was niet meegekomen in de eerste keer. (zie onderaan)
2. aanvullen van vogelobservaties in de tijd (vanaf eerste oplevering to laatste oplevering (zie ook repository
   https://repos.deltares.nl/repos/PMR-NCV/trunk/003 Vogels/Bewerkte data

 
   deze datadumps zijn data in de ruwste vorm.
   
   in sommige gevallen lijkt het of er duplicaten beschikbaar zijn. Er is gemeten in een vliegtuig aan twee zijden.
   in specifieke gevallen is het zo dat een soort aan twee kanten van het vliegtuig is gezien. Dat zijn 2 aparte
   waarnemingen ()

3. aanmaken view met daarin de vogels van PMR.

CREATE OR REPLACE VIEW results.meetobject_vogels AS 
 SELECT l.idlocation AS "Meetpunt.Identificatie",
    o.remark AS "Meetpuntomschrijving",
    l.thegeometry AS "Geometrie",
    st_srid(l.thegeometry) AS "EPSGcode"
   FROM location l 
   JOIN observation o on o.idlocation = l.idlocation
  WHERE o.idmethod = 39 and idunit = 18
  
CREATE OR REPLACE VIEW results.meting_vogels AS 
 SELECT o.idobservation AS "Monster.identificatie",
    o.idobservation AS "Meetpunt.identificatie",
    'aantal'::text AS "Parameter.omschrijving",
    p.parameterdescription AS "Biotaxon.naam",
    u.unit AS "Eenheid.code",
    'OPN'::text AS "Hoedanigheid.code",
    'NVT'::text AS "Waardebewerkingsmethode.code",
    m.methoddescription AS "Bemonsteringsmethode.code",
    o.date AS "Begindatum",
    '+00:00ST'::text AS "Tijd_UTCoffset",
    '='::text AS "Limietsymbool",
    o.value AS "Numeriekewaarde",
    0 AS "Kwaliteitsoordeel.code",
    l.thegeometry AS "Geometrie"
   FROM observation o
   JOIN unit u ON u.idunit = o.idunit
   JOIN parameter p ON p.idparameter = o.idparameter
   JOIN observation_type h ON h.idobservation_type = o.idobservation_type
   JOIN location l ON l.idlocation = o.idlocation
   LEFT JOIN method m ON m.idmethod = o.idmethod
   where o.idmethod = 39 and o.idunit = 18
   
als abstract Vogelwaarnemingen in de Voordelta. Waaronder:
roodkeelduiker(Gavia stellata),parelduiker(Gavia arctica),ijsduiker(Gavia immer),fuut(Podiceps cristatus),noordse stormvogel(Fulmarus glacialis),jan van gent(Sula bassana),aalscholver(Phalacrocorax carbo),lepelaar(Platalea leucorodia),knobbelzwaan(Cygnus olor),wilde zwaan(Cygnus cygnus),canadese gans(Branta canadensis),rotgans(Branta bernicla),bergeend(Tadorna tadorna),smient(Anas penelope),wintertaling(Anas crecca),wilde eend(Anas platyrhynchos),pijlstaart(Anas acuta),slobeend(Anas clypeata),tafeleend(Aythya ferina),kuifeend(Aythya fuligula),topper(Aythya marila),eider(Somateria mollissima),ijseend(Clangula hyemalis),grote zee-eend(Melanitta fusca),brilduiker(Bucephala clangula),middelste zaagbek(Mergus serrator),grote zaagbek(Mergus merganser),scholekster(Haematopus ostralegus),zilverplevier(Pluvialis squatarola),drieteenstrandloper(Calidris alba),bonte strandloper(Calidris alpina),regenwulp(Numenius phaeopus),wulp(Numenius arquata),tureluur(Tringa totanus),groenpootruiter(Tringa nebularia),steenloper(Arenaria interpres),kleine jager(Stercorarius parasiticus),grote jager(Stercorarius skua),dwergmeeuw(Larus minutus),kokmeeuw(Larus ridibundus),stormmeeuw(Larus canus),kleine mantelmeeuw(Larus fuscus),zilvermeeuw(Larus argentatus),grote mantelmeeuw(Larus marinus),drieteenmeeuw(Rissa tridactyla),grote stern(Sterna sandvicensis),visdief(Sterna hirundo),noordse stern(Sterna paradisaea),dwergstern(Sterna albifrons),zeekoet(Uria aalge),alk(Alca torda),mantelmeeuw spec.(Larus sp.),ongedetermineerde duiker(Gavia sp.),stern spec(Chlidonias sp.),zwarte zee-eend(Melanitta nigra nigra),grote stern/visdief(Chlidonias sp.)   
   
  
CREATE OR REPLACE VIEW results.monster_vogels AS 
 SELECT o.idobservation AS "Identificatie",
    l.idlocation AS "Meetpunt.identificatie",
    l.thegeometry AS "Geometrie",
    'NT'::text AS "compartiment.code",
    null::text AS "orgaan.code",
    p.parameterdescription AS "Biotaxon.naam",
    0::integer AS begindiepte_m,
    'Niet geimplementeerd'::text AS "referentievlak.code",
    'NVT'::text AS "monsterbewerkingsmethode.code",
    'NB'::text AS "bemonsteringsmethode.code",
    'Vliegtuig'::text AS "Veldapparaat.omschrijving",
    o.date AS monsternemingsdatum,
   '+00:00ST'::text AS "UTCoffset"
   FROM observation o
   JOIN location l ON o.idlocation = l.idlocation
   JOIN parameter p ON p.idparameter = o.idparameter
   LEFT JOIN method m ON m.idmethod = o.idmethod
   where o.idmethod = 39 and o.idunit = 18  
   
   
Abstract and keywords.
voor jaren.
select avg(value),extract(year from date) as year
from observation o where
idmethod = 39 AND idunit = 18
group by extract(year from date)
order by year

2004,2005,2006,2009,2010,2011,2012,2013
   
voor soortnamen.
select distinct nlnaam||' ('||parameterdescription||')'
from observation o 
join parameter p on p.idparameter = o.idparameter
join buwa.uniek_soort us on us.scientificname = parameterdescription
where
idmethod = 39 AND idunit = 18

pijlstaart (Anas acuta), bonte strandloper (Calidris alpina), wintertaling (Anas crecca), kleine mantelmeeuw (Larus fuscus), kokmeeuw (Larus ridibundus), grote stern (Sterna sandvicensis), zeekoet (Uria aalge), wilde eend (Anas platyrhynchos), grote zee-eend (Melanitta fusca), ijseend (Clangula hyemalis), steenloper (Arenaria interpres), canadese gans (Branta canadensis), scholekster (Haematopus ostralegus), topper (Aythya marila), aalscholver (Phalacrocorax carbo), noordse stern (Sterna paradisaea), groenpootruiter (Tringa nebularia), zilvermeeuw (Larus argentatus), drieteenstrandloper (Calidris alba), wulp (Numenius arquata), alk (Alca torda), regenwulp (Numenius phaeopus), zilverplevier (Pluvialis squatarola), wilde zwaan (Cygnus cygnus), middelste zaagbek (Mergus serrator), smient (Anas penelope), noordse stormvogel (Fulmarus glacialis), parelduiker (Gavia arctica), stormmeeuw (Larus canus), grote mantelmeeuw (Larus marinus), eider (Somateria mollissima), roodkeelduiker (Gavia stellata), kuifeend (Aythya fuligula), slobeend (Anas clypeata), visdief (Sterna hirundo), tureluur (Tringa totanus), bergeend (Tadorna tadorna), drieteenmeeuw (Rissa tridactyla), knobbelzwaan (Cygnus olor), kleine jager (Stercorarius parasiticus), jan van gent (Sula bassana), tafeleend (Aythya ferina), ijsduiker (Gavia immer), lepelaar (Platalea leucorodia), fuut (Podiceps cristatus), brilduiker (Bucephala clangula), rotgans (Branta bernicla), grote jager (Stercorarius skua), dwergmeeuw (Larus minutus), dwergstern (Sterna albifrons)

Abstract
PMR-NCV airborne observations for project PMR-NCV, Nature Conversation Voordelta of the Rotterdam Harbour Project.
PMR-NCV vliegtuig tellingen voor het PMR-NCV project Natuur Compensatie Voordelta van het Rotterdam Harbour Project.

-------------------------------------------------------------------------------------------------------------
aanvullen PMR observations met vogels > t0

- Laatste observatie van vogels in de observations tabel is
select max(date),parameterdescription from observation
join parameter p on p.idparameter = observation.idparameter
join parameter_worms pw on pw.aphiaid = p.foreignkey
where p.referencetable = 'parameter_worms' and lower(pw.class::text) = 'aves'::text
group by parameterdescription
order by max(date),parameterdescription

Larus fuscus op 2011-08-04 09:04:14

- buwa.t_waarneming_temp aangemaakt en aangevuld met idparameter,idlocation en gevuld volgens
create table buwa.t_waarneming_temp as
select * from buwa.t_waarneming
where datum+tijd > to_timestamp('2011-08-04 09:04:14','YYYY-MM-DD HH24:MI:SS')

alter table buwa.t_waarneming_temp add column idlocation integer, idparameter integer

idlocation is ingevuld door een vergelijking op tel_geom met de geom van de locations tabel. Indien geen match, dan
telgeom in de locations table inserten en nog maals die vergelijking doen zodat de idlocation beschikbaar komt.

update buwa.t_waarneming_temp set idlocation =
(select l.idlocation from location l where tel_geom = thegeometry)

select * from buwa.t_waarneming_temp where idlocation is null 
to check how many lcations have not been found.


update the idparameter
update buwa.t_waarneming_temp tw set idparameter =
(select idparam from buwa.uniek_soort us
where soort = us.waarneemcode)

select * from buwa.t_waarneming_temp where idparameter is null resulteerd in 3785 records die niet gematched kunnen worden

select
 39,
 18,
 datum+tijd,
 aantal,
 invoerder||' '||opnemer||' '||waarneemid::text,
 2 as idbservation_type,
 idparam,
 tw.idlocation,
 waarneemid
from 
 buwa.t_waarneming_temp tw,
 location l,
 buwa.uniek_soort us,
 parameter p
where l.thegeometry = tw.tel_geom
and tw.soort = us.waarneemcode
and p.idparameter = idparam
and idparam is not null
and datum+tijd > to_timestamp('2011-08-04 09:04:14','YYYY-MM-DD HH24:MI:SS')

levert in totaal een toevoeging van 10226 records

-------------------------------------------------------------------------------------------------------------
Bijwerken vogeltjes van de T0 (geen tijd bij de datum)

in de observations zijn 25957 records te vinden, althans volgens volgende querie.
select idlocation,idobservation,o.idparameter, p.foreignkey from 
observation o,
parameter p
where (lower(remark) like '%buwa%' or lower(remark) like '%petho%')
and p.idparameter = o.idparameter


-- hulptabel buwa_t0.birdlocations gemaakt met behulp van.
-- dit levert 25957 records op
create table buwa_t0.birdlocations
select idlocation,datum,tijd,idparam from 
location l
,buwa_t0.t_waarneming tw
,buwa_t0.uniek_soort us
,parameter p
where l.thegeometry = tw.tel_geom
and tw.soort = us.soort
and p.idparameter = idparam
and idparam is not null


-- uit observation tabel zijn mbv volgende query 25957 records verwijderd.
delete from observation
where idmethod = 39 and idunit = 18 and idobservation_type =2 and (lower(remark) like '%buwa%' or lower(remark) like'%petho%')


-- volgende query is gebruikt om de data opnieuw in de database te plaatsen

insert into observation (idmethod,idunit,date,value,remark,idobservation_type,idparameter,idlocation,idstation)
select 
 39,
 18,
 datum+tijd,
 aantal,
 invoerder||' '||opnemer||' '||waarneemid::text,
 2 as idbservation_type,
 idparam,
 idlocation,
 waarneemid
from 
 buwa_t0.t_waarneming tw,
 location l,
 buwa_t0.uniek_soort us,
 parameter p
where l.thegeometry = tw.tel_geom
and tw.soort = us.soort
and p.idparameter = idparam
and idparam is not null






-- hulptabel gebruiken om de tijd te koppelen aan de huidige observaties op basis van idlocation etc.


####  Opmerking
      GEEN MATCHES GEVONDEN VOOR 4720 waarnemingen door BUWA gedaan.
####
select count(*) from 
buwa_t0.t_waarneming tw,
buwa_t0.uniek_soort us
where tw.soort = us.soort
and idparam is not null

25957 entries met een parameter id (dus een soort die in worms voorkomt, er zijn een aantal observaties, blauwe reiger bijvoorbeeld en vink die hierin niet zijn opgenomen).
in totaal 4720 waarnemingen, hieronder die complete lijst.
"- -";9
"#A - alk spec";1
"AZ - alk/zeekoet";43
"BALLON";2
"BK - bonte kraai";1
"BLAAS - BLAASWIER";2
"BM - jonge zilver/kleine mantel";7
"BOEI";128
"BR - blauwe reiger";1
"BUGGY - KITEBUGGY";3
"BV - bruinvis";122
"#C - calidris spec.";2
"DEBRIS";4
"END OF TRANSECT";81
"FOAM - LINE OF FOAM";1
"FRONT";48
"FUIK";1
"GASPLATFORM";9
"GEEN VOGEL GEZIEN";2051
"#G - grote meeuw spec.";418
"GV - grote stern/visdief";2
"GZ - grote zilverreiger";2
"HZ - huiszwaluw";1
"JETSK - JETSKI";2
"KANO - KANO";2
"#KE - kleinde eend spec.";1
"KITES - KITESURFER";17
"#K - kleine meeuw spec.";22
"#KZ - kleine zangvogel spec";2
"#M - mantelmeeuw spec.";4
"MOSL - MOSSELZAADVISSER";1
"OD - ongedetermineerde duiker";88
"OS - ongedetermineerde steltloper";5
"PAAL - PAAL";23
"PAA - PAARD";1
"REE - ree";1
"RK - roek";1
"SCHIP - SCHIP";104
"SEP - SEPIA";2
"SPEED - SPEEDBOOT";1
"SP - spreeuw";3
"START - START OF TRANSECT";86
"STRMN - STROOMNAAD";4
"SURF - SURFER";11
"SVGR - SPORTVISSER GROOT";2
"SVKL - SPORTVISSER KLEIN";10
"SW - sperwer";1
"UK - VISKOTTER";151
"UNKNO - UNKNOWN SOORT";1
"UWNET - STAAND WAND";7
"UW - STAAND WAND VISSER";8
"VIS - vis spec";1
"VI - vink";6
"VLIEG - VLIEGER";1
"VN - visdief/noordse stern";330
"#X - meeuw spec";175
"#XS - stern/meeuw spec";4
"XT - stern spec";31
"YACHT - YACHT";1
"Z1 - gewone zeehond";203
"Z2 - grijze zeehond";52
"Z3 - ongedetermineerde zeehond";358
"ZANDH - ZANDHOPPER";5
"ZEILBOOT";40
"#ZOOG - zeezoogdier spec";1
"ZR - zwarte kraai";12