back to saving and loading the database
back to top ..

-- 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

-- 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

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

-- projection on the nearest segment
DROP TABLE IF EXISTS ireland.ireland_station_ccm_500;
CREATE TABLE ireland.ireland_station_ccm_500 as (
        SELECT distinct on (locationcode) locationcode as st_id , gid, wso1_id, min(distance) as distance, the_geom FROM (
               SELECT locationcode, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM ireland.stations As s
               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500)
               WHERE s.the_geom IS NOT NULL
               ORDER BY locationcode) AS sub 
        GROUP BY locationcode,distance, gid, wso1_id,  the_geom
);
alter table ireland.ireland_station_ccm_500 add column id serial;
-- mise à jour de la table geometry_columns
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'ireland', 'ireland_station_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM ireland.ireland_station_ccm_500 LIMIT 1;
-- creation d'index, clé primaire, et constraintes qui vont bien
alter table ireland.ireland_station_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table ireland.ireland_station_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table ireland.ireland_station_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table ireland.ireland_station_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
CREATE INDEX indexstation_ccm_500 ON ireland.ireland_station_ccm_500
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

dbeel integration from 23/03/2012

source:"eda/EDAcommun/sql/implementation_pose/db_feeding_ireland.sql"

Old data were missing, adaptation to load all stations from dbeel including oldest

When first running the EDA program with Elvira we discovered to our utter schock that the "old" data from POSE project were not included. We were dumpfolded so I strived to get them back in the script below which correct scrip above.
note from cedric The lakes are not included lfnsxxx as we have no geometry in the table, so they are not projected but are in the observation place table

ireland.ireland_station_ccm_500
create table ireland.observation_places as(
select *  from dbeel.observation_places where op_gis_systemname='wrbd');
-- g:/iav/ireland
-- pg_dump -U postgres -f "observation_places.sql" -- table ireland.observation_places --verbose dbeel
-- psql -U postgres -f "observation_places.sql" --verbose eda2

DROP TABLE IF EXISTS ireland.ireland_station_ccm_500;
CREATE TABLE ireland.ireland_station_ccm_500 as (
        SELECT distinct on (op_id) op_id as st_id , gid, wso1_id, min(distance) as distance, the_geom FROM (
               SELECT op_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM ireland.observation_places As s
               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500)
               WHERE s.the_geom IS NOT NULL
               ) AS sub 
        GROUP BY op_id,distance, gid, wso1_id,  the_geom
);
alter table ireland.ireland_station_ccm_500 add column id serial;
-- mise à jour de la table geometry_columns
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'ireland', 'ireland_station_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM ireland.ireland_station_ccm_500 LIMIT 1;
-- creation d'index, clé primaire, et constraintes qui vont bien
alter table ireland.ireland_station_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table ireland.ireland_station_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table ireland.ireland_station_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table ireland.ireland_station_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
CREATE INDEX indexstation_ccm_500 ON ireland.ireland_station_ccm_500
  USING GIST ( the_geom GIST_GEOMETRY_OPS );