-- Table: butterfly.brown_hairstreak_records

-- DROP TABLE butterfly.brown_hairstreak_records;

CREATE TABLE butterfly.brown_hairstreak_records
(
  id character(16) NOT NULL,
  taxon_group character(20),
  event_date character(32),
  sample_year integer,
  sample_month integer,
  sample_type character(20),
  grid_ref character(12),
  sample_location character varying,
  location_name character varying,
  common_name character varying(40),
  scientific_name character varying(40),
  checklist character varying,
  adult integer,
  male integer,
  female integer,
  copulating_pair integer,
  ovum integer,
  larvave integer,
  pupae integer,
  signs integer,
  associated_name character varying(40),
  assoication_type character varying(30),
  association_comment character varying,
  sample_comment character varying,
  obs_comment character varying,
  recorders character varying,
  determiner character varying,
  survey_name character varying,
  hectad character(4),
  tetrad character(5),
  monad character(6),
  x real,
  y real,
  x_hectad integer,
  y_hectad integer,
  x_tetrad integer,
  y_tetrad integer,
  x_monad integer,
  y_monad integer,
  the_geom geometry,
  CONSTRAINT bh_id_fk PRIMARY KEY (id),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE butterfly.brown_hairstreak_records
  OWNER TO postgres;


-- These commands are used to create set the_geom
UPDATE butterfly.brown_hairstreak_records
SET the_geom = ST_SetSRID(ST_Point(x_monad,y_monad),27700);


-- Table recorder6_data is similar table to above, but without any geometry
-- Table hectad_records is just a simple table containing "the_geom"
-- View: butterfly.hectad_view

-- DROP VIEW butterfly.hectad_view;

CREATE OR REPLACE VIEW butterfly.hectad_view AS 
 SELECT recorder6_data.id,
    recorder6_data.sample_key,
    recorder6_data.location_name,
    recorder6_data.sample_year,
    recorder6_data.sample_month,
    recorder6_data.sample_day,
    recorder6_data.taxon_group,
    recorder6_data.taxon_name,
    recorder6_data.adults,
    recorder6_data.females,
    recorder6_data.males,
    recorder6_data.copulating_pair,
    recorder6_data.ovum,
    recorder6_data.larvae,
    recorder6_data.pupae,
    recorder6_data.survey_name,
    recorder6_data.grid_ref,
    recorder6_data.hectad,
    hectad_records.the_geom
   FROM butterfly.recorder6_data,
    butterfly.hectad_records,
    vc17
  WHERE recorder6_data.id = hectad_records.records_id AND recorder6_data.taxon_group::text = 'insect - butterfly'::text;

ALTER TABLE butterfly.hectad_view
  OWNER TO postgres;

-- Table: butterfly.hectad_records

-- DROP TABLE butterfly.hectad_records;

CREATE TABLE butterfly.hectad_records
(
  records_id integer,
  the_geom geometry,
  CONSTRAINT records_id_fk FOREIGN KEY (records_id)
      REFERENCES butterfly.recorder6_data (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE butterfly.hectad_records
  OWNER TO postgres;

-- Table: butterfly.recorder6_data

-- DROP TABLE butterfly.recorder6_data;

CREATE TABLE butterfly.recorder6_data
(
  id integer NOT NULL,
  sample_key character(30) NOT NULL,
  location_name character varying,
  sample_date character(32),
  sample_year integer,
  sample_month integer,
  sample_day integer,
  taxon_group character varying(35),
  taxon_name character varying(40),
  adults integer,
  females integer,
  males integer,
  copulating_pair integer,
  ovum integer,
  larvae integer,
  pupae integer,
  survey_name character varying,
  grid_ref character(12),
  hectad character(4),
  tetrad character(5),
  monad character(6),
  grlen real,
  gr_x integer,
  gr_y integer,
  x real,
  y real,
  x_hectad integer,
  y_hectad integer,
  x_tetrad integer,
  y_tetrad integer,
  x_monad integer,
  y_monad integer,
  CONSTRAINT pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE butterfly.recorder6_data
  OWNER TO postgres;

-- Index: butterfly.day_key

-- DROP INDEX butterfly.day_key;

CREATE INDEX day_key
  ON butterfly.recorder6_data
  USING btree
  (sample_day);

-- Index: butterfly.gridref_key

-- DROP INDEX butterfly.gridref_key;

CREATE INDEX gridref_key
  ON butterfly.recorder6_data
  USING btree
  (grid_ref COLLATE pg_catalog."default");

-- Index: butterfly.hectad_key

-- DROP INDEX butterfly.hectad_key;

CREATE INDEX hectad_key
  ON butterfly.recorder6_data
  USING btree
  (hectad COLLATE pg_catalog."default");

-- Index: butterfly.monad_key

-- DROP INDEX butterfly.monad_key;

CREATE INDEX monad_key
  ON butterfly.recorder6_data
  USING btree
  (monad COLLATE pg_catalog."default");

-- Index: butterfly.month_key

-- DROP INDEX butterfly.month_key;

CREATE INDEX month_key
  ON butterfly.recorder6_data
  USING btree
  (sample_month);

-- Index: butterfly.name_key

-- DROP INDEX butterfly.name_key;

CREATE INDEX name_key
  ON butterfly.recorder6_data
  USING btree
  (taxon_name COLLATE pg_catalog."default");

-- Index: butterfly.tetrad_key

-- DROP INDEX butterfly.tetrad_key;

CREATE INDEX tetrad_key
  ON butterfly.recorder6_data
  USING btree
  (tetrad COLLATE pg_catalog."default");

-- Index: butterfly.year_key

-- DROP INDEX butterfly.year_key;

CREATE INDEX year_key
  ON butterfly.recorder6_data
  USING btree
  (sample_year);