Occurrence cube downloads

[MortenHofft]

ping @peterdesmet and @MattBlissett

I've started the work of adding a new download option

And an SQL ui%20OVER%20(PARTITION%20BY%20kingdomKey))%20AS%20kingdomCount,%0A%20%20COUNT(*)%20AS%20occurrences,%20MIN(GBIF_TemporalUncertainty(eventDate))%20AS%20minTemporalUncertainty,%20MIN(COALESCE(coordinateUncertaintyInMeters,%201000))%20AS%20minCoordinateUncertaintyInMeters%0AFROM%0A%20%20occurrence%0AWHERE%20%0A%20%20%22year%22%20%3E%202000%0AGROUP%20BY%0A%20%20taxonRank,%20taxonomicStatus,%20kingdom,%20kingdomKey,%20phylum,%20phylumKey,%20%22year%22) for downloads

Could you please help evaluate if this is functionally what you had in mind? Functionally there are 2 things that aren't working

• downloads always fail despite being accepted by the sql validator. Perhaps: https://github.com/gbif/occurrence/issues/360
• the WHERE isn't filled. Waiting for https://github.com/gbif/occurrence/issues/356

If the functions are as you expected, then we could think about usability (bearing in mind that we will have to reimplement this in the recent future)

• Do we need articles help pages to refer to?
• More help texts in the UI?
• different labels?
• Extend the general download API with a description field, which we could auto generate (from the form) for more a human readable context.
• allow comments in the SQL?
• What is a good name for the download type. And what title?
• How to make this meaningful with the table we have for other formats to show what you can expect
• does an about page for SQL editor makes sense. placeholder for now%20OVER%20(PARTITION%20BY%20kingdomKey))%20AS%20kingdomCount,%0A%20%20COUNT(*)%20AS%20occurrences,%20MIN(GBIF_TemporalUncertainty(eventDate))%20AS%20minTemporalUncertainty,%20MIN(COALESCE(coordinateUncertaintyInMeters,%201000))%20AS%20minCoordinateUncertaintyInMeters%0AFROM%0A%20%20occurrence%0AWHERE%20%0A%20%20%22year%22%20%3E%202000%0AGROUP%20BY%0A%20%20taxonRank,%20taxonomicStatus,%20kingdom,%20kingdomKey,%20phylum,%20phylumKey,%20%22year%22#about). If so someone should write it
• ...?

[peterdesmet]

Nice!! Here's some feedback on the download page (did not test the SQL editor yet):

• [x] Download type name: Cube rather than SQL cube
• [x] Coordinates column can be ✔ (if selected)
• [x] Title Download cube is fine
• [x] Link About species occurrence cubes (renamed) can refer to https://techdocs.gbif.org/en/data-use/data-cubes
• [x] Labels are good, would use regular case for Randomize points within uncertainty circle
• [x] Currently unclear that occurrence count is always included as a measurement. How to best indicate this?
• [ ] I would update the values and order for Spatial resolution + add sections:

Global
- Military grid reference systems (MGRS)
- Extended quarter degree grid (QDGC)
- ISEA3H grid
Europe
- EEA reference grid

• [ ] The values for Spatial resolution should probably be updated slightly. For one, I think that the spatial resolution for MGRS is in meters.
• [ ] I think comments in SQL are fine to me, not sure if they are retained by the query string though
• [ ] On the SQL editor, I would include the link to the occurrence search in the text, rather than a button:

The easiest way to download and explore data is via the occurrence search user interface. But for complex queries and aggregations, then the SQL editor provides more freedom.

[MortenHofft]

Thanks @peterdesmet

On the SQL editor, I would include the link to the occurrence search in the text, rather than a button:

I agree it is nicer, it is only because it is makes life easier for translators. Having them write markdown with variables have caused issues in the past.

[MortenHofft]

The values for Spatial resolution should probably be updated slightly. For one, I think that the spatial resolution for MGRS is in meters.

Yeah I know those are wrong. I'm waiting for you or Matt to tell me what they should be please. I've changed the MGRS as you specified above

EXTENDED_QUARTER_DEGREE_GRID should be? ISEA3H_GRID should be?

[MortenHofft]

I've added mock help texts to all fields and added 2 mock articles (one for sql download and one for cubes).

Help texts If someone with better english skills and understanding can correct the help texts that would be great. Alternatively I can also try my best, it is just a type of thing that takes me forever. If you believe some fields are self explanatory, then let me know and I can remove the help text.

• [ ] review help texts
• [ ] Style help texts a bit, the amount of help texts make it all a bit bland I find. More spacing would probably help.

Articles For the articles: then someone needs to write them if we still want them. https://www.gbif-uat.org/occurrence-cubes https://www.gbif-uat.org/occurrence/download/sql#about

• [ ] write tool text
• [ ] write cube article

Known API bugs

• [ ] the field naming for order is different between environments
• [ ] downloads do not work in UAT, not sure about other env

Download pages Arriving at a download page is confusing if you come from a cube download format. You configured a cube via a UI, and then arrive at an SQL string. It is a requirement to display this better. One way about it could be to add a new feature to downloads generally.

• [ ] API option to attach a human readable description of a download when doing a download.
• [ ] Auto generate a human readable description for cube downloads.
• [ ] Always show available descriptions on download pages.

That is just one idea. Other ideas for how to make the transition easier for users are welcome

Other

• [ ] review grid resolution translation names
• [ ] Ask coms and data products to provide feedback, refine styling and text.

[timrobertson100]

Thanks. I think the text helps in guiding the user.

I think adding the ability to give it a human readable type / description would be good. Alternatively, we could introduce a cube download format in the API itself, which takes the form parameters but does the SQL conversion behind the backend API. The reason to do that, would be to display to the parameters used on the download page which is shown from the DOI. A user could still "open this in the SQL builder" before submitting to do more complicated queries, but it'd hide SQL completely for anyone who didn't. I don't know what would be the more scalable option.