Versions Compared

Old Version 4

changes.mady.by.user Jong Lee

Saved on

compared with

New Version 5

changes.mady.by.user Aaron Hoyle-Katz

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

The RiverWatch Database files can be found on Box.

To extract a table containing the location, time, and observations for each entry, run the following SQL statement on the RiverWatch Database:

Code Block
languagesql
SELECT 
	ActivityT.SiteID, 
	ActivityT.FieldDate, 
	ActivityT.Flatworm, ActivityT.AquaticWorm, 
	ActivityT.Leech, ActivityT.Sowbug, 
	ActivityT.Scud, ActivityT.Dragonfly, 
	ActivityT.BroadwingedDamselfly, ActivityT.NarrowwingedDamselfly, 
	ActivityT.Dobsonfly, ActivityT.Alderfly, 
	ActivityT.TorpedoMayfly, ActivityT.SwimmingMayfly, 
	ActivityT.ClingingMayfly, ActivityT.CrawlingMayfly, 
	ActivityT.BurrowingMayfly, ActivityT.ArmoredMayfly, 
	ActivityT.OtherMayfly, ActivityT.Stonefly, 
	ActivityT.HydropsychidCaddisfly, ActivityT.SnailCaseCaddisfly, 
	ActivityT.SaddlecaseCaddisfly, ActivityT.OtherCaddisfly, 
	ActivityT.RiffleBeetle, ActivityT.WhirligigBeetle, 
	ActivityT.WaterPennyBeetle, ActivityT.Cranefly, 
	ActivityT.BitingMidge, ActivityT.BloodwormMidge, 
	ActivityT.Midge, ActivityT.Blackfly, 
	ActivityT.Snipefly, ActivityT.OtherFly, 
	ActivityT.LefHandedSnail, ActivityT.RightHandedSnail, 
	ActivityT.PlanorbidSnail, ActivityT.Limpet, 
	ActivityT.OperculateSnail, 
	SiteT.SiteID, 
	SiteT.SiteName, 
	SiteT.Latitude, 
	SiteT.Longitude

FROM ActivityT 
INNER JOIN SiteT ON ActivityT.SiteID=SiteT.SiteID;

ActivityT contains each citizen scientist observation with the number of each observed species, and the observation date. SiteT contains all observation site locations (latitude and longitude) and a site name. Joining these two tables by the common SiteID field yields a table with all the requisites for data ingestion.