SCPN Aquatic Macroinvertebrate Data Package For 2007-2024

CREATE TABLE view_eventquantitativeparticle_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "samplenumber" BIGINT,
  "particlenumber" BIGINT,
  "particleembeddedness_pct" BIGINT,
  "particlesizeclass" VARCHAR,
  "particlesize_mm" BIGINT,
  "quantitativeparticlenote" VARCHAR,
  "quantitativeparticlesopversion" VARCHAR,
  "quantitativeparticlevalidation" VARCHAR
);

CREATE TABLE view_eventhabitatreachgcu_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "gcucollected" BIGINT,
  "geomorphicchannelunit" VARCHAR,
  "geomorphicchannelunitdescription" VARCHAR,
  "unitlength_m" DOUBLE,
  "gcunote" VARCHAR,
  "gcusopversion" VARCHAR,
  "gcuvalidation" VARCHAR
);

CREATE TABLE view_eventhabitattransect_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "habitattransectcollected" BIGINT,
  "transectnumber" BIGINT,
  "transectcollector" VARCHAR,
  "transectrecorder" VARCHAR,
  "pointscollected" BIGINT,
  "canopyclosurecollected" BIGINT,
  "woodydebriscollected" BIGINT,
  "woodydebrispresent" BIGINT,
  "dominantgeomorphicchannelunit" VARCHAR,
  "wettedchannelwidth_m" DOUBLE,
  "activechannelwidth_m" DOUBLE,
  "transectnote" VARCHAR,
  "transectsopversion" VARCHAR,
  "transectvalidation" VARCHAR,
  "habitattransectid" VARCHAR
);

CREATE TABLE view_eventquantitativeitis_all (
  "park" VARCHAR,
  "feature" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "samplenumber" BIGINT,
  "quantitativemacroinvertscollected" BIGINT,
  "quantitativemacroinvertspresent" BIGINT,
  "quantitativeareasampled_m2" DOUBLE,
  "quantitativefieldsplit_pct" BIGINT,
  "quantitativelabsplit_pct" DOUBLE,
  "sampleid" BIGINT,
  "phylum" VARCHAR,
  "class" VARCHAR,
  "order" VARCHAR,
  "suborder" VARCHAR,
  "family" VARCHAR,
  "subfamily" VARCHAR,
  "tribe" VARCHAR,
  "genus" VARCHAR,
  "species" VARCHAR,
  "itisbuid" VARCHAR,
  "lifestage" VARCHAR,
  "labnote" VARCHAR,
  "labcount_cnt" BIGINT,
  "bigrarecount_cnt" BIGINT,
  "taxonomicrank" VARCHAR,
  "quantitativeitisnote" VARCHAR,
  "quantitativeitissopversion" VARCHAR,
  "quantitativeitisvalidation" VARCHAR,
  "abundance_cnt_m2" DOUBLE,
  "functionalreferenceid" BIGINT,
  "collectorgatherer" BIGINT,
  "filteringcollector" BIGINT,
  "omnivore" BIGINT,
  "parasite" BIGINT,
  "piercer" BIGINT,
  "predator" BIGINT,
  "scraper" BIGINT,
  "shredder" BIGINT,
  "unspecified" BIGINT,
  "groupmembershipcount" BIGINT,
  "functionalgroupnote" VARCHAR,
  "tolerancereferenceid" BIGINT,
  "minimumtolerance" DOUBLE,
  "maximumtolerance" DOUBLE,
  "tolerance" DOUBLE,
  "classifiedtolerance" VARCHAR,
  "tolerancenote" VARCHAR,
  "primaryhabit" VARCHAR,
  "secondaryhabit" VARCHAR,
  "tertiaryhabit" VARCHAR
);

CREATE TABLE view_eventhabitatreach_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "habitatreachcollected" BIGINT,
  "gcucollected" BIGINT,
  "gcustartpoint" VARCHAR,
  "gcucollector" VARCHAR,
  "gcurecorder" VARCHAR,
  "particlecollected" BIGINT,
  "particlestartpoint" VARCHAR,
  "particlecollector" VARCHAR,
  "particlerecorder" VARCHAR,
  "vegetationcollected" BIGINT,
  "vegetationcollector" VARCHAR,
  "habitatreachnote" VARCHAR,
  "habitatreachsopversion" VARCHAR,
  "habitatreachvalidation" VARCHAR,
  "habitatreachid" VARCHAR
);

CREATE TABLE view_eventhabitattransectwoodydebris_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "transectnumber" BIGINT,
  "woodydebriscollected" BIGINT,
  "woodydebrispresent" BIGINT,
  "debrislength_cm" DOUBLE,
  "debrisdiameter_cm" DOUBLE,
  "debrisnote" VARCHAR,
  "debrissopversion" VARCHAR,
  "debrisvalidation" VARCHAR
);

CREATE TABLE view_eventqualitativeitis_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "qualitativemacroinvertscollected" BIGINT,
  "qualitativemacroinvertspresent" BIGINT,
  "sampleid" BIGINT,
  "phylum" VARCHAR,
  "class" VARCHAR,
  "order" VARCHAR,
  "suborder" VARCHAR,
  "family" VARCHAR,
  "subfamily" VARCHAR,
  "tribe" VARCHAR,
  "genus" VARCHAR,
  "species" VARCHAR,
  "itisbuid" VARCHAR,
  "itistsn" BIGINT,
  "lifestage" VARCHAR,
  "labcount_cnt" BIGINT,
  "bigrarecount_cnt" BIGINT,
  "qualitativefieldsplit_pct" BIGINT,
  "qualitativelabsplit_pct" DOUBLE,
  "abundance_cnt" DOUBLE,
  "taxonomicrank" VARCHAR,
  "primaryfunctionalgroup" VARCHAR,
  "secondaryfunctionalgroup" VARCHAR,
  "classifiedtolerance" VARCHAR,
  "primaryhabit" VARCHAR,
  "secondaryhabit" VARCHAR,
  "tertiaryhabit" VARCHAR
);

CREATE TABLE view_eventqualitative_all (
  "park" VARCHAR,
  "feature" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "sampleid" BIGINT,
  "qualitativehabitatcollected" BIGINT,
  "qualitativemacroinvertscollected" BIGINT,
  "qualitativemacroinvertspresent" BIGINT,
  "qualitativefieldsplit_pct" BIGINT,
  "qualitativelabsplit_pct" DOUBLE,
  "qualitativenote" VARCHAR,
  "qualitativesopversion" VARCHAR,
  "qualitativevalidation" VARCHAR,
  "qualitativeid" VARCHAR
);

CREATE TABLE view_eventhabitatreachparticle_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "particlecollected" BIGINT,
  "particleembedded" BIGINT,
  "newparticlesizeclass" VARCHAR,
  "originalparticlesizeclass" VARCHAR,
  "particlesize_mm" DOUBLE,
  "simplifiedparticlesizeclass" VARCHAR,
  "particlenote" VARCHAR,
  "particlesopversion" VARCHAR,
  "particlevalidation" VARCHAR
);

CREATE TABLE view_eventquantitative_all (
  "park" VARCHAR,
  "feature" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "samplenumber" BIGINT,
  "sampleid" BIGINT,
  "quantitativewaterdepth_m" DOUBLE,
  "quantitativewatervelocity_mpersec" DOUBLE,
  "quantitativeparticlecollected" BIGINT,
  "quantitativemacroinvertscollected" BIGINT,
  "quantitativemacroinvertspresent" BIGINT,
  "quantitativeareasampled_m2" DOUBLE,
  "quantitativefieldsplit_pct" BIGINT,
  "quantitativelabsplit_pct" DOUBLE,
  "quantitativenote" VARCHAR,
  "quantitativesopversion" VARCHAR,
  "quantitativevalidation" VARCHAR,
  "quantitativeid" VARCHAR
);

CREATE TABLE view_event_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "disturbancecollected" BIGINT,
  "habitatreachcollected" BIGINT,
  "habitattransectcollected" BIGINT,
  "quantitativesamplescollected" BIGINT,
  "qualitativesamplecollected" BIGINT,
  "waterqualitycollected" BIGINT,
  "eventnote" VARCHAR,
  "eventvalidation" VARCHAR,
  "eventid" VARCHAR
);

CREATE TABLE view_eventhabitattransectcanopyclosure_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "transectnumber" BIGINT,
  "canopyclosurecollected" BIGINT,
  "centeronlycollected" BIGINT,
  "modifiedrightbank_cnt" BIGINT,
  "modifiedcenter_cnt" BIGINT,
  "modifiedleftbank_cnt" BIGINT,
  "rightbanksamples_cnt" BIGINT,
  "centersamples_cnt" BIGINT,
  "leftbanksamples_cnt" BIGINT,
  "modifiedrightbank_temp" DOUBLE,
  "modifiedcenter_temp" DOUBLE,
  "modifiedleftbank_temp" DOUBLE,
  "modifiedrightbankcanopyclosure_pct" DOUBLE,
  "modifiedcentercanopyclosure_pct" DOUBLE,
  "modifiedleftbankcanopyclosure_pct" DOUBLE,
  "canopyclosurenote" VARCHAR,
  "canopyclosuresopversion" VARCHAR,
  "canopyclosurevalidation" VARCHAR
);

CREATE TABLE view_eventhabitattransectpoint_all (
  "park" VARCHAR,
  "site" VARCHAR,
  "eventdate" TIMESTAMP,
  "eventyear" BIGINT,
  "transectnumber" BIGINT,
  "pointscollected" BIGINT,
  "point" VARCHAR,
  "positionalongtransect_m" DOUBLE,
  "dry" BIGINT,
  "obstruction" BIGINT,
  "waterdepth_m" DOUBLE,
  "watervelocity_mpersec" DOUBLE,
  "geomorphicchannelunit" VARCHAR,
  "habitatstructurecollected" BIGINT,
  "structure_algalmat" BIGINT,
  "structure_leafpack" BIGINT,
  "structure_manmadestructure" BIGINT,
  "structure_vegetation" BIGINT,
  "structure_woodydebris" BIGINT,
  "structure_rootwad" BIGINT,
  "structure_rock" BIGINT,
  "structure_none" BIGINT,
  "pointnote" VARCHAR,
  "pointsopversion" VARCHAR,
  "pointvalidation" VARCHAR,
  "habitattransectpointid" VARCHAR
);