Materials presented at the PostgreSQL/PostGIS data workshop.
Introduction
PostgreSQL/PostGIS Installation
Datamodel
A very simple data model is create based on data that is downloaded from the ICES website (http://ocean.ices.dk/HydChem/HydChem.aspx?plot=yes). The data model is based on downloaded CTD data. Minimize the data download (can take a long time, by using the the various filter options).
In pgAdminIII open the query window and copy + paste the data model below. This code block contains a 1-table datamodel for training purposes. For real pourposes, use the elaborate data model in the subsequent code block.
-- Cruise,Station,Type,yyyy-mm-ddThh:mm,Latitude [degrees_north],Longitude [degrees_east],Bot. Depth [m],PRES [db],TEMP [deg C],PSAL [psu] -- 64HO,0307,B,1990-01-03T07:00,51.4220,3.5570,,0,6.30,31.030 CREATE table tempdata ( cruise character varying (50), station character varying (50), type character varying (2), adate timestamp, latitude float, longitude float, botdepth float, pres float, temp float, psal float, geom public.geometry) -- NB by default a column is nullable. -- This means we can insert the 1st 10 columns from csv, and leave the geom open. -- Use PGAdmin to insert the csv or use command line "c:\Program Files\PostgreSQL\9.5\bin\psql.exe" -h localhost -U postgres -d ices1 -p 5432 copy tempdata(Cruise,Station,Type,yyyy-mm-ddThh:mm,Latitude [degrees_north],Longitude [degrees_east],Bot. Depth [m],PRES [db],TEMP [deg C],PSAL [psu]) from x.csv with csv header delimiter ‘,’; -- You can also add the geom column also afterwards. -- alter table tempdata add column geom public.geometry -- now fill the geom column using the loaded lat and lon columns update tempdata set geom=ST_SetSRID((ST_POINT(longitude,latitude)),4326); -- check whether this worked select count(*) from tempdata where geom is not null;
For more advanced
data model
ALTER SEQUENCE public.parameter_parameterid_seq OWNED BY public.parameter.parameterid;
CREATE SEQUENCE public.icesstation_stationid_seq;
CREATE TABLE public.icesstation (
stationid INTEGER NOT NULL DEFAULT nextval('public.icesstation_stationid_seq'),
name VARCHAR(50) NOT NULL,
geom public.geometry,
CONSTRAINT icesstation_pk PRIMARY KEY (stationid)
);
ALTER SEQUENCE public.icesstation_stationid_seq OWNED BY public.icesstation.stationid;
CREATE TABLE public.oceanography (
oceanid INTEGER NOT NULL,
stationid INTEGER NOT NULL,
parameterid INTEGER,
datetime TIMESTAMP,
pressure DOUBLE PRECISION,
bot_depth DOUBLE PRECISION NOT NULL,
value DOUBLE PRECISION,
CONSTRAINT oceanography_pk PRIMARY KEY (oceanid)
);
ALTER TABLE public.oceanography ADD CONSTRAINT parameter_oceanography_fk
FOREIGN KEY (parameterid)
REFERENCES public.parameter (parameterid)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
ALTER TABLE public.oceanography ADD CONSTRAINT icesstation_oceanography_fk
FOREIGN KEY (stationid)
REFERENCES public.icesstation (stationid)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;
