Ohio Odonata Society Database

Using Dragonfly 2018 data

The Ohio Odonata Society database is being made available for use with SQLite. This database is recent as of December 2018 and includes vetted iNaturalist records from the same time.

You will need
1) the SQLite database (Dragonfly2018.db) and
2) a tool that can interface with the db
There a number of SQLite managers across computing environments.

a couple examples:
DB Browser for SQLite  – select the specific version for your OS and install
Plug-in for Firefox

download the Dragonfly2018.db file to a desired place on your system (desktop is acceptable)

-open the DB Browser application
-click on open database, select odocomp
-you should see the complete database in the ‘browse’ tab

Searching data
you can limit the display by typing in one or more ‘filters’
1) in the column for COUNTY, type in a county name
– you should see the displayed data rows change
2) in the column for MONTH, type in a month reference
– you should see the displayed data rows change
to remove the filters, click on the ‘x’ to cancel, or use the delete key to remove the text

Simple queries
the ‘Execute Query’ tab allows you to define a query, and get more specific results than what you see in the ‘Browse’ tab.

1) this query will provide a list of distinct species for a county:

select distinct SP_NUMBER, GENUS, SPECIES, NAME from OOS2018 where county = ‘Champaign’ order by GENUS, SPECIES

copy and paste the line above and substitute your desired county name for Champaign, then click the blue arrow to run the query


If you still have questions or want help building your own query, contact MaLisa Spring or Jim Lemon (jlem@woh.rr.com) .


Other example Queries from Jim:
1) early and late flight dates for a species in a county
2) missing species from county – presence in an adjacent county
3) frequency – record counts by county


Harder example queries:

1) Medina Co – recent records from one or more surrounding counties, but no record from target county – this can be modified by changing the target (Medina) to a desired Co, then changing the surrounding Co list (‘Summit’, etc) to appropriate values. Also the ‘1999’ year value can be tweaked to a desired target (like > ‘1950’, or < ‘1970’, etc)

SQL query:
select distinct sp_number as OOSID, name as common, ‘(‘||genus||’ ‘||species||’)’ as scientific from OOS2017 where county in (‘Summit’, ‘Lorain’, ‘Cuyahoga’, ‘Ashland’, ‘Wayne’) and year > ‘1999’
and sp_number not in (select distinct sp_number from OOS2016 where county = ‘Medina’) order by sp_number


2) Clinton and Highland Counties – species with single record count (less than 2) – these would be species that are rare or simply not looked for – for higher counts, just expand the “cc < 2” to an appropriate count – you could also expand or contract the Co list – for where you’re travelling.

SQL query:
select distinct genus, species, name, count(*) as cc from OOS2017 where county in (‘Clinton’,’Highland’)
group by genus, species, name having cc < 2 order by genus, species


3) Early/Late Flight – this is interactive on the Browse tab

a) enter desired destination in Filter box under COUNTY – try Champaign – actually Cham would work, just enough to be distinct

b) enter desired species identifier (SP_NUMBER, or GENUS and SPECIES, or NAME) – try Elfin under name

c) look at MONTH – if necessary, enter a month (first three letters) – for either early or late.

d) look at DAYOFMONTH – click on DAYOFMONTH to sort (this is a toggle, click for ascending, next click for descending)

results – with current data, for early flight we see multiple records on May 31 for Elfin Skimmer (this will change when we next load from iNat) – for late flight we see Sep 17 (kind of an outlier data point).