---------------------------------------------------------------------------------------------------------------------------------------------------
-- SQL Script for Creating the Arc Marine Schema in PostgreSQL/PostGIS
-- Developed by: Yassine Lassoued
-- Affiliation: Coastal and Marine Resources Centre (CMRC) - University College Cork
-- Project: Geoscientific Data Integration (GeoDI)
-- Funding Body: National Development Plan (NDP, Ireland)
-- Programme: Sea Change
-- Description:
-- This SQL script creates a schema named "arcmarine" within your PostgreSQL/PostGIS database and creates the
-- Arc Marine feature classes and tables within this schema. All ID fields are incremental.
-- Instructions:
-- Execute this script by running the following command line:
-- psql arcmarine.sql
-- If the server is not on your computer, you will need to specify the hostname to the computer and a username to
-- log into the server with:
-- psql -h server.postgres.com -U dba arcmarine.sql
-- You may want to rename the schema from 'arcmarine' to 'public' or whatsoever but you need to make sure that you replace
-- 'arcmarine' with the new schema name in the Schema definition section and in all tables and views definitions
-- The default spatial reference system for the geometry field is WGS84 which is common to all feature classes. Again, you may
-- want to change the spatial reference system. To do so, you need to change the value of the mySRDI (my spatial reference
-- identifier) variable in the 'Spatial reference system' section. If you want to use different spatial references for the feature
-- classes then specify the reference system identifier for each table in the 'Specify geometry fields' section.
---------------------------------------------------------------------------------------------------------------------------------------------------




---------------------------------------------------------------------------------------------------------------------------------------------------
-- Schema: "arcmarine"

-- DROP SCHEMA arcmarine;

CREATE SCHEMA arcmarine
  AUTHORIZATION postgres;
COMMENT ON SCHEMA arcmarine IS 'Arc Marine is the result of a successful six-year effort to create and define a data model for the marine science and resource management community. It has been developed by researchers from Oregon State University, Duke University, National Oceanic and Atmospheric Administration (NOAA), the Danish Hydrologic Institute, and ESRI.
Arc Marine is based on the Common Marine Data Types, a general framework for envisioning the core feature classes required to represent coastal and marine data. The Common Marine Data Types extend standard geospatial features in order to cover, in a generic and inclusive way, spatially and temporally dynamic processes in a three-dimensional volume. They include explicit relationships between spatial, temporal and depth (volume) referencing.
The marine data types defined in Arc Marine facilitate management of complex spatial analyses within a variety of marine application, including mapping the ocean floor, fisheries management in the water column, marine animal tracking in the water column, and on the sea surface, near-shore and shoreline change, temporal analysis of water temperature, and the integration of physical oceanographic numerical models.';




CREATE FUNCTION getSRID() RETURNS integer AS $$
DECLARE mySRID integer := 4326;
BEGIN
    RETURN mySRID;
END;
$$ LANGUAGE plpgsql;



---------------------------------------------------------------------------------------------------------------------------------------------------
-- Sequences

-- Sequence: arcmarine."objectid_seq"

-- DROP SEQUENCE arcmarine."objectid_seq";

CREATE SEQUENCE arcmarine."objectid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."objectid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."objectid_seq" IS 'A sequence for autoincrementing the Object.OBJECTID key attribute';


-- Sequence: arcmarine."featureid_seq"

-- DROP SEQUENCE arcmarine."featureid_seq";

CREATE SEQUENCE arcmarine."featureid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."featureid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."featureid_seq" IS 'A sequence for autoincrementing the MarineFeature.FeatureID key attribute';


-- Sequence: arcmarine."surveyid_seq"

-- DROP SEQUENCE arcmarine."surveyid_seq";

CREATE SEQUENCE arcmarine."surveyid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."surveyid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."surveyid_seq" IS 'A sequence for autoincrementing the SurveyInfo.SurveyID key attribute';


-- Sequence: arcmarine."deviceid_seq"

-- DROP SEQUENCE arcmarine."deviceid_seq";

CREATE SEQUENCE arcmarine."deviceid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."deviceid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."deviceid_seq" IS 'A sequence for autoincrementing the MeasuringDevice.DeviceID key attribute';


-- Sequence: arcmarine."vehicleid_seq"

-- DROP SEQUENCE arcmarine."vehicleid_seq";

CREATE SEQUENCE arcmarine."vehicleid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."vehicleid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."vehicleid_seq" IS 'A sequence for autoincrementing the Vehicle.VehicleID key attribute';


-- Sequence: arcmarine."seriesid_seq"

-- DROP SEQUENCE arcmarine."seriesid_seq";

CREATE SEQUENCE arcmarine."seriesid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."seriesid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."seriesid_seq" IS 'A sequence for autoincrementing the Series.SeriesID key attribute';


-- Sequence: arcmarine."cruiseid_seq"

-- DROP SEQUENCE arcmarine."cruiseid_seq";

CREATE SEQUENCE arcmarine."cruiseid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."cruiseid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."cruiseid_seq" IS 'A sequence for autoincrementing the Cruise.CruiseID key attribute';


-- Sequence: arcmarine."trackid_seq"

-- DROP SEQUENCE arcmarine."trackid_seq";

CREATE SEQUENCE arcmarine."trackid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."trackid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."trackid_seq" IS 'A sequence for autoincrementing the Track.TrackID key attribute';


-- Sequence: arcmarine."marineeventid_seq"

-- DROP SEQUENCE arcmarine."marineeventid_seq";

CREATE SEQUENCE arcmarine."marineeventid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."marineeventid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."marineeventid_seq" IS 'A sequence for autoincrementing the MarineEvent.MarineEventID key attribute';


-- Sequence: arcmarine."parameterid_seq"

-- DROP SEQUENCE arcmarine."parameterid_seq";

CREATE SEQUENCE arcmarine."parameterid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."parameterid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."parameterid_seq" IS 'A sequence for autoincrementing the Parameter.ParameterID key attribute';


-- Sequence: arcmarine."meshid_seq"

-- DROP SEQUENCE arcmarine."meshid_seq";

CREATE SEQUENCE arcmarine."meshid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."meshid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."meshid_seq" IS 'A sequence for autoincrementing the Mesh.MeshID key attribute';


-- Sequence: arcmarine."tstypeid_seq"

-- DROP SEQUENCE arcmarine."tstypeid_seq";

CREATE SEQUENCE arcmarine."tstypeid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."tstypeid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."tstypeid_seq" IS 'A sequence for autoincrementing the TSType.TSTypeID key attribute';


-- Sequence: arcmarine."measurementid_seq"

-- DROP SEQUENCE arcmarine."measurementid_seq";

CREATE SEQUENCE arcmarine."measurementid_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 2147483647
  START 1
  CACHE 1;
ALTER TABLE arcmarine."measurementid_seq" OWNER TO postgres;
COMMENT ON SEQUENCE arcmarine."measurementid_seq" IS 'A sequence for autoincrementing the Measurement.MeasurementID key attribute';





























---------------------------------------------------------------------------------------------------------------------------------------------------
-- Coded Value Domains


-- Table: arcmarine."TSIntervalUnitType"

-- DROP TABLE arcmarine."TSIntervalUnitType";

CREATE TABLE arcmarine."TSIntervalUnitType"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "TSIntervalUnitType_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TSIntervalUnitType" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TSIntervalUnitType" IS 'This table defines the TSIntervalUnitType code value domain.';


-- Table: arcmarine."ParameterType"

-- DROP TABLE arcmarine."ParameterType";

CREATE TABLE arcmarine."ParameterType"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "ParameterType_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."ParameterType" OWNER TO postgres;
COMMENT ON TABLE arcmarine."ParameterType" IS 'This table defines the ParameterType coded value domain.';


-- Table: arcmarine."MeshType"

-- DROP TABLE arcmarine."MeshType";

CREATE TABLE arcmarine."MeshType"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "MeshType_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeshType" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeshType" IS 'This table defines the MeshType coded value domain.';


-- Table: arcmarine."TSIntervalType"

-- DROP TABLE arcmarine."TSIntervalType";

CREATE TABLE arcmarine."TSIntervalType"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "TSIntervalType_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TSIntervalType" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TSIntervalType" IS 'This table defines the TSIntervalType coded value domain';


-- Table: arcmarine."TSOrigins"

-- DROP TABLE arcmarine."TSOrigins";

CREATE TABLE arcmarine."TSOrigins"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "TSOrigins_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TSOrigins" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TSOrigins" IS 'This table defines the TSOrigins coded value domain';


-- Table: arcmarine."AHBoolean"

-- DROP TABLE arcmarine."AHBoolean";

CREATE TABLE arcmarine."AHBoolean"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "AHBoolean_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."AHBoolean" OWNER TO postgres;
COMMENT ON TABLE arcmarine."AHBoolean" IS 'This table defines the AHBoolean coded value domain';


-- Table: arcmarine."TSDataType"

-- DROP TABLE arcmarine."TSDataType";

CREATE TABLE arcmarine."TSDataType"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "TSDataType_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TSDataType" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TSDataType" IS 'This table defines the TSDataType coded value domain.';


-- Table: arcmarine."InstantaneousPointSubtype"

-- DROP TABLE arcmarine."InstantaneousPointSubtype";

CREATE TABLE arcmarine."InstantaneousPointSubtype"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "InstantaneousPointSubtype_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."InstantaneousPointSubtype" OWNER TO postgres;
COMMENT ON TABLE arcmarine."InstantaneousPointSubtype" IS 'This table defines subtypes of the InstantaneousPoint feature class.';


-- Table: arcmarine."MeshPointSubtype"

-- DROP TABLE arcmarine."MeshPointSubtype";

CREATE TABLE arcmarine."MeshPointSubtype"
(
  "Code" integer NOT NULL,
  "Description" character varying(100),
  CONSTRAINT "MeshPointSubtype_pkey" PRIMARY KEY ("Code")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeshPointSubtype" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeshPointSubtype" IS 'This table defines subtypes of the MeshPoint feature class';


---------------------------------------------------------------------------------------------------------------------------------------------------
-- Arc Marine Tables

-- Table: arcmarine."Object"

-- DROP TABLE arcmarine."Object";

CREATE TABLE arcmarine."Object"
(
  "OBJECTID" oid NOT NULL DEFAULT nextval('arcmarine.objectid_seq'),
  CONSTRAINT "Object_pkey" PRIMARY KEY ("OBJECTID")
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Object" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Object" IS 'An object represents a real world object, such as a fish habitat, an underwater volcano, or a marker buoy, etc.';


-- Table: arcmarine."Cruise"

-- DROP TABLE arcmarine."Cruise";

CREATE TABLE arcmarine."Cruise"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "CruiseID" integer NOT NULL DEFAULT nextval('arcmarine.cruiseid_seq'), -- This attribute is a unique identifier, database wide, for all Cruise records
  "Code" character varying(255), -- A user-defined code for a given cruise
  "Name" character varying(100), -- The name of the cruise
  "Purpose" character varying(255), -- The purpose of the cruise
  "Status" character varying(60), -- Defines the status of the cruise
  "Description" character varying(100), -- A general description of the cruise
  "StartDate" date, -- The beginning time stamp for the cruise
  "EndDate" date, -- The ending time stamp for the cruise
  "ShipName" character varying(100), -- The name of the ship participating in the cruise
  CONSTRAINT "Cruise_pkey" PRIMARY KEY ("CruiseID")
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Cruise" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Cruise" IS 'Cruise is an instantiable object class that stores the characteristics of a ship during an entire expedition. Example: Can be used for defining linear events against MarineLine Features, such as the tracklines of a ship during a cruise.';
COMMENT ON COLUMN arcmarine."Cruise"."CruiseID" IS 'This attribute is a unique identifier, database wide, for all Cruise records';
COMMENT ON COLUMN arcmarine."Cruise"."Code" IS 'A user-defined code for a given cruise';
COMMENT ON COLUMN arcmarine."Cruise"."Name" IS 'The name of the cruise';
COMMENT ON COLUMN arcmarine."Cruise"."Purpose" IS 'The purpose of the cruise';
COMMENT ON COLUMN arcmarine."Cruise"."Status" IS 'Defines the status of the cruise';
COMMENT ON COLUMN arcmarine."Cruise"."Description" IS 'A general description of the cruise';
COMMENT ON COLUMN arcmarine."Cruise"."StartDate" IS 'The beginning time stamp for the cruise';
COMMENT ON COLUMN arcmarine."Cruise"."EndDate" IS 'The ending time stamp for the cruise';
COMMENT ON COLUMN arcmarine."Cruise"."ShipName" IS 'The name of the ship participating in the cruise';


-- Table: arcmarine."MarineEvent"

-- DROP TABLE arcmarine."MarineEvent";

CREATE TABLE arcmarine."MarineEvent"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "MarineEventID" integer NOT NULL DEFAULT nextval('arcmarine.marineeventid_seq'), -- This attribute is a unique identifier, database wide, for all MarineEvent records.
  "FeatureID" integer, -- Associates a feature line or a feature point with the MarineEvent
  "FromLocation" double precision, -- Beginning location for a Linear Event, the location for a Point Event
  "ToLocation" double precision, -- Ending location for a Linear Event
  "DataValue" character varying(255), -- Value for the current event
  CONSTRAINT "MarineEvent_pkey" PRIMARY KEY ("MarineEventID")
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MarineEvent" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MarineEvent" IS 'MarineEvent is an instantiable object class for defining either linear or point events that can be applied referenced to any MarineLine Features with Measures (HasM). Examples: Shoreline Features: Land Classification, Tourism Areas, Wave Action, Erodability, Coastal Risk, Sediment Transport; Transects Features: Accretion/Erosion; Cruise Features: Something to do with time, where the Measures of a Cruise are in a Time format; Run Features: Something to do with time, where the Measures of a Run are in a Time format.';
COMMENT ON COLUMN arcmarine."MarineEvent"."MarineEventID" IS 'This attribute is a unique identifier, database wide, for all MarineEvent records.';
COMMENT ON COLUMN arcmarine."MarineEvent"."FeatureID" IS 'Associates a feature line or a feature point with the MarineEvent';
COMMENT ON COLUMN arcmarine."MarineEvent"."FromLocation" IS 'Beginning location for a Linear Event, the location for a Point Event';
COMMENT ON COLUMN arcmarine."MarineEvent"."ToLocation" IS 'Ending location for a Linear Event';
COMMENT ON COLUMN arcmarine."MarineEvent"."DataValue" IS 'Value for the current event';


-- Table: arcmarine."Series"

-- DROP TABLE arcmarine."Series";

CREATE TABLE arcmarine."Series"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "SeriesID" integer NOT NULL DEFAULT nextval('arcmarine.seriesid_seq'), -- This attribute is a unique identifier, database wide, for all Series records.
  CONSTRAINT "Series_pkey" PRIMARY KEY ("SeriesID")
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Series" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Series" IS 'Series is an instantiable object class for defining a Series from a collection of LocationSeriesPoints or TimeDurationAreas.';
COMMENT ON COLUMN arcmarine."Series"."SeriesID" IS 'This attribute is a unique identifier, database wide, for all Series records.';


-- Table: arcmarine."SurveyInfo"

-- DROP TABLE arcmarine."SurveyInfo";

CREATE TABLE arcmarine."SurveyInfo"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "SurveyID" integer NOT NULL DEFAULT nextval('arcmarine.surveyid_seq'), -- SurveyID is a unique number identifier, database wide, for all SurveyInfo objects.
  "StartDate" date, -- The beginning date of the survey
  "EndDate" date, -- The ending date of the survey
  "Description" character varying(255), -- A general description of the Survey
  "DeviceID" integer, -- A key field for relating a survey with a MeasuringDevice
  "TrackID" integer, -- A key field for relating a survey with a Track
  CONSTRAINT "SurveyInfo_pkey" PRIMARY KEY ("SurveyID")
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."SurveyInfo" OWNER TO postgres;
COMMENT ON TABLE arcmarine."SurveyInfo" IS 'SurveyInfo is an instantiable object class designed for storing information about a specific survey. Surveys generally consists of collecting data for specific areas of interest during a cruise. Then by various means, surveys collect samples and measure values at many locations. The number of locations can range from as few as tens of values, as with soil samples or fish counts, to as many as billions of locations using multibeam swath mapping equipment capturing depth or backscatter values. However, surveys are usually a collection of points, no matter the number.';
COMMENT ON COLUMN arcmarine."SurveyInfo"."SurveyID" IS 'SurveyID is a unique number identifier, database wide, for all SurveyInfo objects.';
COMMENT ON COLUMN arcmarine."SurveyInfo"."StartDate" IS 'The beginning date of the survey';
COMMENT ON COLUMN arcmarine."SurveyInfo"."EndDate" IS 'The ending date of the survey';
COMMENT ON COLUMN arcmarine."SurveyInfo"."Description" IS 'A general description of the Survey';
COMMENT ON COLUMN arcmarine."SurveyInfo"."DeviceID" IS 'A key field for relating a survey with a MeasuringDevice';
COMMENT ON COLUMN arcmarine."SurveyInfo"."TrackID" IS 'A key field for relating a survey with a Track';


-- Table: arcmarine."Vehicle"

-- DROP TABLE arcmarine."Vehicle";

CREATE TABLE arcmarine."Vehicle"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "VehicleID" integer NOT NULL DEFAULT nextval('arcmarine.vehicleid_seq'), -- A unique identifier, database wide, for all Vehicle records
  "Name" character varying(100), -- The name of the vehicle
  "Category" character varying(100), -- Text describing the type of a vehicle
  CONSTRAINT "Vehicle_pkey" PRIMARY KEY ("VehicleID")
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Vehicle" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Vehicle" IS 'Vehicle is an instantiable object class for storing information about the vehicle being used during a survey.';
COMMENT ON COLUMN arcmarine."Vehicle"."VehicleID" IS 'A unique identifier, database wide, for all Vehicle records';
COMMENT ON COLUMN arcmarine."Vehicle"."Name" IS 'The name of the vehicle';
COMMENT ON COLUMN arcmarine."Vehicle"."Category" IS 'Text describing the type of a vehicle';


-- Table: arcmarine."Measurement"

-- DROP TABLE arcmarine."Measurement";

CREATE TABLE arcmarine."Measurement"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "MeasurementID" integer NOT NULL DEFAULT nextval('arcmarine.measurementid_seq'), -- A unique identifier for the feature created by combining unique combinations of FeatureID and ZLocation
  "FeatureID" integer, -- A key field relating to the FeatureID of the Feature
  "FeatureClass" integer, -- The name of the feature class in which the relating feature participates
  "ZLocation" double precision, -- The value representing the depth being associated with the feature
  "XLocation" double precision, -- The X coordinate of the Measurement as it might vary from the X coordinate of the feature the Measurement is associated to
  "YLocation" double precision, -- The Y coordinate of the Measurement as it might vary from the Y coordinate of the feature the Measurement is associated to
  CONSTRAINT "Measurement_pkey" PRIMARY KEY ("MeasurementID")
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Measurement" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Measurement" IS 'Measurement is an instantiable object class designed for extending a single feature with multiple depths. Example: A position along a TC String, ADCP Bin, CTD Bottle.';
COMMENT ON COLUMN arcmarine."Measurement"."MeasurementID" IS 'A unique identifier for the feature created by combining unique combinations of FeatureID and ZLocation';
COMMENT ON COLUMN arcmarine."Measurement"."FeatureID" IS 'A key field relating to the FeatureID of the Feature';
COMMENT ON COLUMN arcmarine."Measurement"."FeatureClass" IS 'The name of the feature class in which the relating feature participates';
COMMENT ON COLUMN arcmarine."Measurement"."ZLocation" IS 'The value representing the depth being associated with the feature';
COMMENT ON COLUMN arcmarine."Measurement"."XLocation" IS 'The X coordinate of the Measurement as it might vary from the X coordinate of the feature the Measurement is associated to';
COMMENT ON COLUMN arcmarine."Measurement"."YLocation" IS 'The Y coordinate of the Measurement as it might vary from the Y coordinate of the feature the Measurement is associated to';


-- Table: arcmarine."MeasuringDevice"

-- DROP TABLE arcmarine."MeasuringDevice";

CREATE TABLE arcmarine."MeasuringDevice"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "DeviceID" integer NOT NULL DEFAULT nextval('arcmarine.deviceid_seq'), -- A unique identifier, database wide, for all MeasuringDevice objects
  "Name" character varying(100), -- Name of the device
  "Description" character varying(100), -- Description of the device
  "VehicleID" integer, -- A key field relating this table to the Vehicles table
  CONSTRAINT "MeasuringDevice_pkey" PRIMARY KEY ("DeviceID")
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeasuringDevice" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeasuringDevice" IS 'MeasuringDevice is an instantiable object class that stores information pertaining to the device taking the measurements.';
COMMENT ON COLUMN arcmarine."MeasuringDevice"."DeviceID" IS ' A unique identifier, database wide, for all MeasuringDevice objects';
COMMENT ON COLUMN arcmarine."MeasuringDevice"."Name" IS 'Name of the device';
COMMENT ON COLUMN arcmarine."MeasuringDevice"."Description" IS 'Description of the device';
COMMENT ON COLUMN arcmarine."MeasuringDevice"."VehicleID" IS 'A key field relating this table to the Vehicles table';


-- Table: arcmarine."Parameter"

-- DROP TABLE arcmarine."Parameter";

CREATE TABLE arcmarine."Parameter"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "ParameterID" integer NOT NULL DEFAULT nextval('arcmarine.parameterid_seq'), -- A unique identifier, database wide, for all Parameter records
  "Name" character varying(255), -- The name of a parameter
  "Description" character varying(255), -- The description of a parameter
  "Quantity" integer DEFAULT 1, -- The quantity type for a parameter, this refers to a ParameterType code (ParameterType.Code). Values are:...
  "Unit" character varying(255), -- The unit of measure used for a parameter
  "SignificantDigits" integer, -- The number of significant digits defining the precision of this parameter
  CONSTRAINT "Parameter_pkey" PRIMARY KEY ("ParameterID"),
  CONSTRAINT "ParameterHasParameterType_fkey" FOREIGN KEY ("Quantity")
      REFERENCES arcmarine."ParameterType" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Parameter" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Parameter" IS 'Parameter is an instantiable object class that stores information about the parameters being measured.';
COMMENT ON COLUMN arcmarine."Parameter"."ParameterID" IS 'A unique identifier, database wide, for all Parameter records';
COMMENT ON COLUMN arcmarine."Parameter"."Name" IS 'The name of a parameter';
COMMENT ON COLUMN arcmarine."Parameter"."Description" IS 'The description of a parameter';
COMMENT ON COLUMN arcmarine."Parameter"."Quantity" IS 'The quantity type for a parameter, this refers to a ParameterType code (ParameterType.Code). Values are:
1 = Other
2 = Scalar
3 = Vector';
COMMENT ON COLUMN arcmarine."Parameter"."Unit" IS 'The unit of measure used for a parameter ';
COMMENT ON COLUMN arcmarine."Parameter"."SignificantDigits" IS 'The number of significant digits defining the precision of this parameter';


-- Table: arcmarine."MeasuredData"

-- DROP TABLE arcmarine."MeasuredData";

CREATE TABLE arcmarine."MeasuredData"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "MeasurementID" integer NOT NULL, -- A foreign key field for relating this table to the measurement table. It refers to Measurement.MeasurementID.
  "ParameterID" integer NOT NULL, -- A key field for relating this table to the Parameter table. It refers to Parameter.ParameterID.
  "DeviceID" integer NOT NULL, -- A key field for relating this table to the MeasuringDevice table. It refers to MeasuringDevice.DeviceID.
  "DataValue" double precision, -- The recorded value
  CONSTRAINT "MeasuredData_pkey" PRIMARY KEY ("MeasurementID", "ParameterID", "DeviceID"),
  CONSTRAINT "MeasurementHasData_fkey" FOREIGN KEY ("MeasurementID")
      REFERENCES arcmarine."Measurement" ("MeasurementID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "MeasuringDeviceHasData_fkey" FOREIGN KEY ("DeviceID")
      REFERENCES arcmarine."MeasuringDevice" ("DeviceID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "ParameterHasData_fkey" FOREIGN KEY ("ParameterID")
      REFERENCES arcmarine."Parameter" ("ParameterID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeasuredData" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeasuredData" IS 'MeasuredData is an instantiable object class that stores recorded values for a given parameter.';
COMMENT ON COLUMN arcmarine."MeasuredData"."MeasurementID" IS 'A foreign key field for relating this table to the measurement table. It refers to Measurement.MeasurementID.';
COMMENT ON COLUMN arcmarine."MeasuredData"."ParameterID" IS 'A key field for relating this table to the Parameter table. It refers to Parameter.ParameterID.';
COMMENT ON COLUMN arcmarine."MeasuredData"."DeviceID" IS 'A key field for relating this table to the MeasuringDevice table. It refers to MeasuringDevice.DeviceID.';
COMMENT ON COLUMN arcmarine."MeasuredData"."DataValue" IS 'The recorded value';


-- Table: arcmarine."TSType"

-- DROP TABLE arcmarine."TSType";

CREATE TABLE arcmarine."TSType"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "TSTypeID" integer NOT NULL DEFAULT nextval('arcmarine.tstypeid_seq'), -- A unique identifier, database wide, for all TSType records
  "Variable" character varying(255), -- The type of data being described by the time series
  "Units" character varying(255), -- The units of measurement
  "IsRegular" integer, -- Whether the units have a regular or irregular time step. This is a foreign key to the AHBoolean table. Values are:...
  "TSInterval" integer, -- The interval between two time steps. This is a foreign key to the TSIntervalType table.
  "DataType" integer, -- The type of time series. This is a foreign key to the TSDataType table. Values are:...
  "Origin" integer, -- The origin of the time series. This is a foreign key to the TSOrigins table. Values are:...
  CONSTRAINT "TSType_pkey" PRIMARY KEY ("TSTypeID"),
  CONSTRAINT "TSTypeHasTSDataType_fkey" FOREIGN KEY ("DataType")
      REFERENCES arcmarine."TSDataType" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "TSTypeHasTSIntervalType_fkey" FOREIGN KEY ("TSInterval")
      REFERENCES arcmarine."TSIntervalType" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "TSTypeHasTSOrigins_fkey" FOREIGN KEY ("Origin")
      REFERENCES arcmarine."TSOrigins" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "TSTypeIsRegular_fkey" FOREIGN KEY ("IsRegular")
      REFERENCES arcmarine."AHBoolean" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TSType" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TSType" IS 'TSType is an index of the types of time series data stored in the TimeSeries objects.';
COMMENT ON COLUMN arcmarine."TSType"."TSTypeID" IS 'A unique identifier, database wide, for all TSType records';
COMMENT ON COLUMN arcmarine."TSType"."Variable" IS 'The type of data being described by the time series';
COMMENT ON COLUMN arcmarine."TSType"."Units" IS 'The units of measurement';
COMMENT ON COLUMN arcmarine."TSType"."IsRegular" IS 'Whether the units have a regular or irregular time step. This is a foreign key to the AHBoolean table. Values are:
1 = True
0 = False';
COMMENT ON COLUMN arcmarine."TSType"."TSInterval" IS 'The interval between two time steps. This is a foreign key to the TSIntervalType table.';
COMMENT ON COLUMN arcmarine."TSType"."DataType" IS 'The type of time series. This is a foreign key to the TSDataType table. Values are:
1 = Instantaneous
2 = Cumulative
3 = Incremental
4 = Average
5 = Maximum
6 = Minimum';
COMMENT ON COLUMN arcmarine."TSType"."Origin" IS 'The origin of the time series. This is a foreign key to the TSOrigins table. Values are:
1 = Recorded
2 = Generated';


-- Table: arcmarine."TimeSeries"

-- DROP TABLE arcmarine."TimeSeries";

CREATE TABLE arcmarine."TimeSeries"
(
  "FeatureID" integer, -- All features from any feature class have the potential to form a relationship with the TimeSeries class. This attribute is a key field for relating this time series to a feature class.
  "TSTypeID" integer, -- A key field for relating this time series to an entry in the TSType table
  "TSDateTime" date, -- The date and time stamp of a time series value
  "TSValue" double precision, -- The measured value for the time step
  CONSTRAINT "TSTypeHasTimeSeries_fkey" FOREIGN KEY ("TSTypeID")
      REFERENCES arcmarine."TSType" ("TSTypeID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TimeSeries" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TimeSeries" IS 'TimeSeries is an instantiable object class that stores the time-varying attributes of features.';
COMMENT ON COLUMN arcmarine."TimeSeries"."FeatureID" IS 'All features from any feature class have the potential to form a relationship with the TimeSeries class. This attribute is a key field for relating this time series to a feature class.';
COMMENT ON COLUMN arcmarine."TimeSeries"."TSTypeID" IS 'A key field for relating this time series to an entry in the TSType table';
COMMENT ON COLUMN arcmarine."TimeSeries"."TSDateTime" IS 'The date and time stamp of a time series value';
COMMENT ON COLUMN arcmarine."TimeSeries"."TSValue" IS 'The measured value for the time step';


-- Table: arcmarine."Feature"

-- DROP TABLE arcmarine."Feature";

CREATE TABLE arcmarine."Feature"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "Shape" geometry -- Geometry field
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Feature" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Feature" IS 'Features are geographic objects that have a spatial location defined. More specifically, a feature is just like an object but it also has a geometry or shape column in its relational database table. Through inheritance, a feature class has all of the attributes and behaviors of the object, but with additional, spatial capabilities.';
COMMENT ON COLUMN arcmarine."Feature"."Shape" IS 'Geometry field';


-- Table: arcmarine."MarineFeature"

-- DROP TABLE arcmarine."MarineFeature";

CREATE TABLE arcmarine."MarineFeature"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
  "FeatureID" integer NOT NULL DEFAULT nextval('arcmarine.featureid_seq'), -- A geodatabase-wide unique identifier and key field for participating in relationships
  "FeatureCode" character varying(255), -- A user-defined code used for identifying a MarineFeature
  CONSTRAINT "MarineFeature_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."Feature")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MarineFeature" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MarineFeature" IS 'Marine Features are feature classes for representing physical maritime features (i.e., spatial features that represent natural or manmade things in the water).';
COMMENT ON COLUMN arcmarine."MarineFeature"."FeatureID" IS 'A geodatabase-wide unique identifier and key field for participating in relationships';
COMMENT ON COLUMN arcmarine."MarineFeature"."FeatureCode" IS 'A user-defined code used for identifying a MarineFeature';


-- Table: arcmarine."MarinePoint"

-- DROP TABLE arcmarine."MarinePoint";

CREATE TABLE arcmarine."MarinePoint"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  CONSTRAINT "MarinePoint_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineFeature")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MarinePoint" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MarinePoint" IS 'MarinePoint is a generic abstract class for representing Marine Features and Measurement Points.';


-- Table: arcmarine."FeaturePoint"

-- DROP TABLE arcmarine."FeaturePoint";

CREATE TABLE arcmarine."FeaturePoint"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  CONSTRAINT "FeaturePoint_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarinePoint")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."FeaturePoint" OWNER TO postgres;
COMMENT ON TABLE arcmarine."FeaturePoint" IS 'A FeaturePoint could be a fixed object, such as a permanent monument, a structure, or a fixed buoy that does not require any specific measurement or attribute. The standard point feature common to standard GIs applications would sufficiently represent this feature as a fixed (x, y) location in space and would allow for attributes to be attached or related to this feature. The feature would be considered as not having any required temporal attributes, but in a marine environment a required z-elevation value would be necessary to relate the feature to a vertical datum.';


-- Table: arcmarine."MeasurementPoint"

-- DROP TABLE arcmarine."MeasurementPoint";

CREATE TABLE arcmarine."MeasurementPoint"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  "CruiseID" integer, -- A key field for relating this feature to the cruise table
  CONSTRAINT "MeasurementPoint_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarinePoint")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeasurementPoint" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeasurementPoint" IS 'MeasurementPoint is an abstract subclass of MarinePoint used for categorizing points that are associated with Time or Measurements.';
COMMENT ON COLUMN arcmarine."MeasurementPoint"."CruiseID" IS 'A key field for relating this feature to the cruise table';


-- Table: arcmarine."TimeSeriesPoint"

-- DROP TABLE arcmarine."TimeSeriesPoint";

CREATE TABLE arcmarine."TimeSeriesPoint"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
-- Inherited:   "CruiseID" integer,
  CONSTRAINT "TimeSeriesPoint_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MeasurementPoint")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TimeSeriesPoint" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TimeSeriesPoint" IS 'While Location Series provide the generic representation for moving points, the Time Series subtype provides a representation for features that stay in a fixed location but record attribute data over time. Again, numerous marine features readily fit this description. A weather buoy recording wave heights and wind speeds at a fixed location, a sea turtle nesting beach where observers record the number of hatchlings each season, and a gauging station in an estuary that records changes in salinity are all fixed geographic locations with attributes measured at different time intervals. So Time Series points can provide a common data type for a wide variety of common monitoring applications in coastal and marine environments. A TimeSeriesPoint can have multiple Z depths via implementing the Measurement object class. Examples: Observation buoy or hydrophone, Moored Current meter, OBS (Ocean Bottom Seismometer), Moored ADCP (Acoustic Doppler Current Profiler).';


-- Table: arcmarine."InstantaneousPoint"

-- DROP TABLE arcmarine."InstantaneousPoint";

CREATE TABLE arcmarine."InstantaneousPoint"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
-- Inherited:   "CruiseID" integer,
  "TimeValue" date, -- The time stamp for identifying the point
  "ZValue" double precision, -- The Z value for identifying the point
  "SurveyID" integer, -- A foreign key to the SurveyInfo object class.
  "SeriesID" integer, -- A key field for relating this feature to the Series table
  "PointType" integer DEFAULT 1, -- Defines the subtype to be one of the following:...
  CONSTRAINT "InstantaneousPoint_pkey" PRIMARY KEY ("FeatureID"),
  CONSTRAINT "InstantaneousPointHasSubtype_fkey" FOREIGN KEY ("PointType")
      REFERENCES arcmarine."InstantaneousPointSubtype" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "SurveyInfoHasPoints_fkey" FOREIGN KEY ("SurveyID")
      REFERENCES arcmarine."SurveyInfo" ("SurveyID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."MeasurementPoint")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."InstantaneousPoint" OWNER TO postgres;
COMMENT ON TABLE arcmarine."InstantaneousPoint" IS 'Often the time of an observation is critical to the representation of the data and further analysis for marine GIS applications. A conductivity-temperature-depth (CTD) cast from a vessel measures salinity, temperature, and depth for a particular moment in time at a particular depth. Similarly, an observer spotting a right whale notes the location as well as the time of observation. Our ability to analyze and relate marine observation data to other marine features is inherently tied to our ability to locate observation in time and space. So the development of a feature class subtype specifically designed to represent data tied to an instant in time is essential for a wide number of marine applications. The Instantaneous Point subtype provides a common feature class that requires a location (x, y, z) as well as a time (t) description in addition to any measurement (m1, ..., mn) attributes collected at that location in space and time. Each observation in this generic data type is independant. Examples: CTD (conductivity/temperature/depth), XBT (eXpendable BathyThermograph), SVP (Sound Velocity Profile) casts below the water surface, all with multiple Measurements. Instantaneous Point = CTD or measurement package Measurement Table – Measure ID of 1st CTD measure stop at depth z MDevice Table = bottle, or could be same device but calibrated different way MType = reading is of type dissolved O2 (could be salinity, temp, photosynthetically available radiation (PAR), etc.) MData = that actual numerical value of dissolved O2.';
COMMENT ON COLUMN arcmarine."InstantaneousPoint"."TimeValue" IS 'The time stamp for identifying the point';
COMMENT ON COLUMN arcmarine."InstantaneousPoint"."ZValue" IS 'The Z value for identifying the point';
COMMENT ON COLUMN arcmarine."InstantaneousPoint"."SurveyID" IS 'A foreign key to the SurveyInfo object class.';
COMMENT ON COLUMN arcmarine."InstantaneousPoint"."SeriesID" IS 'A key field for relating this feature to the Series table';
COMMENT ON COLUMN arcmarine."InstantaneousPoint"."PointType" IS 'Defines the subtype to be one of the following:
1 = Instant (default value)
2 = Sounding
3 = Survey
4 = LocationSeries';


-- Table: arcmarine."MarineLine"

-- DROP TABLE arcmarine."MarineLine";

CREATE TABLE arcmarine."MarineLine"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  CONSTRAINT "MarineLine_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineFeature")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MarineLine" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MarineLine" IS ' The MarineLine class is an abstract class for defining subclasses of linear features.';


-- Table: arcmarine."ProfileLine"

-- DROP TABLE arcmarine."ProfileLine";

CREATE TABLE arcmarine."ProfileLine"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  CONSTRAINT "ProfileLine_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineLine")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."ProfileLine" OWNER TO postgres;
COMMENT ON TABLE arcmarine."ProfileLine" IS 'The Profile Line subtype provides a common data type for the representation of attributes along a linear feature. Some common examples in marine GIS applications would be a bathymetric profile constructed from a bathymetric grid (with change measures along that profile), cross sections, seismic reflections, or transport lines.';


-- Table: arcmarine."FeatureLine"

-- DROP TABLE arcmarine."FeatureLine";

CREATE TABLE arcmarine."FeatureLine"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  CONSTRAINT "FeatureLine_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineLine")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."FeatureLine" OWNER TO postgres;
COMMENT ON TABLE arcmarine."FeatureLine" IS 'Many marine features are most appropriately represented by standard line features. An undersea cable, a juridictional boundary, or a shipping lane could all be readily represented with a standard Feature Line. Feature Lines require a unique identifier; a vector of x,y coordinate pairs; as well as free-form measurement attributes unique to the particular application.';


-- Table: arcmarine."Shoreline"

-- DROP TABLE arcmarine."Shoreline";

CREATE TABLE arcmarine."Shoreline"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  "VDatum" double precision, -- Defines the vertical datum for the Shoreline feature
  CONSTRAINT "Shoreline_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."FeatureLine")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Shoreline" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Shoreline" IS 'One category of Feature Line is so common and essential for coastal and marine applications that we provided a specialized subtype. Shoreline is a subtype of Feature Lines in that it demarcates essential boundaries of oceans and estuaries, but Shoreline definitions must also be explicitly stated (e.g. Mean Low Water versus Mean High Water) and must relate to a vertical datum to be properly represented. The specialized Shoreline subtype requires a shoreline type and a VDatum attribute to assure consistency in the representation and interpretation of shoreline features. Examples: Shoreline types can be according to datums (Mean High Water, Mean Low Water, etc.), or rocky, sandy, etc.';
COMMENT ON COLUMN arcmarine."Shoreline"."VDatum" IS 'Defines the vertical datum for the Shoreline feature';


-- Table: arcmarine."TimeDurationLine"

-- DROP TABLE arcmarine."TimeDurationLine";

CREATE TABLE arcmarine."TimeDurationLine"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  "StartDate" date, -- The beginning time stamp for the feature
  "EndDate" date, -- The ending time stamp for the feature
  "VehicleID" integer, -- A key field for relating a Vehicle with the TimeDurationLine
  "CruiseID" integer, -- A key field for relating a Cruise with the TimeDurationLine
  CONSTRAINT "TimeDurationLine_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineLine")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TimeDurationLine" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TimeDurationLine" IS 'Just as point observations in marine applications often need to be explicitly related to the time, lines also often need to be represented with starting and stopping time as well as starting and stopping location. We developed the Time duration Lines data type to provide marine GIS users with a common feature that requires a starting time, an ending time, and duration as core attributes. Types of common marine applications could be recording the sampling effort of a research vessel along legs of a survey, the duration of a trawl, and a segment of an autonomous vehicle track. With the required time attributes, each of these types of Time Duration Line features could be associated with other marine features based on the time of the observation along the line. This allows for a more direct method of associating dynamic environmental features (e.g. sea surface temperature - SST - or Beaufort Sea State) with the location and the appropriate period of time for the line.';
COMMENT ON COLUMN arcmarine."TimeDurationLine"."StartDate" IS 'The beginning time stamp for the feature';
COMMENT ON COLUMN arcmarine."TimeDurationLine"."EndDate" IS 'The ending time stamp for the feature';
COMMENT ON COLUMN arcmarine."TimeDurationLine"."VehicleID" IS 'A key field for relating a Vehicle with the TimeDurationLine';
COMMENT ON COLUMN arcmarine."TimeDurationLine"."CruiseID" IS 'A key field for relating a Cruise with the TimeDurationLine';


-- Table: arcmarine."Track"

-- DROP TABLE arcmarine."Track";

CREATE TABLE arcmarine."Track"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
-- Inherited:   "StartDate" date,
-- Inherited:   "EndDate" date,
-- Inherited:   "VehicleID" integer,
-- Inherited:   "CruiseID" integer,
  "TrackID" integer NOT NULL DEFAULT nextval('arcmarine.trackid_seq'), -- An identifier for a Track feature
  "Name" character varying(100), -- The name of a specific Track
  "Method" character varying(100), -- Text describing the method for a specific track
  "Description" character varying(100), -- Text describing the Track
  "LocalDesc" character varying(100), -- Text describing the locale of the Track
  CONSTRAINT "Track_pkey" PRIMARY KEY ("TrackID"),
  CONSTRAINT "CruiseHasTracks_fkey" FOREIGN KEY ("CruiseID")
      REFERENCES arcmarine."Cruise" ("CruiseID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."TimeDurationLine")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Track" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Track" IS 'Track is a linear feature class representinf the path and event associated with going on a specific tangent from the expedition. A Track can be further defined by the Cruise object class. Example: The track of a research vessel during an entire expedition, or the track of a vehicle during a run of observations or measurements. A track can be very complex depending on the kind of measurements made in multiple surveys, as opposed to a transect which is normally somewhat of a straight line and derived from one and only one survey.';
COMMENT ON COLUMN arcmarine."Track"."TrackID" IS 'An identifier for a Track feature';
COMMENT ON COLUMN arcmarine."Track"."Name" IS 'The name of a specific Track';
COMMENT ON COLUMN arcmarine."Track"."Method" IS 'Text describing the method for a specific track';
COMMENT ON COLUMN arcmarine."Track"."Description" IS 'Text describing the Track';
COMMENT ON COLUMN arcmarine."Track"."LocalDesc" IS 'Text describing the locale of the Track';


-- Table: arcmarine."SurveyKey"

-- DROP TABLE arcmarine."SurveyKey";

CREATE TABLE arcmarine."SurveyKey"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "SurveyID" integer NOT NULL, -- A foreign key to the SurveyInfo object class
  "FeatureID" integer NOT NULL, -- A foreign key to the ProfileLine feature class
  CONSTRAINT "SurveyKey_pkey" PRIMARY KEY ("SurveyID", "FeatureID"),
  CONSTRAINT "ProfileLineHasSurveyKeys_fkey" FOREIGN KEY ("FeatureID")
      REFERENCES arcmarine."ProfileLine" ("FeatureID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "SurveyHasSurveyKeys_fkey" FOREIGN KEY ("SurveyID")
      REFERENCES arcmarine."SurveyInfo" ("SurveyID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."SurveyKey" OWNER TO postgres;
COMMENT ON TABLE arcmarine."SurveyKey" IS 'A relationship table for relating a ProfileLine to a SurveyInfo';
COMMENT ON COLUMN arcmarine."SurveyKey"."SurveyID" IS 'A foreign key to the SurveyInfo object class';
COMMENT ON COLUMN arcmarine."SurveyKey"."FeatureID" IS 'A foreign key to the ProfileLine feature class';


-- Table: arcmarine."MarineArea"

-- DROP TABLE arcmarine."MarineArea";

CREATE TABLE arcmarine."MarineArea"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  CONSTRAINT "MarineArea_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineFeature")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MarineArea" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MarineArea" IS 'Area features in the marine environment are devided into two common types: Feature Areas represented as static, time-independant areas, and Time Duration Areas that require changing time attriutes for their representation.';


-- Table: arcmarine."FeatureArea"

-- DROP TABLE arcmarine."FeatureArea";

CREATE TABLE arcmarine."FeatureArea"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  CONSTRAINT "FeatureArea_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineArea")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."FeatureArea" OWNER TO postgres;
COMMENT ON TABLE arcmarine."FeatureArea" IS 'Any marine area that can be represented by a static polygon can be appropriately represented as a Feature Area. Three straightforward examples of Feature Areas are the permanent juridictional area of a marine sanctuary, benthic habitat features, or the area contained within the exclusive economic zone (EEZ) of a nation. Feature Areas require a unique identifier, a vector of x,y coordinates forming the boundary; a depth (z) attribute; as well as user-defined measurement (m) attributes.';


-- Table: arcmarine."TimeDurationArea"

-- DROP TABLE arcmarine."TimeDurationArea";

CREATE TABLE arcmarine."TimeDurationArea"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  "SeriesID" integer, -- A key field for relating this feature to the Series table
  "StartDate" date, -- The beginning time stamp for the feature
  "EndDate" date, -- The ending time stamp for the feature
  CONSTRAINT "TimeDurationArea_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineArea")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."TimeDurationArea" OWNER TO postgres;
COMMENT ON TABLE arcmarine."TimeDurationArea" IS 'Many features of the marine environment or management responses may be ephemeral. Shellfish habitat may be demarcated as exceeding pollution thresholds for a portion of the year; seasonal area management (SAMs) and dynamic area management (DAMs) fisheries closures may be invoked for particular seasons or after specific events; and oil and gas leases may persist for specific periods of time. These three examples represent a geographic area feature tied to a specific starting and stopping time. The team developed the Time Duration Area data type to allow for this appropriate representation of marine features that persist for specified periods of time. This type of time-dependent area feature allows for users to make queries concerning overlaps in space and time. The Time Duration Area feature requires a beginning and ending time attribute in addition to the standard variables required for general area features.';
COMMENT ON COLUMN arcmarine."TimeDurationArea"."SeriesID" IS 'A key field for relating this feature to the Series table';
COMMENT ON COLUMN arcmarine."TimeDurationArea"."StartDate" IS 'The beginning time stamp for the feature';
COMMENT ON COLUMN arcmarine."TimeDurationArea"."EndDate" IS 'The ending time stamp for the feature';


-- Table: arcmarine."MeshVolume"

-- DROP TABLE arcmarine."MeshVolume";

CREATE TABLE arcmarine."MeshVolume"
(
-- Inherited:   "OBJECTID" oid NOT NULL
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeshVolume" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeshVolume" IS 'Mesh volume';


-- Table: arcmarine."MeshElement"

-- DROP TABLE arcmarine."MeshElement";

CREATE TABLE arcmarine."MeshElement"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  "Node1ID" integer, -- Contains a Mesh Point that represents a corner of the MeshElement
  "Node2ID" integer, -- Contains a Mesh Point that represents a corner of the MeshElement
  "Node3ID" integer, -- Contains a Mesh Point that represents a corner of the MeshElement
  "Node4ID" integer, -- Contains a Mesh Point that represents a corner of the MeshElement
  CONSTRAINT "MeshElement_pkey" PRIMARY KEY ("FeatureID")
)
INHERITS (arcmarine."MarineFeature")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeshElement" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeshElement" IS 'MeshElements are polygonal features composed of a combination of either three or four nodes.';
COMMENT ON COLUMN arcmarine."MeshElement"."Node1ID" IS 'Contains a Mesh Point that represents a corner of the MeshElement';
COMMENT ON COLUMN arcmarine."MeshElement"."Node2ID" IS 'Contains a Mesh Point that represents a corner of the MeshElement';
COMMENT ON COLUMN arcmarine."MeshElement"."Node3ID" IS 'Contains a Mesh Point that represents a corner of the MeshElement';
COMMENT ON COLUMN arcmarine."MeshElement"."Node4ID" IS 'Contains a Mesh Point that represents a corner of the MeshElement';


-- Table: arcmarine."Mesh"

-- DROP TABLE arcmarine."Mesh";

CREATE TABLE arcmarine."Mesh"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "MeshID" integer NOT NULL DEFAULT nextval('arcmarine.meshid_seq'), -- The unique identifier for a Mesh
  "TotalPoints" integer, -- Total number of points participating in the Mesh
  "NoOfPointsI" integer, -- Total number of points available in the I direction within a Mesh
  "NoOfPointsJ" integer, -- Total number of points available in the J direction within a Mesh
  "NoOfPointsK" integer, -- Total number of points available in the K direction within a Mesh
  "Dimension" integer DEFAULT 2, -- A coded value domain defining the subtype to be one of the following:...
  CONSTRAINT "Mesh_pkey" PRIMARY KEY ("MeshID"),
  CONSTRAINT "MeshHasMeshType_fkey" FOREIGN KEY ("Dimension")
      REFERENCES arcmarine."MeshType" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."Mesh" OWNER TO postgres;
COMMENT ON TABLE arcmarine."Mesh" IS 'A Mesh is a collection of points either a line, area, or volume features in nature.';
COMMENT ON COLUMN arcmarine."Mesh"."MeshID" IS 'The unique identifier for a Mesh';
COMMENT ON COLUMN arcmarine."Mesh"."TotalPoints" IS 'Total number of points participating in the Mesh';
COMMENT ON COLUMN arcmarine."Mesh"."NoOfPointsI" IS 'Total number of points available in the I direction within a Mesh';
COMMENT ON COLUMN arcmarine."Mesh"."NoOfPointsJ" IS 'Total number of points available in the J direction within a Mesh';
COMMENT ON COLUMN arcmarine."Mesh"."NoOfPointsK" IS 'Total number of points available in the K direction within a Mesh';
COMMENT ON COLUMN arcmarine."Mesh"."Dimension" IS 'A coded value domain defining the subtype to be one of the following:
1 = linear
2 = Area
3 = Volume';


-- Table: arcmarine."MeshPoint"

-- DROP TABLE arcmarine."MeshPoint";

CREATE TABLE arcmarine."MeshPoint"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
-- Inherited:   "Shape" geometry,
-- Inherited:   "FeatureID" integer NOT NULL,
-- Inherited:   "FeatureCode" character varying(255),
  "IPosition" integer, -- A feature's location in the I direction for a given mesh
  "JPosition" integer, -- A feature's location in the J direction for a given mesh
  "KPosition" integer, -- A feature's location in the K direction for a given mesh
  "MeshID" integer, -- A foreign key to the Mesh table
  "PointType" integer DEFAULT 1, -- A coded value defining the subtype of the MeshType, to be one of the following: 1 = GridPoint, 2 = NodePoint. Default value is 1.
  CONSTRAINT "MeshPoint_pkey" PRIMARY KEY ("FeatureID"),
  CONSTRAINT "MeshHasPoints_fkey" FOREIGN KEY ("MeshID")
      REFERENCES arcmarine."Mesh" ("MeshID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "MeshPointHasMeshPointSubtype_fkey" FOREIGN KEY ("PointType")
      REFERENCES arcmarine."MeshPointSubtype" ("Code") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."MarineFeature")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."MeshPoint" OWNER TO postgres;
COMMENT ON TABLE arcmarine."MeshPoint" IS 'MeshPoint is a feature class representing point features from a 1D, 2D, or 3D numerical model as either an equality spaced mesh or irregularly spaced mesh, where the values of each point will change over time.';
COMMENT ON COLUMN arcmarine."MeshPoint"."IPosition" IS 'A feature''s location in the I direction for a given mesh';
COMMENT ON COLUMN arcmarine."MeshPoint"."JPosition" IS 'A feature''s location in the J direction for a given mesh';
COMMENT ON COLUMN arcmarine."MeshPoint"."KPosition" IS 'A feature''s location in the K direction for a given mesh';
COMMENT ON COLUMN arcmarine."MeshPoint"."MeshID" IS 'A foreign key to the Mesh table';
COMMENT ON COLUMN arcmarine."MeshPoint"."PointType" IS 'A coded value defining the subtype of the MeshType, to be one of the following: 1 = GridPoint, 2 = NodePoint. Default value is 1.';


-- Table: arcmarine."VectorQuantity"

-- DROP TABLE arcmarine."VectorQuantity";

CREATE TABLE arcmarine."VectorQuantity"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "FeatureID" integer, -- A foreign key to the MeshPoint feature class
  "ParameterID" integer, -- A foreign key to the Parameter table
  "XComponent" double precision, -- The vector X component value
  "YComponent" double precision, -- The vector Y component value
  "ZComponent" double precision, -- The vector Z component value
  "TimeValue" date, -- The time stamp assigned to the vector value
  CONSTRAINT "MeshPointHasVectors_fkey" FOREIGN KEY ("FeatureID")
      REFERENCES arcmarine."MeshPoint" ("FeatureID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "ParameterHasVectors_fkey" FOREIGN KEY ("ParameterID")
      REFERENCES arcmarine."Parameter" ("ParameterID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."VectorQuantity" OWNER TO postgres;
COMMENT ON TABLE arcmarine."VectorQuantity" IS 'VectorQuantity is an object class that stores the vector values associated with a MeshPoint.';
COMMENT ON COLUMN arcmarine."VectorQuantity"."FeatureID" IS 'A foreign key to the MeshPoint feature class';
COMMENT ON COLUMN arcmarine."VectorQuantity"."ParameterID" IS 'A foreign key to the Parameter table';
COMMENT ON COLUMN arcmarine."VectorQuantity"."XComponent" IS 'The vector X component value';
COMMENT ON COLUMN arcmarine."VectorQuantity"."YComponent" IS 'The vector Y component value';
COMMENT ON COLUMN arcmarine."VectorQuantity"."ZComponent" IS 'The vector Z component value';
COMMENT ON COLUMN arcmarine."VectorQuantity"."TimeValue" IS 'The time stamp assigned to the vector value';


-- Table: arcmarine."ScalarQuantity"

-- DROP TABLE arcmarine."ScalarQuantity";

CREATE TABLE arcmarine."ScalarQuantity"
(
-- Inherited:   "OBJECTID" oid NOT NULL,
  "FeatureID" integer, -- A foreign key to the ScalarQuantity table
  "ParameterID" integer, -- A foreign key to the Parameter table
  "DataValue" double precision, -- The recorded value
  "TimeValue" date, -- The time stamp assigned to the vector value
  CONSTRAINT "MeshPointHasScalars_fkey" FOREIGN KEY ("FeatureID")
      REFERENCES arcmarine."MeshPoint" ("FeatureID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "ParameterHasScalars_fkey" FOREIGN KEY ("ParameterID")
      REFERENCES arcmarine."Parameter" ("ParameterID") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
INHERITS (arcmarine."Object")
WITH (OIDS=FALSE);
ALTER TABLE arcmarine."ScalarQuantity" OWNER TO postgres;
COMMENT ON TABLE arcmarine."ScalarQuantity" IS 'ScalarQuantity is an object class that stores the scalar values associated with a MeshPoint.';
COMMENT ON COLUMN arcmarine."ScalarQuantity"."FeatureID" IS 'A foreign key to the ScalarQuantity table';
COMMENT ON COLUMN arcmarine."ScalarQuantity"."ParameterID" IS 'A foreign key to the Parameter table';
COMMENT ON COLUMN arcmarine."ScalarQuantity"."DataValue" IS 'The recorded value';
COMMENT ON COLUMN arcmarine."ScalarQuantity"."TimeValue" IS 'The time stamp assigned to the vector value';






---------------------------------------------------------------------------------------------------------------------------------------------------
-- Specify geometry fields

INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'Feature',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'GEOMETRY');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'MarineFeature',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'GEOMETRY');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'MarinePoint',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'POINT');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'FeaturePoint',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'POINT');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'MeasurementPoint',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'POINT');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'TimeSeriesPoint',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'POINT');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'InstantaneousPoint',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'POINT');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'MarineLine',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'LINESTRING');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'ProfileLine',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'LINESTRING');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'FeatureLine',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'LINESTRING');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'ShoreLine',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'LINESTRING');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'TimeDurationLine',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'LINESTRING');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'Track',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'LINESTRING');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'MarineArea',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'POLYGON');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'FeatureArea',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'POLYGON');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'TimeDurationArea',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'POLYGON');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'MeshElement',    				-- table name
	'Shape',               		-- spatial column name
	2,                   		-- Dimension
	getSRID(),                 -- srid
	'POINT');


INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
   VALUES (current_database(),        	 -- database name
	'arcmarine',              	-- schema name
	'MeshPoint',    				-- table name
	'Shape',               		-- spatial column name
	3,                   		-- Dimension
	getSRID(),                 -- srid
	'POINT');
	

---------------------------------------------------------------------------------------------------------------------------------------------------
-- Insert coded values

INSERT INTO arcmarine."TSIntervalUnitType"("Code", "Description") VALUES(1,'Second');
INSERT INTO arcmarine."TSIntervalUnitType"("Code", "Description") VALUES(2,'Minute');
INSERT INTO arcmarine."TSIntervalUnitType"("Code", "Description") VALUES(3,'Hour');
INSERT INTO arcmarine."TSIntervalUnitType"("Code", "Description") VALUES(4,'Day');
INSERT INTO arcmarine."TSIntervalUnitType"("Code", "Description") VALUES(5,'Week');
INSERT INTO arcmarine."TSIntervalUnitType"("Code", "Description") VALUES(6,'Month');
INSERT INTO arcmarine."TSIntervalUnitType"("Code", "Description") VALUES(7,'Year');



INSERT INTO arcmarine."ParameterType"("Code", "Description") VALUES(1,'Other');
INSERT INTO arcmarine."ParameterType"("Code", "Description") VALUES(2,'Scalar');
INSERT INTO arcmarine."ParameterType"("Code", "Description") VALUES(3,'Vector');



INSERT INTO arcmarine."MeshType"("Code", "Description") VALUES(1,'Linear');
INSERT INTO arcmarine."MeshType"("Code", "Description") VALUES(2,'Area');
INSERT INTO arcmarine."MeshType"("Code", "Description") VALUES(3,'Volume');



INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(1,'1Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(2,'2Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(3,'3Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(4,'4Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(5,'5Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(6,'10Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(7,'15Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(8,'20Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(9,'30Minute');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(10,'1Hour');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(11,'2Hour');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(12,'3Hour');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(13,'4Hour');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(14,'6Hour');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(15,'8Hour');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(16,'12Hour');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(17,'1Day');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(18,'1Week');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(19,'1Month');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(20,'1Year');
INSERT INTO arcmarine."TSIntervalType"("Code", "Description") VALUES(99,'Other');



INSERT INTO arcmarine."TSOrigins"("Code", "Description") VALUES(1,'Recorded');
INSERT INTO arcmarine."TSOrigins"("Code", "Description") VALUES(2,'Generated');



INSERT INTO arcmarine."AHBoolean"("Code", "Description") VALUES(0,'False');
INSERT INTO arcmarine."AHBoolean"("Code", "Description") VALUES(1,'True');



INSERT INTO arcmarine."TSDataType"("Code", "Description") VALUES(1,'Instantaneous');
INSERT INTO arcmarine."TSDataType"("Code", "Description") VALUES(2,'Cumulative');
INSERT INTO arcmarine."TSDataType"("Code", "Description") VALUES(3,'Incremental');
INSERT INTO arcmarine."TSDataType"("Code", "Description") VALUES(4,'Average');
INSERT INTO arcmarine."TSDataType"("Code", "Description") VALUES(5,'Maximum');
INSERT INTO arcmarine."TSDataType"("Code", "Description") VALUES(6,'Minimum');



INSERT INTO arcmarine."InstantaneousPointSubtype"("Code", "Description") VALUES(1,'Instant');
INSERT INTO arcmarine."InstantaneousPointSubtype"("Code", "Description") VALUES(2,'Sounding');
INSERT INTO arcmarine."InstantaneousPointSubtype"("Code", "Description") VALUES(3,'SurveyPoint');
INSERT INTO arcmarine."InstantaneousPointSubtype"("Code", "Description") VALUES(4,'LocationSeries');



INSERT INTO arcmarine."MeshPointSubtype"("Code", "Description") VALUES(1,'GridPoint');
INSERT INTO arcmarine."MeshPointSubtype"("Code", "Description") VALUES(2,'NodePoint');





---------------------------------------------------------------------------------------------------------------------------------------------------
-- Views

-- View: arcmarine."Instant"

-- DROP VIEW arcmarine."Instant";

CREATE OR REPLACE VIEW arcmarine."Instant" AS 
 SELECT "InstantaneousPoint"."OBJECTID", "InstantaneousPoint"."FeatureID", "InstantaneousPoint"."FeatureCode", "InstantaneousPoint"."CruiseID", "InstantaneousPoint"."TimeValue", "InstantaneousPoint"."ZValue", "InstantaneousPoint"."SurveyID", "InstantaneousPoint"."SeriesID", "InstantaneousPoint"."PointType", "InstantaneousPoint"."Shape"
   FROM arcmarine."InstantaneousPoint"
  WHERE "InstantaneousPoint"."PointType" = 1;

ALTER TABLE arcmarine."Instant" OWNER TO postgres;
COMMENT ON VIEW arcmarine."Instant" IS 'Instant subtype of InstantaneousPoint (Pointtype = 1).
Often the time of an observation is critical to the representation of the data and further analysis for marine GIS applications. A conductivity-temperature-depth (CTD) cast from a vessel measures salinity, temperature, and depth for a particular moment in time at a particular depth. Similarly, an observer spotting a right whale notes the location as well as the time of observation. Our ability to analyze and relate marine observation data to other marine features is inherently tied to our ability to locate observation in time and space. So the development of a feature class subtype specifically designed to represent data tied to an instant in time is essential for a wide number of marine applications. The Instantaneous Point subtype provides a common feature class that requires a location (x, y, z) as well as a time (t) description in addition to any measurement (m1, ..., mn) attributes collected at that location in space and time. Each observation in this generic data type is independant. Examples: CTD (conductivity/temperature/depth), XBT (eXpendable BathyThermograph), SVP (Sound Velocity Profile) casts below the water surface, all with multiple Measurements. Instantaneous Point = CTD or measurement package Measurement Table – Measure ID of 1st CTD measure stop at depth z MDevice Table = bottle, or could be same device but calibrated different way MType = reading is of type dissolved O2 (could be salinity, temp, photosynthetically available radiation (PAR), etc.) MData = that actual numerical value of dissolved O2.';


-- View: arcmarine."Sounding"

-- DROP VIEW arcmarine."Sounding";

CREATE OR REPLACE VIEW arcmarine."Sounding" AS 
 SELECT "InstantaneousPoint"."OBJECTID", "InstantaneousPoint"."FeatureID", "InstantaneousPoint"."FeatureCode", "InstantaneousPoint"."CruiseID", "InstantaneousPoint"."TimeValue", "InstantaneousPoint"."ZValue", "InstantaneousPoint"."SurveyID", "InstantaneousPoint"."SeriesID", "InstantaneousPoint"."PointType", "InstantaneousPoint"."Shape"
   FROM arcmarine."InstantaneousPoint"
  WHERE "InstantaneousPoint"."PointType" = 2;

ALTER TABLE arcmarine."Sounding" OWNER TO postgres;
COMMENT ON VIEW arcmarine."Sounding" IS 'Sounding subtype of InstantaneousPoint (PointType = 2).
SoundingType is a type for the Sounding class. Soundings, the measurement or estimate of a depth value (z) at a location with no other attributes, is a very common Point Feature in marine applications and was assigned a seperate subtype to represent this simple feature.';


-- View: arcmarine."SurveyPoint"

-- DROP VIEW arcmarine."SurveyPoint";

CREATE OR REPLACE VIEW arcmarine."SurveyPoint" AS 
 SELECT "InstantaneousPoint"."OBJECTID", "InstantaneousPoint"."FeatureID", "InstantaneousPoint"."FeatureCode", "InstantaneousPoint"."CruiseID", "InstantaneousPoint"."TimeValue", "InstantaneousPoint"."ZValue", "InstantaneousPoint"."SurveyID", "InstantaneousPoint"."SeriesID", "InstantaneousPoint"."PointType", "InstantaneousPoint"."Shape"
   FROM arcmarine."InstantaneousPoint"
  WHERE "InstantaneousPoint"."PointType" = 3;

ALTER TABLE arcmarine."SurveyPoint" OWNER TO postgres;
COMMENT ON VIEW arcmarine."SurveyPoint" IS 'SurveyPoint subtype of InstantaneousPoint (PointType = 3).
A Survey Point measures a single defined measurement (e.g. lidar elevations) at a discrete geographic location with no other required attributes.';


-- View: arcmarine."LocationSeries"

-- DROP VIEW arcmarine."LocationSeries";

CREATE OR REPLACE VIEW arcmarine."LocationSeries" AS 
 SELECT "InstantaneousPoint"."OBJECTID", "InstantaneousPoint"."FeatureID", "InstantaneousPoint"."FeatureCode", "InstantaneousPoint"."CruiseID", "InstantaneousPoint"."TimeValue", "InstantaneousPoint"."ZValue", "InstantaneousPoint"."SurveyID", "InstantaneousPoint"."SeriesID", "InstantaneousPoint"."PointType", "InstantaneousPoint"."Shape"
   FROM arcmarine."InstantaneousPoint"
  WHERE "InstantaneousPoint"."PointType" = 4;

ALTER TABLE arcmarine."LocationSeries" OWNER TO postgres;
COMMENT ON VIEW arcmarine."LocationSeries" IS 'LocationSeries subtype of InstantaneousPoint (PointType = 4).
Another data type common to marine applications pertains to objects moving in the ocean environment. A vessel moving along a track, an autonomous vehicle conducting a dive, and a telemetry track of a satellite-tagged animal all represent multiple locations in space and time for a single entity. The Location Series subtype represents a series of point locations for an identified feature.';


-- View: arcmarine."GridPoint"

-- DROP VIEW arcmarine."GridPoint";

CREATE OR REPLACE VIEW arcmarine."GridPoint" AS 
 SELECT "MeshPoint"."OBJECTID", "MeshPoint"."FeatureID", "MeshPoint"."FeatureCode", "MeshPoint"."IPosition", "MeshPoint"."JPosition", "MeshPoint"."KPosition", "MeshPoint"."MeshID", "MeshPoint"."PointType", "MeshPoint"."Shape"
   FROM arcmarine."MeshPoint"
  WHERE "MeshPoint"."PointType" = 1;

ALTER TABLE arcmarine."GridPoint" OWNER TO postgres;
COMMENT ON VIEW arcmarine."GridPoint" IS 'GridPoint subtype of MeshPoint (PointType = 1).';


-- View: arcmarine."NodePoint"

-- DROP VIEW arcmarine."NodePoint";

CREATE OR REPLACE VIEW arcmarine."NodePoint" AS 
 SELECT "MeshPoint"."OBJECTID", "MeshPoint"."FeatureID", "MeshPoint"."FeatureCode", "MeshPoint"."IPosition", "MeshPoint"."JPosition", "MeshPoint"."KPosition", "MeshPoint"."MeshID", "MeshPoint"."PointType", "MeshPoint"."Shape"
   FROM arcmarine."MeshPoint"
  WHERE "MeshPoint"."PointType" = 2;

ALTER TABLE arcmarine."NodePoint" OWNER TO postgres;
COMMENT ON VIEW arcmarine."NodePoint" IS 'NodePoint subtype of MeshPoint (PointType = 2)';