__________other analysis from t2 dataset______________

create table pruts.t2otheranalysis (
	date date,
	station integer,
	wgs84_n float,
	wgs84_e float,
	gegevens varchar(75),
	value float,
	idparameter integer,
	idunit integer,
	idobservation_type integer,
	idmethod integer
	);

alter table pruts.t2otheranalysis add column geom geometry

alter table pruts.t2otheranalysis add column idlocation integer

insert into parameter (parameterdescription,foreignkey,referencetable) values
	('s%n (% n-tot.)',20,'parameter_sediment'),
	('scnratio (value)',21,'parameter_sediment'),
	('sorg.%c (% org. c)',22,'parameter_sediment');

update parameter_sediment set foreignkey = 22 where parameter = '%org.c'

insert into observation_type (obs_type_description) values
	('s%n (% n-tot.)'),
	('scnratio (value)'),
	('sorg.%c (% org. c)')

update pruts.t2otheranalysis set idobservation_type = 32 where idunit = 37

select * from pruts.t2otheranalysis where idunit is null

select setval('observation_type_idobservation_type_seq', 29)

update pruts.t2otheranalysis set geom = st_transform(st_setsrid(st_point(wgs84_e,wgs84_n),4326),4326)

update pruts.t2otheranalysis s
set idlocation = l.idlocation
from location l
where st_within(l.thegeometry, st_buffer( st_transform(s.geom, 4326) , 8.1818181818181818181818181818182e-6))
and s.idlocation is null

insert into observation 
 (idmethod,idunit,date,value,idlocation,idparameter,idobservation_type,idsample)
select
 i.idmethod
,i.idunit
,i.date
,i.value
,i.idlocation
,i.idparameter
,i.idobservation_type
,i.locid
FROM
 pruts.t2otheranalysis i;

alter table pruts.t2otheranalysis add column locid serial


____________________morning__________________
insert into parameter (parameterdescription, foreignkey, referencetable) values
	('specific surface area', 15, 'parameter_sediment');

insert into unit (unit, unitdescription) values
	('cm2/cc', 'total area of material per volume');

select distinct (sed_parameter) from pruts.t0sediment

insert into quality (qualitydescription) values
	('Present / absence'),
	('Telling - zeer betrouwbaar (Afw. < 5%, +/- 1)'),
	('Telling - redelijk betrouwbaar (Afw. < 5%, +/- 10)'),
	('Telling - matig betrouwbaar (Afw. < 5%,  +/- 25)'),
	('Telling - betrouwbaar (Afw. < 5%, +/- 5)'),
	('Phylum zeker'),
	('Ordo zeker'),
	('Soort zeker'),
	('Genus zeker'),
	('Genus vermoedelijk'),
	('Family zeker'),
	('Weging - Zeer nauwkeurig'),
	('Weging van deelmonster, of weging met afwijkend gewicht'),
	('Berekend via gemiddelde of sterk afwijkend gewicht'),
	('Berekend via lengte/gewicht regressie zelfde seizoen'),
	('Berekend via conversie natgewicht of len/gew regressie - afwijkend gewicht'),
	('Berekend via gemiddelde of sterk afwijkend gewicht'),
	('Berekend via conversie natgewicht of len/gew regressie')
	;

update pruts.t0sediment t
set idparameter = u.idparameter
from parameter u
where t.sed_parameter = u.parameterdescription;

update pruts.t0sediment set idmethod= 46;

update pruts.t0sediment set idobservation_type = 23 where sed_parameter = 'Slib percentage <2mu';
update pruts.t0sediment set idobservation_type = 23 where sed_parameter = 'Slib percentage <32mu';
update pruts.t0sediment set idobservation_type = 22 where sed_parameter = 'Smedium%';
update pruts.t0sediment set idobservation_type = 26 where sed_parameter = 'SD(0.9)';
update pruts.t0sediment set idobservation_type = 23 where sed_parameter = 'slib precentage <16 mu';
update pruts.t0sediment set idobservation_type = 22 where sed_parameter = 'Percentage Coarse-zand';
update pruts.t0sediment set idobservation_type = 24 where sed_parameter = 'SpSA';
update pruts.t0sediment set idobservation_type = 31 where sed_parameter = 'SD50PHIm';
Update pruts.t0sediment set idobservation_type = 22 where sed_parameter = 'Svfines%';
update pruts.t0sediment set idobservation_type = 28 where sed_parameter = 'SD50MUm';
update pruts.t0sediment set idobservation_type = 25 where sed_parameter = 'SD(0.1)';
update pruts.t0sediment set idobservation_type = 22 where sed_parameter = 'Sfines%';
update pruts.t0sediment set idobservation_type = 23 where sed_parameter = 'Slib percentage <4mu';
update pruts.t0sediment set idobservation_type = 23 where sed_parameter = 'slib percentage <50 mu';
update pruts.t0sediment set idobservation_type = 23 where sed_parameter = 'slib percentage <63 mu';
update pruts.t0sediment set idobservation_type = 31 where sed_parameter = 'Standaard deviatie';
update pruts.t0sediment set idobservation_type = 23 where sed_parameter = 'Slib percentage <8mu';

update pruts.t1sediment set idobservation_type = 23 where param = 'Slib percentage <2mu';
update pruts.t1sediment set idobservation_type = 23 where param = 'Slib percentage <32mu';
update pruts.t1sediment set idobservation_type = 22 where param = 'Smedium%';
update pruts.t1sediment set idobservation_type = 26 where param = 'SD(0.9)';
update pruts.t1sediment set idobservation_type = 23 where param = 'slib precentage <16 mu';
update pruts.t1sediment set idobservation_type = 22 where param = 'Percentage Coarse-zand';
update pruts.t1sediment set idobservation_type = 24 where param = 'SpSA';
update pruts.t1sediment set idobservation_type = 31 where param = 'SD50PHIm';
Update pruts.t1sediment set idobservation_type = 22 where param = 'Svfines%';
update pruts.t1sediment set idobservation_type = 28 where param = 'SD50MUm';
update pruts.t1sediment set idobservation_type = 25 where param = 'SD(0.1)';
update pruts.t1sediment set idobservation_type = 22 where param = 'Sfines%';
update pruts.t1sediment set idobservation_type = 23 where param = 'Slib percentage <4mu';
update pruts.t1sediment set idobservation_type = 23 where param = 'slib percentage <50 mu';
update pruts.t1sediment set idobservation_type = 23 where param = 'slib percentage <63 mu';
update pruts.t1sediment set idobservation_type = 31 where param = 'Standaard deviatie';
update pruts.t1sediment set idobservation_type = 23 where param = 'Slib percentage <8mu';

update pruts.t2sediment set idobservation_type = 23 where parameter_type = 'Slib percentage <2mu';
update pruts.t2sediment set idobservation_type = 23 where parameter_type = 'Slib percentage <32mu';
update pruts.t2sediment set idobservation_type = 22 where parameter_type = 'Smedium%';
update pruts.t2sediment set idobservation_type = 26 where parameter_type = 'SD(0.9)';
update pruts.t2sediment set idobservation_type = 23 where parameter_type = 'slib precentage <16 mu';
update pruts.t2sediment set idobservation_type = 22 where parameter_type = 'Percentage Coarse-zand';
update pruts.t2sediment set idobservation_type = 24 where parameter_type = 'SpSA';
update pruts.t2sediment set idobservation_type = 31 where parameter_type = 'SD50PHIm';
Update pruts.t2sediment set idobservation_type = 22 where parameter_type = 'Svfines%';
update pruts.t2sediment set idobservation_type = 28 where parameter_type = 'SD50MUm';
update pruts.t2sediment set idobservation_type = 25 where parameter_type = 'SD(0.1)';
update pruts.t2sediment set idobservation_type = 22 where parameter_type = 'Sfines%';
update pruts.t2sediment set idobservation_type = 23 where parameter_type = 'Slib percentage <4mu';
update pruts.t2sediment set idobservation_type = 23 where parameter_type = 'slib percentage <50 mu';
update pruts.t2sediment set idobservation_type = 23 where parameter_type = 'slib percentage <63 mu';
update pruts.t2sediment set idobservation_type = 31 where parameter_type = 'Standaard deviatie';
update pruts.t2sediment set idobservation_type = 23 where parameter_type = 'Slib percentage <8mu';

update pruts.t0sediment set idunit = 26 where sed_parameter = 'Slib percentage <2mu';
update pruts.t0sediment set idunit = 26 where sed_parameter = 'Slib percentage <32mu';
update pruts.t0sediment set idunit = 25 where sed_parameter = 'Smedium%';
update pruts.t0sediment set idunit = 29 where sed_parameter = 'SD(0.9)';
update pruts.t0sediment set idunit = 26 where sed_parameter = 'slib precentage <16 mu';
update pruts.t0sediment set idunit = 25 where sed_parameter = 'Percentage Coarse-zand';
update pruts.t0sediment set idunit = 34 where sed_parameter = 'SpSA';
update pruts.t0sediment set idunit = 31 where sed_parameter = 'SD50PHIm';
Update pruts.t0sediment set idunit = 25 where sed_parameter = 'Svfines%';
update pruts.t0sediment set idunit = 30 where sed_parameter = 'SD50MUm';
update pruts.t0sediment set idunit = 28 where sed_parameter = 'SD(0.1)';
update pruts.t0sediment set idunit = 25 where sed_parameter = 'Sfines%';
update pruts.t0sediment set idunit = 26 where sed_parameter = 'Slib percentage <4mu';
update pruts.t0sediment set idunit = 26 where sed_parameter = 'slib percentage <50 mu';
update pruts.t0sediment set idunit = 26 where sed_parameter = 'slib percentage <63 mu';
update pruts.t0sediment set idunit = 31 where sed_parameter = 'Standaard deviatie';
update pruts.t0sediment set idunit = 26 where sed_parameter = 'Slib percentage <8mu';

update pruts.t1sediment set idunit = 26 where param = 'Slib percentage <2mu';
update pruts.t1sediment set idunit = 26 where param = 'Slib percentage <32mu';
update pruts.t1sediment set idunit = 25 where param = 'Smedium%';
update pruts.t1sediment set idunit = 29 where param = 'SD(0.9)';
update pruts.t1sediment set idunit = 26 where param = 'slib precentage <16 mu';
update pruts.t1sediment set idunit = 25 where param = 'Percentage Coarse-zand';
update pruts.t1sediment set idunit = 34 where param = 'SpSA';
update pruts.t1sediment set idunit = 31 where param = 'SD50PHIm';
Update pruts.t1sediment set idunit = 25 where param = 'Svfines%';
update pruts.t1sediment set idunit = 30 where param = 'SD50MUm';
update pruts.t1sediment set idunit = 28 where param = 'SD(0.1)';
update pruts.t1sediment set idunit = 25 where param = 'Sfines%';
update pruts.t1sediment set idunit = 26 where param = 'Slib percentage <4mu';
update pruts.t1sediment set idunit = 26 where param = 'slib percentage <50 mu';
update pruts.t1sediment set idunit = 26 where param = 'slib percentage <63 mu';
update pruts.t1sediment set idunit = 31 where param = 'Standaard deviatie';
update pruts.t1sediment set idunit = 26 where param = 'Slib percentage <8mu';

update pruts.t2sediment set idunit = 26 where parameter_type = 'Slib percentage <2mu';
update pruts.t2sediment set idunit = 26 where parameter_type = 'Slib percentage <32mu';
update pruts.t2sediment set idunit = 25 where parameter_type = 'Smedium%';
update pruts.t2sediment set idunit = 29 where parameter_type = 'SD(0.9)';
update pruts.t2sediment set idunit = 26 where parameter_type = 'slib precentage <16 mu';
update pruts.t2sediment set idunit = 25 where parameter_type = 'Percentage Coarse-zand';
update pruts.t2sediment set idunit = 34 where parameter_type = 'SpSA';
update pruts.t2sediment set idunit = 31 where parameter_type = 'SD50PHIm';
Update pruts.t2sediment set idunit = 25 where parameter_type = 'Svfines%';
update pruts.t2sediment set idunit = 30 where parameter_type = 'SD50MUm';
update pruts.t2sediment set idunit = 28 where parameter_type = 'SD(0.1)';
update pruts.t2sediment set idunit = 25 where parameter_type = 'Sfines%';
update pruts.t2sediment set idunit = 26 where parameter_type = 'Slib percentage <4mu';
update pruts.t2sediment set idunit = 26 where parameter_type = 'slib percentage <50 mu';
update pruts.t2sediment set idunit = 26 where parameter_type = 'slib percentage <63 mu';
update pruts.t2sediment set idunit = 31 where parameter_type = 'Standaard deviatie';
update pruts.t2sediment set idunit = 26 where parameter_type = 'Slib percentage <8mu';

update pruts.t0sediment set idparameter = 42532 where sed_parameter = 'Slib percentage <2mu';
update pruts.t0sediment set idparameter = 42533 where sed_parameter = 'Slib percentage <32mu';
update pruts.t0sediment set idparameter = 42530 where sed_parameter = 'Smedium%';
update pruts.t0sediment set idparameter = 42525 where sed_parameter = 'SD(0.9)';
update pruts.t0sediment set idparameter = 42531 where sed_parameter = 'slib precentage <16 mu';
update pruts.t0sediment set idparameter = 42526 where sed_parameter = 'Percentage Coarse-zand';
update pruts.t0sediment set idparameter = 42545 where sed_parameter = 'SpSA';
update pruts.t0sediment set idparameter = 42529 where sed_parameter = 'SD50PHIm';
Update pruts.t0sediment set idparameter = 42540 where sed_parameter = 'Svfines%';
update pruts.t0sediment set idparameter = 42528 where sed_parameter = 'SD50MUm';
update pruts.t0sediment set idparameter = 42524 where sed_parameter = 'SD(0.1)';
update pruts.t0sediment set idparameter = 42527 where sed_parameter = 'Sfines%';
update pruts.t0sediment set idparameter = 42534 where sed_parameter = 'Slib percentage <4mu';
update pruts.t0sediment set idparameter = 42535 where sed_parameter = 'slib percentage <50 mu';
update pruts.t0sediment set idparameter = 42536 where sed_parameter = 'slib percentage <63 mu';
update pruts.t0sediment set idparameter = 42539 where sed_parameter = 'Standaard deviatie';
update pruts.t0sediment set idparameter = 42537 where sed_parameter = 'Slib percentage <8mu';

update pruts.t1sediment set idparameter = 42532 where param = 'Slib percentage <2mu';
update pruts.t1sediment set idparameter = 42533 where param = 'Slib percentage <32mu';
update pruts.t1sediment set idparameter = 42530 where param = 'Smedium%';
update pruts.t1sediment set idparameter = 42525 where param = 'SD(0.9)';
update pruts.t1sediment set idparameter = 42531 where param = 'slib precentage <16 mu';
update pruts.t1sediment set idparameter = 42526 where param = 'Percentage Coarse-zand';
update pruts.t1sediment set idparameter = 42545 where param = 'SpSA';
update pruts.t1sediment set idparameter = 42529 where param = 'SD50PHIm';
Update pruts.t1sediment set idparameter = 42540 where param = 'Svfines%';
update pruts.t1sediment set idparameter = 42528 where param = 'SD50MUm';
update pruts.t1sediment set idparameter = 42524 where param = 'SD(0.1)';
update pruts.t1sediment set idparameter = 42527 where param = 'Sfines%';
update pruts.t1sediment set idparameter = 42534 where param = 'Slib percentage <4mu';
update pruts.t1sediment set idparameter = 42535 where param = 'slib percentage <50 mu';
update pruts.t1sediment set idparameter = 42536 where param = 'slib percentage <63 mu';
update pruts.t1sediment set idparameter = 42539 where param = 'Standaard deviatie';
update pruts.t1sediment set idparameter = 42537 where param = 'Slib percentage <8mu';

update pruts.t2sediment set idparameter = 42532 where parameter_type = 'Slib percentage <2mu';
update pruts.t2sediment set idparameter = 42533 where parameter_type = 'Slib percentage <32mu';
update pruts.t2sediment set idparameter = 42530 where parameter_type = 'Smedium%';
update pruts.t2sediment set idparameter = 42525 where parameter_type = 'SD(0.9)';
update pruts.t2sediment set idparameter = 42531 where parameter_type = 'slib precentage <16 mu';
update pruts.t2sediment set idparameter = 42526 where parameter_type = 'Percentage Coarse-zand';
update pruts.t2sediment set idparameter = 42545 where parameter_type = 'SpSA';
update pruts.t2sediment set idparameter = 42529 where parameter_type = 'SD50PHIm';
Update pruts.t2sediment set idparameter = 42540 where parameter_type = 'Svfines%';
update pruts.t2sediment set idparameter = 42528 where parameter_type = 'SD50MUm';
update pruts.t2sediment set idparameter = 42524 where parameter_type = 'SD(0.1)';
update pruts.t2sediment set idparameter = 42527 where parameter_type = 'Sfines%';
update pruts.t2sediment set idparameter = 42534 where parameter_type = 'Slib percentage <4mu';
update pruts.t2sediment set idparameter = 42535 where parameter_type = 'slib percentage <50 mu';
update pruts.t2sediment set idparameter = 42536 where parameter_type = 'slib percentage <63 mu';
update pruts.t2sediment set idparameter = 42539 where parameter_type = 'Standaard deviatie';
update pruts.t2sediment set idparameter = 42537 where parameter_type = 'Slib percentage <8mu';

select distinct (date) from pruts.t2sediment

select distinct (monsterdatum) from pruts.t0sediment

select * from pruts.t2benthos where idparameter is null

delete from observation where idobservation is not null

select setval('observation_idobservation_seq', 1)

update pruts.t0sediment set idlocation = null where idmethod = 46;
update pruts.t1sediment set idlocation = null where idmethod = 46;
update pruts.t2sediment set idlocation = null where idmethod = 46;
update pruts.t0sediment set geom = null where idmethod = 46;
update pruts.t1sediment set geom = null where idmethod = 46;
update pruts.t2sediment set geom = null where idmethod = 46

update pruts.t2sediment set geom = st_transform(st_setsrid(st_point(wgs84_e,wgs84_n),4326),4326)

update pruts.t2sediment s
set idlocation = l.idlocation
from location l
where st_within(l.thegeometry, st_buffer( st_transform(s.geom, 4326) , 8.1818181818181818181818181818182e-6))
and s.idlocation is null

select * from pruts.t2sediment where idobservation_type = 31

update pruts.t2sediment set idobservation_type = 28 where idunit = 31

insert into observation 
 (idmethod,idunit,date,value,idlocation,idparameter,idobservation_type,idsample)
select
 i.idmethod
,i.idunit
,i.monsterdatum
,i.value
,i.idlocation
,i.idparameter
,i.idobservation_type
,i.locid
FROM
 pruts.t0sediment i where monsterdatum='2007-10-17';

insert into observation 
 (idmethod,idunit,date,value,idlocation,idparameter,idobservation_type,idsample)
select
 i.idmethod
,i.idunit
,i.sampling_date
,i.value
,i.idlocation
,i.idparameter
,i.idobservation_type
,i.locid
FROM
 pruts.t1sediment i where sampling_date = '2008-10-20';

 select distinct (sampling_date) from pruts.t1sediment 

insert into observation 
 (idmethod,idunit,date,value,idlocation,idparameter,idobservation_type,idsample)
select
 i.idmethod
,i.idunit
,i.date
,i.value
,i.idlocation
,i.idparameter
,i.idobservation_type
,i.locid
FROM
 pruts.t2sediment i where date = '2009-04-01';

 insert into observation 
 (idmethod,idunit,date,value,idlocation,idparameter,idobservation_type,idsample)
select
 i.idmethod
,i.idunit
,i.date
,i.value
,i.idlocation
,i.idparameter
,i.idobservation_type
,i.locid
FROM
 pruts.t2sediment i where date = '2009-07-14';

 insert into observation 
 (idmethod,idunit,date,value,idlocation,idparameter,idobservation_type,idsample)
select
 i.idmethod
,i.idunit
,i.date
,i.value
,i.idlocation
,i.idparameter
,i.idobservation_type
,i.locid
FROM
 pruts.t2sediment i where date = '2008-07-04';