Query to extract record counts from Indicia live data

[johnvanbreda]

This query provides counts of verified/all records for each species concept/grid square combination. Here's an example query:

select taxa_taxon_list_external_key, map_sq_1km_id, 
  sum(case record_status when 'V' then 1 else 0 end) as verified, 
  sum(case record_status when 'R' then 0 else 1 end) as all_records
from cache_occurrences_functional 
group by taxa_taxon_list_external_key, map_sq_1km_id

This will be merged with model output to enhance the usefulness of the data.

Notes:

• Might be necessary to run a date range filter on the above, e.g. last 5 years, or a range of years to match the model run it is being merged with.
• No need to group by week number as we are interested in absolute presence throughout the year, not just for the model output's week.

[johnvanbreda]

Consider keeping this in a separate index, otherwise multiple date-limited model outputs would need to be updated with a single set of values (duplication). Easier to keep updated if only one copy. Multiple indexes can be searched in one hit - see https://www.elastic.co/guide/en/elasticsearch//reference/current/search-multiple-indices.html