...
| Code Block | ||
|---|---|---|
| ||
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.