code for finding material for temporal comparisons?

[ondatra]

Musing here about how to find material in our collections (or other databases like GBIF) to use in temporal comparisons of genetic and/or morphological change.
Has someone already written code or a program that would allow searching for 1. some taxon, 2. some spatial window (geographic, elevational), and 3. some stated temporal distance with an minimum sample size? I could do such a thing manually, like searching for all Microtus californicus from 1890-1950, map it, search from 1951 to 2026, map it, look for overlap. But, it would be awesome to automate such a process, adjust spatial window size, sample size, parts available, etc. Perhaps someone has already done this?
Thanks,
Chris

[Cam Webb]

Hi Chris,

Is this something like what you were asking for? Some SQL to run on arctos-test [1] (replace with your chosen values):

SELECT scientific_name, COUNT(*) AS freq FROM flat
  WHERE guid_prefix = 'UAM:Herb' AND
  dec_long > -180 AND dec_long < -160 AND
  dec_lat > 50 AND dec_long < 60 AND
  scientific_name ~ 'Salix' AND
  began_date > '1950-01-01' AND ended_date < '1970-01-01'
GROUP BY scientific_name
ORDER BY freq DESC, scientific_name
LIMIT 100;

Salix arctica 47
Salix fuscescens 23
Salix pulchra 21
Salix ovalifolia var. ovalifolia 18
Salix glauca var. glauca 16
...

Cam

[1]: https://arctos-test.tacc.utexas.edu/tools/userSQL.cfm