sql downloads - some mgrs codes appear to not be returned

jhnwllr commented 4 months ago

@MattBlissett It seems like certain bands of MGRS ids are not returned.

https://www.gbif.org/occurrence/download/0017153-240425142415019

plot

Here is a sample of ids that were not found in the download but should seemingly be present unless I have misinterpreted something...

01CCA
01CDA
01CEA
01CFA
01CCV
01CDV
01CEV
01CFV
01CDU
01CEU
01CDT
01CET
01CDS
01CES
01CDR
01CER
01CDQ
01CEQ
01CDP
01CEP
01CDN
01CEN
01CDM
01CEM
01DCK
01DDK
01DEK
01DFK
01DCJ
01DDJ
01DEJ
01DFJ
01DCH
01DDH
01DEH
01DFH
01DCG
01DDG
01DEG
01DFG
01DCF
01DDF
01DEF
01DFF
01DCE
01DDE

SELECT 
  GBIF_MGRSCode(
    100000,  
    decimalLatitude,
    decimalLongitude,
    COALESCE(coordinateUncertaintyInMeters, 0) 
  ) AS mgrs,
  COUNT(DISTINCT speciesKey) AS unique_species_count
FROM
  occurrence
GROUP BY
  mgrs

MattBlissett commented 4 months ago

I think some of the smallest values like 01CCA are invalid, see image where the grid starts at D for -180°.

Zones 1-9 are currently formatted without a leading zero. I can't see an official specification, but if it's this then I should add that zero.

I haven't looked at what's happening in Australia.

Note you should filter for coordinateUncertaintyInMeters being reasonable, there are some extremely large values. (We should probably add a geospatial issue for very large values in this field.)

MattBlissett commented 2 months ago

Zones under 10 are now output with a leading 0.

gbif / occurrence

sql downloads - some mgrs codes appear to not be returned #348