Request for all term and taxon lists associated with app

[sacrevert]

@johnvanbreda @DavidRoy @LaurenceJonesES At some point could you also upload a zip file here of all the taxon and term lists (including IDs) associated with this project? I will be doing some work on data visualisation at some point, and it would be useful to have all the terms in the correct format for downstream filtering, rather than creating fake data to see how they emerge in the downloads. Cheers!

[sacrevert]

@johnvanbreda @DavidRoy Hi both, would it be possible to upload these?

[kazlauskis]

Hi Oli, this might be useful.

All the species warehouse IDs:

https://github.com/NERC-CEH/dunes-app/blob/master/src/common/data/species/index.json

Habitat IDs:

https://github.com/NERC-CEH/dunes-app/blob/master/src/common/data/habitats.json

@johnvanbreda do you know if there is some report in the warehouse available to download all survey terms and matching ids?

@sacrevert All the terms and ids are available in the app code. Try opening the config.js file inside survey folder, you should be able to find most of the terms and ids in there. Here is the link: https://github.com/NERC-CEH/dunes-app/tree/master/src/Survey

[johnvanbreda]

@sacrevert here's a query that extract's all the attributes, plus where the attributes are lookups, the list of terms. I've uploaded the results as an attachment (CSV format, just rename the extension:

select su.title as survey, 'sample' as entity, a.id as attribute_id, a.caption, a.data_type, string_agg(t.id::text || ': ' || t.term, CHR(10) || CHR(13) || ' ' ORDER BY t.sort_order, t.term) as terms
from surveys su
join sample_attributes_websites aw on aw.restrict_to_survey_id=su.id and aw.deleted=false
join sample_attributes a on a.id=aw.sample_attribute_id and a.deleted=false
left join cache_termlists_terms t on t.termlist_id=a.termlist_id and a.data_type='L'
where su.website_id=125
group by su.title, a.id, a.caption, a.data_type
UNION
select su.title as survey, 'occurrence', a.id as attribute_id, a.caption, a.data_type, string_agg(t.id::text || ': ' || t.term, CHR(13) || CHR(10) || ' ' ORDER BY t.sort_order, t.term) as terms
from surveys su
join occurrence_attributes_websites aw on aw.restrict_to_survey_id=su.id and aw.deleted=false
join occurrence_attributes a on a.id=aw.occurrence_attribute_id and a.deleted=false
left join cache_termlists_terms t on t.termlist_id=a.termlist_id and a.data_type='L'
where su.website_id=125
group by su.title, a.id, a.caption, a.data_type
order by survey, entity DESC, caption

Dune attributes.txt

Have you got what you need for the taxa from Karolis' post above?

[sacrevert]

That's all great. Many thanks @johnvanbreda and @kazlauskis !