You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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:

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.

  • No labels