root/db/trunk/schema/sst_prod.sql

--
-- PostgreSQL database dump
--

SET SESSION AUTHORIZATION 'postgres';

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 32024)
-- Name: sst_prod; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE sst_prod (
    station_id character varying NOT NULL,
    time_stamp timestamp without time zone NOT NULL,
    z double precision,
    temperature_celcius double precision,
    lon double precision,
    lat double precision,
    title character varying,
    institution character varying,
    institution_url character varying,
    institution_dods_url character varying,
    source character varying,
    refs character varying,
    contact character varying,
    report_time_stamp timestamp without time zone,
    top_of_hour integer,
    secs_from_report_time_stamp integer,
    secs_from_time_stamp integer,
    the_geom geometry NOT NULL,
    positive character varying,
    label_z double precision,
    temperature_fahrenheit double precision,
    value_temperature_celcius character varying,
    value_temperature_fahrenheit character varying,    
    qcflag_data_availability character(1),
    qcflag_sensor_range character(1),
    qcflag_gross_range character(1),
    qcflag_climatological_range character(1),
    qcflag_rate_of_change character(1),
    qcflag_aggregate character(1),          
    seq integer DEFAULT nextval('sst_prod_seq'::text),
    CONSTRAINT "$1" CHECK ((srid(the_geom) = -1)),
    CONSTRAINT "$2" CHECK (((geometrytype(the_geom) = 'POINT'::text) OR (the_geom IS NULL))),
    CONSTRAINT positive_up_down CHECK (((((positive)::text = ('up'::character varying)::text) OR ((positive)::text = ('down'::character varying)::text)) OR ((positive)::text = (''::character varying)::text)))
);


--
-- TOC entry 3 (OID 32024)
-- Name: sst_prod; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE sst_prod FROM PUBLIC;
GRANT SELECT ON TABLE sst_prod TO GROUP readonly;


--
-- TOC entry 5 (OID 400102)
-- Name: sst_prod__gist; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod__gist ON sst_prod USING gist (the_geom);


--
-- TOC entry 7 (OID 400103)
-- Name: sst_prod__oid; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod__oid ON sst_prod USING btree (oid);


--
-- TOC entry 6 (OID 400104)
-- Name: sst_prod__id_z_rep_time; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod__id_z_rep_time ON sst_prod USING btree (station_id, z, report_time_stamp);


--
-- TOC entry 8 (OID 400105)
-- Name: sst_prod__report_time_stamp; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod__report_time_stamp ON sst_prod USING btree (report_time_stamp);


--
-- TOC entry 10 (OID 400107)
-- Name: sst_prod__station_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod__station_id ON sst_prod USING btree (station_id);


--
-- TOC entry 11 (OID 400108)
-- Name: sst_prod__time_stamp; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod__time_stamp ON sst_prod USING btree (time_stamp);

ALTER TABLE sst_prod CLUSTER ON sst_prod__time_stamp;


--
-- TOC entry 9 (OID 2235963646)
-- Name: sst_prod__seq; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod__seq ON sst_prod USING btree (seq);


--
-- TOC entry 12 (OID 2282858229)
-- Name: sst_prod_top_of_hour; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX sst_prod_top_of_hour ON sst_prod USING btree (top_of_hour);


--
-- TOC entry 4 (OID 32032)
-- Name: pk_id_time_stamp; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sst_prod
    ADD CONSTRAINT pk_id_time_stamp PRIMARY KEY (station_id, time_stamp);


--
-- TOC entry 13 (OID 1208244017)
-- Name: sst_labels_and_times_trig; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER sst_labels_and_times_trig
    BEFORE INSERT OR UPDATE ON sst_prod
    FOR EACH ROW
    EXECUTE PROCEDURE sst_labels_and_times();