back to saving and loading the database with elvira?
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--