Add portal-specific graphs in Overview section

[johanvonboer]

Which graphs that are being shown in the result overview section should change depending on which portal is selected.

Some preliminary suggestions (from @visead) are:

• Dendro

  • Building types
  • Identified provenance
  • @MattiasSealander more/other?

• Isotopes

  • Chemical elements?
  • Maybe Mats has some ideas? @MGE4

• Ceramics

  • Temper/margin something maybe...? @MattiasSealander

• Insects

  • Eco codes (the bugs ones, not the Koch ones)

[MattiasSealander]

Off the top of my head:

Dendro

• Building types
• Identified tree species
• If possible, summary of the outermost tree ring date (time line? bar graph? histogram?)
• Summary dated/not dated samples (not sure if they want, need input from dendro people)

Do we need to distinguish between dendro for building and archaeology? Archaeological features can be interesting, but of course mainly to archaeologists?

Ceramics Difficult due to how results are recorded. Perhaps mainly metadata.

• Dating (relative age)
• Culture (think it was registered in sample group somewhere)
• Vessel type (if vessel)
• Sample type (possibly through the above)

We would need some way of telling the primary temper, which is difficult at the moment, in order to provide a simple graphic summarizing it. At the moment, this is impossible.

Identified provenance
@MattiasSealander more/other?

[MGE4]

As far as isotopes go i would have to say

• summary of isotope type in samples
• isotope analysis method
• material sampled (i.e bone, ceramic matrix - internal, ceramic matrix - internal, charred deposit - internal, etc.)
• relative_ages

I will wright a pathway to the different parameters ASAP.

[MGE4]

Isotope information in descending priority Count or percentages can be changed depending on what Phill think is cooler. Site distribution shold absolutely be kept as is.

1) Isotope types found in samples:

SELECT SUM(designation) FROM tbl_isotope_types WHERE tbl_isotope_types.isotope_type_id = tbl_isotope_measurments.isotope_type_id;

2) Isotope methods used:

SELECT SUM(method_name) FROM tbl_methods WHERE tbl_methods.method_id = tbl_isotope_measurments.method_id;

3) Materials sampled:

SELECT SUM(location) FROM tbl_sample_lacations WHERE tbl_sample_locations.sample_location_type_id = tbl_sample_locations.sample_location_type_id AND tbl_sample_locations.sample_location_type = 2;

4) Archaeological Time periods:

SELECT SUM(relative_age_name) FROM tbl_relative_ages WHERE tbl_relative_ages.relative_age_id = tbl_relative_date.relative_age_id;

I haven't been able to test this but i hope you can see what im trying to do here.

[johanvonboer]

@MGE4 I tried implementing some of your suggestions Mats but I got some strange results for 3/4 of them. SQL is not my strong point so I might have made some mistake somewhere in my implementations, but this is what I got:

1. SQL: SELECT isotope_types.designation, COUNT(isotope_types.designation) FROM postgrest_api.isotope_types INNER JOIN postgrest_api.isotope_measurements ON isotope_types.isotope_type_id = isotope_measurements.isotope_type_id GROUP BY isotope_types.designation;

RESULT:

COMMENT: It just seems strange that they all have a count of 944, except 2. Will this change in the future to be more diverse?

2. SQL: SELECT DISTINCT(methods.method_name) FROM postgrest_api.methods INNER JOIN postgrest_api.isotope_measurements ON methods.method_id = isotope_measurements.method_id;

RESULT:

COMMENT: Just one result... Seems like it's all under 1 method?

3. SQL: SELECT * FROM postgrest_api.sample_locations WHERE sample_locations.sample_location_type_id = 2;

RESULT: No screenshot here because the result is simply zero - no rows! :(

The last one works quite well, got a list of about 600+ items with some various counts. But the previous 3 has some strange results in different ways. I can't really create meaningful graphs out of those as it is now - which isn't really a problem, I just thought I would raise the issue so that you are aware of the situation - maybe there's a problem in the data somehow?

[MGE4]

Yea, the thing is that we only got a small data set from one lab, so the data that is in SEAD at the moment will be a bit boring in that way, but as soon as we get some new data it will change and look a lot more interesting.

1) This looks good, this will change in the future. Im curious what the 2832 samples with 13c/12c ratio, are... im gonna have a look at that.

2) yes its all under one method but there are multiple methods of doing isotope analysis, so this will also change in the future with more labs sending there data. But what i waned it to show was how many samples there are under the different methods, not just showing the methods.

3) What i wanted to show with the third one was what different materials that were sampled in the data we got, this shold show something like: ceramic matrix - internal, ceramic matrix - internal, charred deposit - internal, etc. and the count of how many of each. If that makes any sence.

Note: Moust of the data will not show anything interesting at this moment, but once new data comes in, this will be more exciting.

[MattiasSealander]

EDIT:

As you asked I have used previous SQL to make views in the postgrest_api schema in the sead_production db. The current views created are:

• dendro_building types Summarizes counts of samples per building type
• dendro_tree_species Counts of samples per tree species (should be only for dendro data as it uses the dendro specific lookup in the WHERE clause)
• dendro_dated_samples_count Updated version of the previous view. Counts of no. of dated and not dated samples in the dendro data. Returned per site.
• ceramics_relative_ages Counts of samples per relative age for the ceramics data
• ceramics_culture Counts of samples per noted culture. Culture is stored as part of a string in the notes field in relative_dates, the SQL uses substring to fetch what follows the "Culture:" part of the notes field and then casts it as uppercase for each word in the following string. Only issue is that the cultures are mainly in Swedish, may need to update in the future.
• ceramics_type_count Counts of no. of sample_group_id per description_types. The view returns counts of ceramic objects per type of object for the ceramics data. Uses a subquery to return only ceramics relevant data since there were issues to get correct counts without it.

[johanvonboer]

@MattiasSealander Great stuff Mattias - thanks! Haven't had time to start looking at it yet but hoping tomorrow.

[johanvonboer]

Btw - If you have access to the sead_production db (not sure if you do?) on seadserv then if you create the views in the postgrest_api-schema there then that would be super awesome for me because any views in there automatically becomes accessible via URLs like: https://browser.sead.se:3000/qse_site_dataset_biblio?site_id=eq.1980

So then I can just call on them directly in the code. But if you don't have access then no worries, giving me SQL here is super helpful as well

[MattiasSealander]

I've added a couple of views, please try and see if they work as intended.

[johanvonboer]

@MattiasSealander Sounds great - but which ones? :)

[MattiasSealander]

In the above edited comment, the names in the bullet list are the names they have in the DB.

[johanvonboer]

I have done some work on this now and new charts for ceramics and dendro are up at the new site: https://browser.sead.se/

At their respective portals: https://browser.sead.se/ceramic https://browser.sead.se/dendro

I modified your views a little @MattiasSealander to include site_id's as well, this makes them a lot less efficient to fetch, but it enabled the graphs to be dynamic and change according to the current filter selection, which I think is cool :) (and maybe useful?)

I couldn't figure out the view for dendro_dated_samples though, so I left that out for now. Perhaps you can make a view for dummies - or fix it so that it works the same way as the others?

I might build a proper server for delivering the data for these charts in the future instead of just relying on PostgREST since that would speed up the loading a lot, but for now it's more efficient in terms of coding time to just do it like this. Even if I do these views would still be used though, so there's no time lost.

Will get around to implementing the isotopes ones as well.

[johanvonboer]

Btw, I added a "qse_" prefix to the names, just so I know which views in that schema are made by us and which are just the default 1:1 mappings to the tables created by PostgREST. So e.g. dendro_tree_species is now called qse_dendro_tree_species, just so you know where to find them.

[MattiasSealander]

Need to discuss how to handle dendro archaeological data. When selecting an archaeological site in the dendro data, building specific graphics will fail. Further discussion in new issue?

[johanvonboer]

Need to discuss how to handle dendro archaeological data. When selecting an archaeological site in the dendro data, building specific graphics will fail. Further discussion in new issue?

Sure! Feel free to create a new issue for it.