San Francisco Bay Area Network Salmonids Monitoring Datasets

CREATE TABLE nps_imd_sfan_salmonids_antennadataset (
  "id" BIGINT,
  "antennadataid" BIGINT,
  "detectiondate" TIMESTAMP,
  "detectiontime" VARCHAR,
  "detectionduration" VARCHAR,
  "locationid" VARCHAR,
  "locationcode" VARCHAR,
  "locationname" VARCHAR,
  "tagtype" VARCHAR,
  "pittag" VARCHAR,
  "antennanumber" VARCHAR,
  "numberofdetections" BIGINT,
  "numberofemptyscans" DOUBLE,
  "origfromfilename" VARCHAR,
  "qcflag" VARCHAR,
  "qcnotes" VARCHAR,
  "dataprocessinglevel" VARCHAR,
  "dataprocessinglevelnotes" VARCHAR
);

CREATE TABLE nps_imd_sfan_salmonids_cohosmoltestimateanalysisdataset (
  "watershed" VARCHAR,
  "year" BIGINT,
  "totalcohosmoltcount" DOUBLE,
  "estimate" DOUBLE,
  "se" DOUBLE,
  "ci_95" DOUBLE,
  "low_ci" DOUBLE,
  "high_ci" DOUBLE,
  "analysismodel" VARCHAR,
  "qcflags" VARCHAR,
  "qcflagnotes" VARCHAR,
  "protocolversion" BIGINT
);

CREATE TABLE nps_imd_sfan_salmonids_eventsdataset (
  "id" BIGINT,
  "eventid" BIGINT,
  "streamid" BIGINT,
  "streamname" VARCHAR,
  "streamcode" VARCHAR,
  "reachnum" DOUBLE,
  "projectcode" VARCHAR,
  "surveytype" VARCHAR,
  "projectdescription" VARCHAR,
  "fieldseason" VARCHAR,
  "startdate" TIMESTAMP,
  "starttime" VARCHAR,
  "endtime" VARCHAR,
  "observers" VARCHAR,
  "dataentrydate" TIMESTAMP,
  "gpsid" DOUBLE,
  "verified" VARCHAR,
  "createddatetime" TIMESTAMP,
  "createdby" VARCHAR,
  "verifieddate" TIMESTAMP,
  "verifiedby" VARCHAR,
  "updateddatetime" TIMESTAMP,
  "updatedby" VARCHAR,
  "qcflag" VARCHAR,
  "qcnotes" VARCHAR,
  "dataprocessinglevel" VARCHAR,
  "dataprocessingleveldatetime" TIMESTAMP,
  "dataprocessingleveluser" VARCHAR
);

CREATE TABLE nps_imd_sfan_salmonids_indexhabitatdataset (
  "id" BIGINT,
  "indexhabitatid" BIGINT,
  "eventid" BIGINT,
  "watershed" VARCHAR,
  "watershed2letter" VARCHAR,
  "streamname" VARCHAR,
  "streamcode" VARCHAR,
  "startdate" TIMESTAMP,
  "dataprocessinglevel" VARCHAR,
  "indexhabitatqcflag" VARCHAR,
  "indexhabitatqcnotes" VARCHAR,
  "reachname" VARCHAR,
  "indexreach" VARCHAR,
  "reachdescription" VARCHAR,
  "indexreachnotes" VARCHAR,
  "observers" VARCHAR,
  "recorder" VARCHAR,
  "os_perc" BIGINT,
  "us_perc" BIGINT,
  "fg_perc" BIGINT,
  "rootwad" BIGINT,
  "lwdjam" BIGINT,
  "swdjam" BIGINT,
  "wood10_20cm" BIGINT,
  "wood20_50cm" BIGINT,
  "wood_gt50cm" BIGINT,
  "drybed" DOUBLE,
  "isolatedpools" VARCHAR,
  "isolatedpoolscomments" VARCHAR,
  "leftbank" BIGINT,
  "rightbank" BIGINT,
  "bankcomments" VARCHAR,
  "createddatetime" TIMESTAMP,
  "indexhabitatreachid" VARCHAR,
  "indexhabitatreachqcflag" VARCHAR,
  "indexhabitatreachqcflagnotes" VARCHAR,
  "stream_km" DOUBLE,
  "latitude_down" DOUBLE,
  "longitude_down" DOUBLE,
  "latitude_up" DOUBLE,
  "longitude_up" DOUBLE
);

CREATE TABLE nps_imd_sfan_salmonids_spawnfishmeasurementsdataset (
  "id" BIGINT,
  "spawnfishmeasurementid" BIGINT,
  "eventid" BIGINT,
  "startdate" TIMESTAMP,
  "spawnerseason" VARCHAR,
  "watershed" VARCHAR,
  "watershed2letter" VARCHAR,
  "streamname" VARCHAR,
  "streamcode" VARCHAR,
  "streamcode2letter" VARCHAR,
  "hwy1milepost" DOUBLE,
  "locationid" DOUBLE,
  "locationcode" VARCHAR,
  "locationname" VARCHAR,
  "sectionnum" DOUBLE,
  "reachnum" DOUBLE,
  "unitcode" VARCHAR,
  "latitude" DOUBLE,
  "longitude" DOUBLE,
  "locationtype" VARCHAR,
  "calendarday" BIGINT,
  "weather" VARCHAR,
  "observers" VARCHAR,
  "recorder" VARCHAR,
  "waterclarity_cm" VARCHAR,
  "stage_ft" VARCHAR,
  "spawnersurveycomments" VARCHAR,
  "spawnersurveyscreateddatetime" TIMESTAMP,
  "dataprocessinglevel" VARCHAR,
  "spawnfishmeasurementsqcflag" VARCHAR,
  "spawnfishmeasurementsqcnotes" VARCHAR,
  "upstreamdistance_m" DOUBLE,
  "speciescode" VARCHAR,
  "commonname" VARCHAR,
  "scientificname" VARCHAR,
  "fishcondition" VARCHAR,
  "sex" VARCHAR,
  "gtorlt" VARCHAR,
  "length_cm" DOUBLE,
  "weight_g" VARCHAR,
  "carcassmarkcode" DOUBLE,
  "carcassrecapcode" VARCHAR,
  "tissue" VARCHAR,
  "scales" VARCHAR,
  "pittag" DOUBLE,
  "otoliths" VARCHAR,
  "waypoint" DOUBLE,
  "envelopenum" DOUBLE,
  "envelopeid" VARCHAR,
  "spawnfishmeasurementscomments" VARCHAR,
  "spawnfishmeasurementscreateddatetime" TIMESTAMP
);

CREATE TABLE nps_imd_sfan_salmonids_spawnreddmeasurementsdataset (
  "id" BIGINT,
  "spawnreddmeasurementid" BIGINT,
  "eventid" BIGINT,
  "spawnerseason" VARCHAR,
  "watershed" VARCHAR,
  "watershed2letter" VARCHAR,
  "streamname" VARCHAR,
  "streamcode" VARCHAR,
  "hwy1milepost" DOUBLE,
  "locationid" DOUBLE,
  "locationcode" VARCHAR,
  "locationname" VARCHAR,
  "sectionnum" DOUBLE,
  "reachnum" DOUBLE,
  "unitcode" VARCHAR,
  "latitude" DOUBLE,
  "longitude" DOUBLE,
  "locationtype" VARCHAR,
  "calendarday" BIGINT,
  "weather" VARCHAR,
  "observers" VARCHAR,
  "recorder" VARCHAR,
  "waterclarity_cm" VARCHAR,
  "stage_ft" VARCHAR,
  "spawnersurveyscomments" VARCHAR,
  "spawnersurveyscreateddatetime" TIMESTAMP,
  "upstreamdistance_m" DOUBLE,
  "bank" VARCHAR,
  "dataprocesinglevel" VARCHAR,
  "spawnreddmeasurementsqcflag" VARCHAR,
  "spawnreddmeasurementsqcnotes" VARCHAR,
  "reddconfidence" VARCHAR,
  "speciescode" VARCHAR,
  "commonname" VARCHAR,
  "scientificname" VARCHAR,
  "predictedvalue_knn" VARCHAR,
  "reddpotlength_m" DOUBLE,
  "reddpotwidth_m" DOUBLE,
  "reddtaillength_m" DOUBLE,
  "reddtailwidth_m" DOUBLE,
  "reddlength_m" DOUBLE,
  "reddwidth_m" DOUBLE,
  "reddarea_msq" DOUBLE,
  "fishspecies" VARCHAR,
  "fishnumber" DOUBLE,
  "reddagecode" DOUBLE,
  "comments" VARCHAR,
  "reddupdate" VARCHAR,
  "reddmeasurementupdatedate" TIMESTAMP,
  "reddmeasurementcreateddatetime" TIMESTAMP,
  "redd_finaldetermination" VARCHAR,
  "redd_finaldetermination_by" VARCHAR,
  "redd_finaldetermination_comments" VARCHAR
);

CREATE TABLE nps_imd_sfan_salmonids_summermeasurementsdataset (
  "id" BIGINT,
  "summermeasurementid" BIGINT,
  "eventid" BIGINT,
  "startdate" TIMESTAMP,
  "fieldseason" BIGINT,
  "watershed" VARCHAR,
  "watershed2letter" VARCHAR,
  "streamname" VARCHAR,
  "streamcode" VARCHAR,
  "hwy1milepost" VARCHAR,
  "locationid" DOUBLE,
  "locationcode" VARCHAR,
  "locationname" VARCHAR,
  "unitcode" VARCHAR,
  "latitude" DOUBLE,
  "longitude" DOUBLE,
  "observers" VARCHAR,
  "summerpassidint" DOUBLE,
  "summerpassesqcflag" VARCHAR,
  "summerpassesqcnotes" VARCHAR,
  "pass" DOUBLE,
  "passtype" VARCHAR,
  "visibility" VARCHAR,
  "timeelapsed_s" DOUBLE,
  "volts" VARCHAR,
  "setting" VARCHAR,
  "summerpassescomments" VARCHAR,
  "summerpassescreateddatetime" TIMESTAMP,
  "dataprocessinglevel" VARCHAR,
  "summermeasurementsqcflag" VARCHAR,
  "summermeasurementsqcnotes" VARCHAR,
  "speciescode" VARCHAR,
  "scientificname" VARCHAR,
  "commonname" VARCHAR,
  "lifestage" VARCHAR,
  "lifestagedescription" VARCHAR,
  "forklength_mm" DOUBLE,
  "lengthcategory" VARCHAR,
  "totalweight_g" DOUBLE,
  "bagweight_g" DOUBLE,
  "fishweight_g" DOUBLE,
  "randomsample" VARCHAR,
  "tally" VARCHAR,
  "numberoffish" DOUBLE,
  "injured" VARCHAR,
  "dead" VARCHAR,
  "scales" VARCHAR,
  "tissue" VARCHAR,
  "envelopeid" VARCHAR,
  "priorseason" VARCHAR,
  "pittag" DOUBLE,
  "summermeasurementscomments" VARCHAR,
  "oldfishid" DOUBLE,
  "summermeasurementcreateddatetime" TIMESTAMP,
  "efishsruveysid" BIGINT,
  "efishsurveysqcflag" VARCHAR,
  "efishsurveysqcnotes" VARCHAR,
  "indexreach" VARCHAR,
  "indexunit" DOUBLE,
  "basinwidereach" VARCHAR,
  "basinwideunit" DOUBLE,
  "basinwideunitcode" VARCHAR,
  "habitattype" VARCHAR,
  "habitatdescription" VARCHAR,
  "habitattype2" VARCHAR,
  "habitatdescription2" VARCHAR,
  "studyarea" VARCHAR,
  "sidechannel" VARCHAR,
  "calibrationpool" VARCHAR,
  "temp_c" DOUBLE,
  "flow_cfs" VARCHAR,
  "do_perc" DOUBLE,
  "do_mgl" DOUBLE,
  "conductivity_ms" DOUBLE,
  "specificspecificspecificconductance_ms" DOUBLE,
  "salinity_ppt" DOUBLE,
  "numberofpasses" BIGINT,
  "electrofishsurveycomments" VARCHAR,
  "excludebw" VARCHAR,
  "excludebwcode" VARCHAR,
  "excludebwdescription" VARCHAR,
  "length_m" DOUBLE,
  "averagewidth_m" DOUBLE,
  "maxdepth_m" DOUBLE,
  "crestdepth_m" DOUBLE,
  "averagedepth_m" DOUBLE,
  "meassurfacearea_msq" DOUBLE,
  "measvolume_mcub" DOUBLE,
  "residualpooldepth_m" DOUBLE,
  "habitatdescriptioncomments" VARCHAR
);