Implement support for new datasets

[johanvonboer]

Selecting all methods which are linked to datasets:

SELECT
methods.method_id,
methods.method_group_id,
methods.method_name,
methods.method_abbrev_or_alt_name,
method_groups.description as method_group_description
FROM
postgrest_api.methods
INNER JOIN postgrest_api.datasets ON datasets.method_id = methods.method_id
INNER JOIN postgrest_api.method_groups ON method_groups.method_group_id = methods.method_group_id
GROUP BY 
methods.method_id,
methods.method_group_id,
methods.method_name,
methods.method_abbrev_or_alt_name,
method_group_description
ORDER BY method_group_description, method_name

Result:

Among these, the ones that are currently supported are:

• Abundance module
  • Methods: 3, 6, 8, 14, 15, 40, 111
  • Rendered as: Stacked (horizontal) bar chart (default), or table
• Ceramics module
  • Methods: 171, 172
  • Rendered as: Table
• Dendro module
  • Methods: 10
  • Rendered as: Table
• MeasuredValue module
  • All in method group: 2
  • Rendered as: Bar chart (default) or table

All other datasets are currently not supported (for rendering in the site reports / landing pages). Need information on which ones should be implemented (all?) and how they are structured internally in the database as well as how they best should be rendered.

[johanvonboer]

Attacking these in this order:

Also filtered out the ones we already support, and will ignore the top ones related to time periods. @visead I hope that seems like a good strategy.

[johanvonboer]

Query for finding sites with a specific method:

SELECT methods.method_id, methods.method_group_id, methods.method_name, datasets.dataset_id, sites.site_id FROM postgrest_api.methods INNER JOIN postgrest_api.datasets ON datasets.method_id = methods.method_id INNER JOIN postgrest_api.analysis_entities ON analysis_entities.dataset_id = datasets.dataset_id INNER JOIN postgrest_api.physical_samples ON analysis_entities.physical_sample_id = physical_samples.physical_sample_id INNER JOIN postgrest_api.sample_groups ON physical_samples.sample_group_id = sample_groups.sample_group_id INNER JOIN postgrest_api.sites ON sample_groups.site_id = sites.site_id

WHERE datasets.method_id = 151

[visead]

Method group 3 - radiometric dating tbl_geochronology Show as table: tbl_dating_uncertainty.uncertainy, tbl_geochronology:lab_number, age, "+" error_older, "-"error younger, delta_13c probably also dating_material - but need to find this!

[johanvonboer]

Some detective work regarding method group 19 (mostly notes to self):

You can get "relative_dates" from analysis_entities for datasets in this method group, by: select * from postgrest_api.relative_dates where analysis_entity_id=137213 And then you can get relative_ages based on that: select * from postgrest_api.relative_ages where relative_age_id=550

But why are these 2 separate tables? It doesn't seem to be a one-to-many relationship since relative_ages should then contain a relative_date_id...

Update: It seems that it CAN be a one-to-many relationship, but it's just not following the same pattern as the rest of db, since relative_dates is mostly just a linking table, it could link 1 AE to multiple instances of relative_age.

[visead]

Yes, tbl_relative_ages is essentially a massive lookup table for definitions of dating periods, year ranges or single years. As dating periods can be used to date any number of samples, tbl_relative_dates maps one age (=period etc.) to many samples (via analysis entities)

[johanvonboer]

I see, so it's actually that it's linking one instance of relative_age to multiple instances of AE, makes sense, thanks!

[johanvonboer]

Remaining unsupported datasets:

And the SQL for this:

SELECT methods.method_id, methods.method_group_id, methods.method_name, methods.method_abbrev_or_alt_name, method_groups.description AS method_group_description, COUNT(datasets.dataset_id) AS datasets FROM postgrest_api.methods INNER JOIN postgrest_api.datasets ON datasets.method_id = methods.method_id INNER JOIN postgrest_api.method_groups ON method_groups.method_group_id = methods.method_group_id WHERE methods.method_group_id NOT IN (2, 3, 19, 20) AND methods.method_id NOT IN (3, 6, 8, 14, 15, 40, 111, 171, 172, 10) GROUP BY methods.method_id, methods.method_group_id, methods.method_name, methods.method_abbrev_or_alt_name, method_group_description ORDER BY datasets DESC