How to search for first observations in my state (mine)

My yard survey has turned up a few Leps not seen in my state before, which I’d like to submit to Bugguide, which serves as a gateway to Moth Photographers Group. But I can’t remember which ones were rare and don’t want to slog through all my obs one by one.

Can I use a URL search to filter my observatons for:

  1. Leps observed by me, but no one else, in West Virginia.
  2. Leps observed by me, but fewer than 10 others, in West Virginia.

For pure curiosity:
3. Any species that I have submitted, that are rare in the location observed.

Thanks

You could filter by place, then look at the species tab… Your rare ones will be at the end as it is sorted by number of obs

the way i would handle #1 & #2 is to download a list of Lepidoptera observations in West Virginia. then take the results into something (ex. Excel, a database, etc.) that you can use to aggregate observations by species.

for the first item, return just the species where the max observer (based on alpha order of observer login or id) = min observer = you.

for the second item, you would find the species that you have observed, then do a distinct count of observers for each of those species, returning only the ones that have a distinct count of observers less than 11 (10 + you).

for the final item, i don’t think “rare in the location observed” is something that is well-defined across all potential species. so unless you have some sort source that defines this to bump up against your list of species, there’s not really a good way to do this. you could assume that count of observations is a proxy for rarity, but then you’ll need to establish what the count threshold for that definition of rarity would be.

2 Likes

This is way above my skill level with excel, but I will muse on it & get help.

Thank you.

I had already created a project “Moths of West Virginia,” so I scrolled to the bottom of the species list and am looking for ones I recall seeing. Easiest solution at this point, hoping the memory holds. :-)
Thanks

actually, if you do #2, you can get #1, too, just by looking for cases where # of distinct observers = 1 vs distinct observers > 1.

if you were to load, say, verifiable WV Lep observations into a database (or spreadsheet that you can run some SQL against), you could run some SQL like this:

SELECT A.taxon_species_name
	, MAX(taxon_id) AS taxon_id
	, COUNT(DISTINCT user_login) AS observer_count
	, COUNT(id) AS observation_count
, MIN(id) AS first_submitted_obs
FROM (
	SELECT DISTINCT taxon_species_name
	FROM observations 
	WHERE user_login = 'becksnyc'
	) A
JOIN observations B
	ON A.taxon_species_name = B.taxon_species_name
GROUP BY A.taxon_species_name
HAVING COUNT(DISTINCT user_id) < 11
ORDER BY COUNT(DISTINCT user_login) ASC
	, A.taxon_species_name ASC

and that would return results that look something like this:

taxon name taxon id observer count observation count first submitted obs
Acronicta noctivaga 212445 1 1 61831014
Aethes angulatana 151807 1 1 61818257
Aethes rana 417063 1 5 61825714
Aglossa disciferalis 325918 1 1 61836448
Aglossa pinguinalis 212729 1 5 61857405
Apantesis arge 606563 1 1 72551644
Aphomia terrenella 213951 1 3 61808067
Argyrotaenia alisellana 214153 1 1 61882001
Argyrotaenia pinatubana 214161 1 3 61971429
Arugisa lutea 214368 1 3 61808075
Aterpia approximana 214542 1 1 61835450
Bactra furfurana 173472 1 2 61830998
Bedellia somnulentella 214958 1 1 68456366
Bibarrambla allenella 215194 1 2 61971418
Cabera variolaria 215615 1 1 61831037
Caenurgia chloropha 69669 1 1 61808066
Caloptilia negundella 318528 1 1 61838758
Caloptilia stigmatella 215900 1 1 61824277
Chloridea virescens 509353 1 1 59241772
Chytonix palliatricula 52906 1 1 61824290
Coleophora mayrella 321675 1 8 61838746
Condica sutor 217495 1 2 61913049
Crambodes talidiformis 217671 1 1 61857407
Cycnia oregonensis 143117 1 1 61880585
Diastictis argyralis 342848 1 3 61824272
Dichomeris inversella 218377 1 2 61836443
Dichomeris juncidella 218378 1 1 61971419
Dichomeris ligulella 179029 1 1 71940375
Dichomeris offula 218383 1 1 61978327
Eoreuma densellus 700601 1 2 61856501
Ephestia kuehniella 324640 1 1 61836450
Ephestiodes infimella 219287 1 1 68965961
Epiblema abruptana 417008 1 1 61838786
Epiblema glenni 324291 1 1 61838783
Epiblema minutana 1109554 1 1 68967782
Episimus argutana 357353 1 1 68643804
Eugnosta bimaculana 475049 1 1 61888880
Frumenta nundinella 367331 1 1 61838788
Garella nilotica 220292 1 1 61836458
Gerdana caritella 220372 1 1 61914339
Hedya chionosema 220995 1 1 61824289
Hedya separatana 220997 1 1 61838767
Holcocera chalcofrontella 221398 1 2 61836439
Homochlodes fritillaria 221438 1 1 61836464
Homorthodes lindseyi 357969 1 1 61808103
Homostinea curviliniella 325313 1 3 61818262
Hyperstrotia nana 504475 1 2 61836466
Hypomecis umbrosaria 122470 1 1 61838753
Leptostales ferruminaria 177479 1 4 61888872
Leucania incognita 223548 1 1 68502790
Lipocosmodes fuliginosalis 177342 1 5 61838789
Lygropia rivulalis 205236 1 2 61880609
Macaria aequiferaria 224250 1 1 61808072
Macrochilo louisiana 177214 1 1 61913046
Meganola phylla 332250 1 2 61825736
Metalectra richardsi 224971 1 1 61835451
Mompha eloisella 322378 1 1 61808091
Moodna ostrinella 225241 1 1 61824265
Nascia acutellus 176853 1 1 61808082
Nemapogon auropulvella 326306 1 2 61856512
Nemapogon clematella 416837 1 1 61907890
Neodactria luteolellus 143098 1 1 61836447
Niditinea orleansella 199878 1 1 61808077
Nola cereella 225986 1 1 61808062
Nycteola metaspilella 226047 1 1 61818256
Oenoe hybromella 416841 1 1 68647133
Olethreutes concinnana 148025 1 1 61882002
Olethreutes monetiferanum 636793 1 1 61882008
Omphalocera cariosa 417127 1 2 61838743
Ozarba aeria 226803 1 1 61856520
Palpita freemanalis 324620 1 1 61907884
Papaipema nebris 154228 1 3 59251211
Parapediasia decorellus 417112 1 2 61824267
Peoria longipalpella 325507 1 2 61913064
Phycitodes reliquellum 568906 1 1 61913042
Psaphida resumens 209329 1 1 72566226
Psara obscuralis 324627 1 1 61882020
Pseudogalleria inimicella 417038 1 1 61838755
Schinia arcigera 143196 1 1 61913053
Sonia canadana 231147 1 1 61838742
Sonia constrictana 417010 1 1 61825725
Spoladea recurvalis 52043 1 2 62131961
Taygete attributella 232337 1 1 61838766
Thyraylia bunteana 320966 1 2 61808101
Tinea pellionella 193928 1 1 61818280
Varneria postremella 363228 1 1 61831032
Wockia asperipunctella 453470 1 1 61971431
Acleris flavivittana 212333 2 4 2403549
Acleris placidana 823767 2 2 6910767
Acrolophus plumifrontella 212400 2 3 6910992
Agonopterix argillacea 212736 2 3 61880591
Agonopterix pulvipennella 210604 2 8 2826722
Anacampsis levipedella 416945 2 2 50595092
Anicla illapsa 213630 2 4 2612460
Anicla infecta 213631 2 5 6891797
Antaeotricha schlaegeri 213740 2 3 3247481
Caloptilia blandella 342492 2 3 54410901
Caloptilia rhoifoliella 215898 2 5 6495715
Celiptera frustulum 216354 2 2 2434693
Chionodes discoocellella 216736 2 4 61913061
Diastictis ventralis 143284 2 3 2672532
Dichomeris flavocostella 205206 2 4 6207798
Dichomeris punctipennella 218385 2 2 7328590
Dichrorampha bittana 218394 2 2 6390309
Dicymolomia julianalis 218424 2 5 4153309
Epiblema otiosana 142999 2 3 6628888
Eucosma parmatana 511560 2 5 4022240
Eupsilia vinulenta 143010 2 8 4195795
Euzophera semifuneralis 220023 2 3 3262353
Feltia subterranea 152979 2 2 56381887
Geina periscelidactylus 144071 2 2 4015092
Glaphyria fulminalis 220389 2 2 2429304
Glyphidocera juniperella 333290 2 5 6654872
Herpetogramma sphingealis 324298 2 2 2429487
Homoeosoma deceptorium 221445 2 2 2459327
Homophoberia apicosa 143613 2 4 4227015
Hypena sordidula 221970 2 2 7024716
Hypenodes fractilinea 221972 2 5 17309501
Hyperstrotia secta 222005 2 2 6892210
Idaea scintillularia 222167 2 2 4259587
Isochaetes beutenmuelleri 222331 2 2 6910902
Leucania linda 223552 2 4 13734718
Leuconycta lepidula 223564 2 2 56280394
Limnaecia phragmitella 223680 2 6 52329094
Lithophane baileyi 179726 2 3 9689532
Macrochilo absorptalis 177209 2 2 54301177
Macronoctua onusta 224312 2 2 33490406
Metalectra discalis 153304 2 3 53443430
Nigetia formosalis 225958 2 7 1870368
Oegoconia novimundi 321374 2 3 7175152
Oligia modica 147941 2 4 61857406
Papaipema cataphracta 82381 2 5 2445223
Paralobesia viteana 334338 2 3 7171588
Pelochrista derelicta 511586 2 2 61971405
Perispasta caeculalis 227476 2 2 4015477
Petrophila canadensis 201345 2 2 52456207
Phalaenostola eumelusalis 143735 2 6 50417853
Phtheochroa riscana 345629 2 2 61913052
Ponometia erastrioides 228634 2 7 3263140
Proteoteras moffatiana 228846 2 2 2408756
Pseudorthodes vecors 143094 2 2 6891532
Saucrobotys futilalis 146889 2 2 31681653
Sigela brauneata 338318 2 2 45843501
Sparganothis distincta 324666 2 2 7171443
Zanclognatha marcidilinea 311260 2 2 463759
Acleris robinsoniana 212349 3 5 2818648
Acrobasis angusella 212369 3 4 2404483
Adoxophyes furcatana 417052 3 3 16710204
Agnorisma badinodis 154478 3 7 4294433
Agnorisma bollii 155298 3 4 4389402
Allagrapha aerea 81670 3 10 4026978
Azenia obtusa 202013 3 4 6891661
Balsa tristrigella 214842 3 4 3343976
Cabera erythemaria 205194 3 3 31702046
Caloptilia violacella 324207 3 3 4061439
Catocala residua 216310 3 3 2459365
Celypha cespitana 324008 3 9 4027962
Chionodes mediofuscella 216738 3 9 2914926
Chloropteryx tepperaria 216785 3 4 15715489
Clostera inclusa 196518 3 3 7021713
Condylolomia participalis 217498 3 3 50594705
Crocidophora serratissimalis 217766 3 6 6370927
Cucullia asteroides 146625 3 4 2427495
Elophila gyralis 332646 3 4 13968589
Elophila obliteralis 335071 3 4 3951542
Endothenia hebesana 219169 3 8 8399550
Epiblema strenuana 219298 3 6 6632231
Eupsilia morrisoni 204089 3 5 2631620
Evergestis pallidata 81685 3 4 4154285
Glena cribrataria 220395 3 3 1358878
Haimbachia placidella 470638 3 4 50594758
Hulda impudens 366507 3 4 6485904
Hymenia perspectalis 142995 3 5 2350557
Hypsoropha hormos 222116 3 3 2698509
Leucania adjuta 223541 3 3 31525638
Leucania ursula 207995 3 5 1842414
Lochmaeus bilineata 224089 3 3 2709786
Macaria bisignata 146886 3 5 57753535
Macrochilo litophora 177213 3 5 14780251
Monopis longella 978666 3 8 4027473
Nedra ramosula 147093 3 4 1420033
Nematocampa resistaria 81656 3 3 6474733
Norape ovina 225994 3 4 14157614
Ogdoconta cinereola 143849 3 6 6390785
Olethreutes fasciatana 143659 3 3 5045112
Oruza albocostaliata 176174 3 4 7155995
Paectes oculatrix 84000 3 11 2873735
Pelochrista dorsisignatana 511557 3 6 4044355
Pelochrista similiana 511559 3 3 61907888
Phlogophora periculosa 124183 3 3 7720088
Plusiodonta compressipalpis 125475 3 4 4199135
Promalactis suzukiella 123611 3 6 3358031
Proteoteras aesculana 228844 3 9 10839313
Schinia rivulosa 143382 3 7 2757812
Sciota subcaesiella 230622 3 3 57176188
Selenia kentaria 129426 3 4 2921728
Tetanolita mynesalis 154333 3 12 15475579
Xylesthia pruniramiella 233841 3 3 52641887
Acronicta increta 212434 4 5 6890580
Aglossa cuprina 143608 4 5 3811183
Anageshna primordialis 213510 4 4 6405871
Apoda biguttata 214013 4 5 6910911
Arogalea cristifasciella 214195 4 4 7228105
Arta statalis 214211 4 6 14684000
Calledapteryx dryopterata 143476 4 6 2875158
Chimoptesis pennsylvaniana 417026 4 5 2634759
Chrysendeton medicinalis 363819 4 5 2429328
Chrysodeixis includens 148908 4 4 4144589
Cisthene plumbea 217046 4 7 1870581
Colocasia propinquilinea 160791 4 4 2439510
Crambidia pallida 143520 4 7 21943744
Cyclophora packardi 217969 4 5 2662044
Dargida diffusa 218139 4 6 2410829
Elaphria grata 146713 4 19 2430354
Epicallima argenticinctella 324766 4 8 2688256
Epipaschia superatalis 219354 4 9 2429416
Eudonia strigalis 146946 4 5 4022700
Eudryas unio 219719 4 4 30179811
Euzophera ostricolorella 244605 4 4 32281650
Hypena bijugalis 130010 4 5 7024614
Hypsopygia costalis 129149 4 10 4171646
Idia lubricalis 119066 4 5 7024662
Lithacodia musta 223965 4 7 7208766
Machimia tentoriferella 81689 4 7 4026530
Martyringa latipennis 224466 4 7 6207684
Natada nasoni 225460 4 4 61628321
Ochropleura implecta 143121 4 9 2719724
Orthosia hibisci 226646 4 12 2775394
Palpita magniferalis 208112 4 9 3344003
Papaipema baptisiae 82382 4 5 4170047
Pasiphila rectangulata 129223 4 5 7021588
Peridroma saucia 179339 4 6 6417440
Phaecasiophora niveiguttana 325858 4 7 4045031
Plutella xylostella 126765 4 8 2826730
Pococera asperatella 321469 4 13 7020382
Polygrammodes flavidalis 143017 4 11 2429495
Pyrausta bicoloralis 205301 4 8 2751830
Rusicada privata 554232 4 12 54603789
Timandra amaturaria 142994 4 4 4199239
Vitula edmandsii 233590 4 5 2461209
Zale galbanata 207983 4 7 4044554
Zanclognatha lituralis 143027 4 4 26750061
Agrotis ipsilon 126276 5 17 2426515
Athetis tarda 214552 5 7 4022103
Baileya ophthalmica 143525 5 6 3977967
Caenurgina erechtea 126640 5 11 463741
Cerastis tenebrifera 216431 5 9 2775225
Choristoneura parallela 216808 5 12 6642141
Clepsis peritana 132789 5 13 4009507
Coryphista meadii 143201 5 18 1367173
Crocidophora tuberculalis 217767 5 9 2429502
Cydia latiferreana 148907 5 8 2664535
Dasychira obliquata 218152 5 7 1551644
Glenoides texanaria 220398 5 14 59214927
Hypena deceptalis 143654 5 6 2410909
Hypena manalis 221967 5 8 4021666
Idaea bonifata 323057 5 16 2389218
Lophosis labeculata 177241 5 5 6684942
Mocis texana 225188 5 17 2411524
Morrisonia latex 176927 5 6 4044448
Orthodes majuscula 194598 5 9 2422262
Perigea xanthioides 227470 5 12 2732180
Phalaenostola larentioides 227683 5 13 2734543
Phosphila miselioides 227864 5 9 4043107
Platynota idaeusalis 146797 5 12 6096215
Pseudothyatira cymatophoroides 204699 5 7 2434334
Schizura leptinoides 230608 5 5 2403379
Spargaloma sexpunctata 231254 5 6 2430411
Spodoptera frugiperda 132468 5 7 4358256
Tetanolita floridana 232390 5 13 463753
Tosale oviplagalis 145534 5 8 463754
Trigrammia quadrinotaria 232960 5 6 6497483
Xanthorhoe ferrugata 143485 5 8 2771611
Zale horrida 233933 5 9 3035932
Acronicta interrupta 212436 6 8 2455069
Adoneta spinuloides 212510 6 11 3799685
Agriphila ruricolella 146968 6 19 4038370
Apantesis parthenice 606575 6 6 4006593
Apoda y-inversum 214015 6 8 361291
Argyrotaenia velutinana 208118 6 15 2616832
Choristoneura rosaceana 143728 6 10 4029755
Galgula partita 143119 6 18 2442581
Haploa lecontei 84038 6 12 5166826
Lacinipolia explicata 321467 6 12 2700420
Nerice bidentata 201280 6 6 7021694
Pangrapta decoralis 226889 6 8 2726655
Pero ancetaria 227512 6 6 6892391
Phigalia strigataria 227741 6 12 2704842
Plagodis phlogosaria 205241 6 9 3035935
Platynota flavedana 208090 6 14 2738418
Pleuroprucha insulsaria 143105 6 13 2740108
Plodia interpunctella 124184 6 13 7020542
Pseudohermonassa bicarnea 205065 6 7 2750396
Renia adspergillus 126992 6 10 6914644
Spragueia leo 143592 6 6 2765916
Tortricidia flexuosa 128785 6 9 3810926
Tortricidia testacea 232759 6 6 6452338
Urola nivalis 132736 6 14 2429504
Baileya australis 215225 7 10 2436493
Callopistria mollissima 199999 7 8 2625251
Catocala piatrix 143751 7 8 14887831
Dasylophia anguina 218158 7 7 2402728
Elaphria versicolor 218931 7 13 7099291
Gluphisia septentrionis 143274 7 7 6938534
Misogada unicolor 225178 7 9 3077208
Nephelodes minians 153076 7 17 4069124
Orthonama obstipata 143002 7 18 3810681
Papaipema cerussata 147320 7 10 4204468
Pseudeustrotia carneola 143118 7 18 2405340
Schizura unicornis 126207 7 7 361082
Sparganothis sulfureana 81683 7 16 2403302
Xestia dolosa 143123 7 7 31743658
Agriphila vulgivagellus 947590 8 18 2429509
Chlorochlamys chloroleucaria 149017 8 14 1870414
Chytolita morbidalis 129226 8 10 291859
Clemensia albata 81672 8 15 2426548
Emmelina monodactyla 127431 8 16 2432163
Evergestis rimosalis 359686 8 12 2429406
Fissicrambus mutabilis 143730 8 14 4009534
Herpetogramma pertextalis 143008 8 15 2429336
Horisme intestinata 205228 8 11 2696554
Idia aemula 127460 8 23 2447690
Patalene olyzonaria 227307 8 9 356052
Amorbia humerosana 213414 9 13 4048317
Anagrapha falcifera 59300 9 11 2604877
Condica vecors 217496 9 22 3247446
Heterocampa guttivitta 203462 9 10 7021773
Idia americalis 127457 9 12 2445573
Lochmaeus manteo 153443 9 11 7021758
Marimatha nigrofimbria 143181 9 21 1848847
Microcrambus biguttellus 225105 9 16 2395909
Nomophila nearctica 58750 9 17 2429498
Pantographa limata 205240 9 13 2429313
Pyrausta acrionalis 142998 9 21 2407757
Scoparia biplagialis 146963 9 13 2429417
Anavitrinella pampinaria 63750 10 15 2449925
Autographa precationis 142990 10 28 4375138
Caenurgina crassiuscula 52900 10 20 1693003
Catocala ultronia 132881 10 16 7024817
Digrammia ocellinata 218446 10 18 4032983
Euchlaena amoenaria 219627 10 11 463756
Hydrelia inornata 221596 10 13 6880045
Hypena madefactalis 143125 10 18 2410884
Iridopsis defectaria 222284 10 33 2427127
Lascoria ambigualis 146750 10 16 2434401
Palthis angulalis 128242 10 21 3059327
Xanthorhoe lacustrata 205036 10 19 1870428

(if anyone is interested in learning how to load data into a simple database, let me know.)