PostgreSQL/PostGIS at DSD2016

Datamodel

A very simple data model is create based on data that is downloaded from the ICES website. The data model is based on downloaded CTD data.

In pgAdminIII open the query window and copy + paste the data model below.

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;

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;