Using SQL to query iNat's DWCA taxonomy export

Tutorials
6

Species without common names for a specified lexicon and a specific country (=checklist?) and common names that occur for more then one species within a lexicon:

SELECT vnames.vernacularName, vnames.contributor, Taxa.scientificName, vnames.locality, vnames.source, vnames.lexicon, vnames.created, Taxa.taxonID
FROM Taxa INNER JOIN vnames ON Taxa.id = vnames.id
*WHERE *
((
*(vnames.vernacularName) In *

(SELECT vnames.vernacularName
FROM Taxa INNER JOIN vnames ON Taxa.id = vnames.id
WHERE taxonRank= ‘Species’
GROUP BY [vernacularName] *
HAVING (Count()>1 ))

))
ORDER BY vnames.vernacularName;

Interessting file, you can also find out which lexicon has the most common names after English and which user has the first position in the leader board of adding common names…

Postscript:
There is a policy for common names and genus and infraspecies. I was wondering what to do with this so I removed them from the result of this query, but that is not correct…
https://forum.inaturalist.org/t/duplicating-common-name-for-nominate-subspecies/11298/8?u=optilete

1 Like