back to  ["saving and loading the database with elvira"] [[BR]]
back to top [..][[BR]]

{{{
#!sql
-- load this into dbeel
create schema ireland;
drop table if exists ireland.stations;
create table ireland.stations(
id serial,
locationcode integer,
sourcefile text,
easting numeric,
northing numeric,
catchment text,
waterbody text,
site text,
sitecode text);


-- the file is on the key
copy ireland.stations (
	locationcode,
        sourcefile ,
        easting ,
        northing ,
        catchment,
        waterbody,
        site ,
        sitecode)
         from 'C:/Documents and Settings/edeeyto/Desktop/EDA France Jan 12/gis.csv' with CSV header delimiter as ',' NULL as 'NAN'; --975
drop table if exists ireland.survey;
create table ireland.survey(
surveyid serial,
sourcefile text,
easting numeric,
northing numeric,
thedate date,
theyear integer,
catchment text,
waterbody text,
site text,
sitecode text,
sitelength numeric,
siteaveragewettedwidth numeric,
methodology text,
efishingunits integer,
numfishingpasses integer,
sitewettedarea numeric,
fishing1 integer,
fishing2 integer,
fishing3 integer,
fishing4 integer,
fishing5 integer,
totaleel integer,
estimate_for_site numeric,
ci_for_site numeric,
numbersperm2 numeric,
ciperm2 numeric,
catchability_p numeric,
percentageci numeric,
minest numeric,
densityperm2 numeric,
surveycode text,
samplingcode text,
locationcode integer);

copy ireland.survey(
	sourcefile,
	easting,
	northing,
	thedate,
	theyear,
	catchment,
	waterbody,
	site,
	sitecode,
	sitelength,
	siteaveragewettedwidth,
	methodology,
	efishingunits,
	numfishingpasses,
	sitewettedarea,
	fishing1,
	fishing2,
	fishing3,
	fishing4,
	fishing5,
	totaleel,
	estimate_for_site,
	ci_for_site,
	numbersperm2,
	ciperm2,
	catchability_p,
	percentageci,
	minest,
	densityperm2,
	surveycode,
	samplingcode,
	locationcode)
        from 'C:/Documents and Settings/edeeyto/Desktop/EDA France Jan 12/survey.csv' with CSV header delimiter as ';'; --1256

drop table if exists ireland.fishlength;
create table ireland.fishlength(
	fishlengthid serial primary key,
	sourcefile text,
	samplingcode text,
	length_cm numeric,
	weight numeric,
	locationcode integer);
	        
copy ireland.fishlength ( sourcefile ,
	samplingcode,
	length_cm,
	weight,
	locationcode) from 'f:/fishlength.csv' with CSV header delimiter as ',' NULL as 'NAN'; --4085
}}}


to add these files to QGS

{{{
#!sql
-- select  * from ireland.stations;
set search_path to ireland, public;
alter table stations add constraint c_uk_locationcode unique (locationcode);


-- select * from ireland.survey;
-- the joining works fine
-- select * from ireland.survey u join ireland.stations s on s.locationcode=u.locationcode;
SELECT AddGeometryColumn('ireland', 'stations','the_geom', 29903,'POINT',2); 
UPDATE stations SET the_geom=PointFromText('POINT(' || easting || ' ' || northing || ')',29903);
alter table stations drop constraint enforce_srid_the_geom;
update stations set the_geom=ST_TRANSFORM(the_geom,3035); --975
alter table stations add constraint  enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

--SELECT * from geometry_columns where f_table_schema ='ireland';
update geometry_columns set srid=3035  where f_table_schema ='ireland';--1


SELECT AddGeometryColumn('ireland', 'survey','the_geom', 29903,'POINT',2); 
UPDATE survey SET the_geom=PointFromText('POINT(' || easting || ' ' || northing || ')',29903);--1256
alter table survey drop constraint enforce_srid_the_geom;
update survey set the_geom=ST_TRANSFORM(the_geom,3035); --975
alter table survey add constraint  enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

--SELECT * from geometry_columns where f_table_schema ='ireland';
update geometry_columns set srid=3035  where f_table_schema ='ireland';--1
}}}


to export files from postgres e.g. for viewing in excel

{{{
#!sql
select * from ireland.stations
select * from ireland.survey
select * from ireland.fishlength

--then chose export from the file menu--
}}}


{{{

/*
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
*/
}}}