Using SQL to query iNat's DWCA taxonomy export

Tutorials
4

this is also possible by querying against the data in the DWCA export file.

for example, this query:

SELECT A.scientificName, A.taxonID
FROM taxa A
LEFT JOIN vnames B
	ON A.id = B.id
WHERE taxonRank = 'species'
	AND genus = 'Asclepias'
	AND B.id is NULL

returns:

scientificName taxonID
Asclepias mcvaughii https://www.inaturalist.org/taxa/207411
Asclepias circinalis https://www.inaturalist.org/taxa/277429
Asclepias jorgeana https://www.inaturalist.org/taxa/277432
Asclepias mirifica https://www.inaturalist.org/taxa/277434
Asclepias pratensis https://www.inaturalist.org/taxa/277436
Asclepias pseudorubricaulis https://www.inaturalist.org/taxa/277437
Asclepias scheryi https://www.inaturalist.org/taxa/277438
Asclepias subaphylla https://www.inaturalist.org/taxa/277439
Asclepias vinosa https://www.inaturalist.org/taxa/277440
Asclepias virletii https://www.inaturalist.org/taxa/277441
Asclepias nummularioides https://www.inaturalist.org/taxa/277619
Asclepias zanthodacryon https://www.inaturalist.org/taxa/277621
Asclepias atroviolacea https://www.inaturalist.org/taxa/284334
Asclepias conzattii https://www.inaturalist.org/taxa/284336
Asclepias coulteri https://www.inaturalist.org/taxa/284337
Asclepias crocea https://www.inaturalist.org/taxa/284338
Asclepias fournieri https://www.inaturalist.org/taxa/284340
Asclepias gentryi https://www.inaturalist.org/taxa/284341
Asclepias masonii https://www.inaturalist.org/taxa/284343
Asclepias pellucida https://www.inaturalist.org/taxa/284345
Asclepias puberula https://www.inaturalist.org/taxa/284346
Asclepias schaffneri https://www.inaturalist.org/taxa/284348
Asclepias standleyi https://www.inaturalist.org/taxa/284349
Asclepias bifida https://www.inaturalist.org/taxa/284616
Asclepias leptopus https://www.inaturalist.org/taxa/284619
Asclepias pringlei https://www.inaturalist.org/taxa/284622
Asclepias kamerunensis https://www.inaturalist.org/taxa/437303
Asclepias elegantula https://www.inaturalist.org/taxa/503712
Asclepias barjoniifolia https://www.inaturalist.org/taxa/542965
Asclepias adscendens https://www.inaturalist.org/taxa/580329
Asclepias bicuspis https://www.inaturalist.org/taxa/580331
Asclepias brevicuspis https://www.inaturalist.org/taxa/580332
Asclepias compressidens https://www.inaturalist.org/taxa/580334
Asclepias concinna https://www.inaturalist.org/taxa/580335
Asclepias cooperi https://www.inaturalist.org/taxa/580336
Asclepias crassinervis https://www.inaturalist.org/taxa/580337
Asclepias disparilis https://www.inaturalist.org/taxa/580341
Asclepias dissona https://www.inaturalist.org/taxa/580342
Asclepias expansa https://www.inaturalist.org/taxa/580344
Asclepias fallax https://www.inaturalist.org/taxa/580345
Asclepias fulva https://www.inaturalist.org/taxa/580347
Asclepias gordon-grayae https://www.inaturalist.org/taxa/580349
Asclepias meliodora https://www.inaturalist.org/taxa/580353
Asclepias meyeriana https://www.inaturalist.org/taxa/580354
Asclepias montevaga https://www.inaturalist.org/taxa/580355
Asclepias monticola https://www.inaturalist.org/taxa/580356
Asclepias nana https://www.inaturalist.org/taxa/580358
Asclepias navicularis https://www.inaturalist.org/taxa/580359
Asclepias oreophila https://www.inaturalist.org/taxa/580360
Asclepias patens https://www.inaturalist.org/taxa/580361
Asclepias peltigera https://www.inaturalist.org/taxa/580362
Asclepias praemorsa https://www.inaturalist.org/taxa/580363
Asclepias randii https://www.inaturalist.org/taxa/580364
Asclepias rara https://www.inaturalist.org/taxa/580365
Asclepias schlechteri https://www.inaturalist.org/taxa/580366
Asclepias ulophylla https://www.inaturalist.org/taxa/580368
Asclepias velutina https://www.inaturalist.org/taxa/580369
Asclepias vicaria https://www.inaturalist.org/taxa/580370
Asclepias woodii https://www.inaturalist.org/taxa/580371
Asclepias dregeana https://www.inaturalist.org/taxa/596584
Asclepias melantha https://www.inaturalist.org/taxa/622354
Asclepias alpestris https://www.inaturalist.org/taxa/625237
Asclepias amabilis https://www.inaturalist.org/taxa/625238
Asclepias ameliae https://www.inaturalist.org/taxa/625239
Asclepias candida https://www.inaturalist.org/taxa/625264
Asclepias dependens https://www.inaturalist.org/taxa/625274
Asclepias flanaganii https://www.inaturalist.org/taxa/625275
Asclepias graminifolia https://www.inaturalist.org/taxa/625276
Asclepias grandirandii https://www.inaturalist.org/taxa/625277
Asclepias foliosa https://www.inaturalist.org/taxa/625526
Asclepias inaequalis https://www.inaturalist.org/taxa/625528
Asclepias langsdorffii https://www.inaturalist.org/taxa/625530
Asclepias longirostra https://www.inaturalist.org/taxa/625539
Asclepias longissima https://www.inaturalist.org/taxa/625542
Asclepias minutiflora https://www.inaturalist.org/taxa/625560
Asclepias mtorwiensis https://www.inaturalist.org/taxa/625563
Asclepias nuttii https://www.inaturalist.org/taxa/625572
Asclepias palustris https://www.inaturalist.org/taxa/625582
Asclepias pilgeriana https://www.inaturalist.org/taxa/625584
Asclepias pseudoamabilis https://www.inaturalist.org/taxa/625586
Asclepias pseudofimbriata https://www.inaturalist.org/taxa/625588
Asclepias pygmaea https://www.inaturalist.org/taxa/625593
Asclepias senecionifolia https://www.inaturalist.org/taxa/860958
Asclepias fimbriata https://www.inaturalist.org/taxa/1104559
Asclepias boliviensis https://www.inaturalist.org/taxa/1159860
Asclepias schumanniana https://www.inaturalist.org/taxa/1188941
1 Like