Health Assessment Assets

[bakamine]

The figure images for the health assessment are currently stored as assets in GCIS (example). They should, instead, point to remote images we now have stored in S3. Bucket:

https://climatehealth2016.s3.amazonaws.com/climatehealth2016/gcis-figures

Naming convention:

Figure-c.n_s.png

where

c is the chapter n is the ordinal _s are sub-image index starting with “a”, “b”, etc

I'm told you should be able to use this as a starting point to script the operation:

https://gist.github.com/abuddenb/2e5400ea20c5f2da79feb5df0ab7e55f

[lomky]

@bakamine could I get access to the AWS account? I have no plans on changing anything in the S3, access will just allow me to test this more robustly.

[amruelama]

Referencing this issue to let you know I had been working on migrating PDF distributed reports from 'Assets' to downloads.globalchange.gov manually (by adding URL). Some of them had Amazon AWS links as well - I didn't migrate those URLs. The most recent CCHHG (2016) report I uploaded is saved in Assets but needs to be in downloads. @bakamine I think it's good to store them directly on downloads.globalchange.gov or Amazon AWS while we upload a file. This would reduce the time on uploading the files to downloads and then adding the URLs later to link them to the appropriate location. Do you think this approach is fine?

[lomky]

@bakamine there seems to be a missing asset in S3. We have a figure in chapter 'front-matter', but in the S3 figures start with Figure-0.1.png being the first figure in 'executive-summary', which the next chapter.

Did we miss adding that figure to the S3, and may I add it manually myself or is there a process?

[lomky]

Actually, there seem to be a few figures missing from the S3.

This is what I find when I walk through the 2016 Health Report Figures right now. bolded are not represented in the S3. I have not yet checked the sub-image files.

Chapter	Generated S3 ID	Figure ID	Assets Link	Note
front-matter	Figure-F.1	understanding-the-exposure-pathway-diagrams	Current Location	Missing, No chapter represented.
executive-summary	Figure-0.1	examples-of-climate-impacts-on-human-health	Current Location
executive-summary	Figure-0.2	es-climate-change-and-health	Current Location
executive-summary	Figure-0.3	es-projected-changes-in-deaths-in-us-cities-by-season	Current Location
executive-summary	Figure-0.4	es-projected-change-in-temperature-ozone-and-ozone-related-premature-deaths-in-2030	Current Location
executive-summary	Figure-0.5	es-estimated-deaths-and-billion-dollar-losses-from-extreme-weather-events-in-the-u-s-2004-2013	Current Location
executive-summary	Figure-0.6	es-changes-in-lyme-disease-case-report-distribution	Current Location
executive-summary	Figure-0.7	es-links-between-climate-change-water-quantity-and-quality-and-human-exposure-to-water-related-illness	Current Location
executive-summary	Figure-0.8	es-farm-to-table	Current Location
executive-summary	Figure-0.9	es-the-impact-of-climate-change-on-physical-mental-and-community-health	Current Location
executive-summary	Figure-0.10	es-determinants-of-vulnerability	Current Location	Just missing?
climate-change-and-human-health	Figure-1.1	major-us-climate-trends	Current Location
climate-change-and-human-health	Figure-1.2	change-in-number-of-extreme-precipitation-events	Current Location
climate-change-and-human-health	Figure-1.3	projected-changes-in-temperature-and-precipitation-by-mid-century	Current Location
climate-change-and-human-health	Figure-1.4	projected-changes-in-hottest-coldest-and-wettest-driest-day-of-the-year	Current Location
climate-change-and-human-health	Figure-1.5	climate-change-and-health	Current Location
climate-change-and-human-health	Figure-1.6	sources-of-uncertainty	Current Location
temperature-related-death-and-illness	Figure-2.1	climate-change-and-health-extreme-heat	Current Location
temperature-related-death-and-illness	Figure-2.2	heat-related-deaths-during-the-1995-chicago-heat-wave	Current Location
temperature-related-death-and-illness	Figure-2.3	projected-changes-in-temperature-related-death-rates	Current Location
temperature-related-death-and-illness	Figure-2.4	projected-changes-in-deaths-in-us-cities-by-season	Current Location
air-quality-impacts	Figure-3.1	climate-change-and-health-outdoor-air-quality	Current Location
air-quality-impacts	Figure-3.2	projected-change-in-temperature-ozone-and-ozone-related-premature-deaths-in-2030	Current Location
air-quality-impacts	Figure-3.3	projected-change-in-ozone-related-premature-deaths	Current Location
air-quality-impacts	Figure-3.4	ragweed-pollen-season-lengthens	Current Location
extreme-events	Figure-4.1	estimated-deaths-and-billion-dollar-losses-from-extreme-weather-events-in-the-us-2004-2013	Current Location
extreme-events	Figure-4.2	climate-change-and-health-flooding	Current Location
extreme-events	Figure-4.3	hurricane-induced-flood-effects-in-eastern-and-central-united-states	Current Location
extreme-events	Figure-4.4	projected-increases-in-very-large-fires	Current Location
vectorborne-diseases	Figure-5.1	climate-change-and-health-lyme-disease	Current Location
vectorborne-diseases	Figure-5.2	changes-in-lyme-disease-case-report-distribution	Current Location
vectorborne-diseases	Figure-5.3	life-cycle-of-blacklegged-ticks-ixodes-scapularis	Current Location
vectorborne-diseases	Figure-5.4	projected-change-in-lyme-disease-onset-week	Current Location
vectorborne-diseases	Figure-5.5	incidence-of-west-nile-neuroinvasive-disease-by-county-in-the-united-states	Current Location
vectorborne-diseases	Figure-5.6	climate-impacts-on-west-nile-virus-transmission	Current Location
water-related-illnesses	Figure-6.1	climate-change-and-health-vibrio	Current Location
water-related-illnesses	Figure-6.2	links-between-climate-change-water-quantity-and-quality-and-human-exposure-to-water-related-illness	Current Location
water-related-illnesses	Figure-6.3	locations-of-livestock-and-projections-of-heavy-precipitation	Current Location
water-related-illnesses	Figure-6.4	projections-of-vibrio-occurrence-and-abundance-in-chesapeake-bay	Current Location
water-related-illnesses	Figure-6.5	changes-in-suitable-coastal-vibrio-habitat-in-alaska	Current Location
water-related-illnesses	Figure-6.6	projected-changes-in-caribbean-gambierdiscus-species	Current Location
water-related-illnesses	Figure-6.7	projections-of-growth-of-alexandrium-in-puget-sound	Current Location
food-safety-nutrition-and-distribution	Figure-7.1	farm-to-table	Current Location	Correctly in S3
food-safety-nutrition-and-distribution	Figure-7.2	climate-change-and-health-salmonella	Current Location
food-safety-nutrition-and-distribution	Figure-7.3	seasonality-of-human-illnesses-associated-with-foodborne-pathogens	Current Location
food-safety-nutrition-and-distribution	Figure-7.4	effects-of-carbon-dioxide-on-protein-and-minerals	Current Location
food-safety-nutrition-and-distribution	Figure-7.5	mississippi-river-level-at-st-louis-missouri	Current Location
food-safety-nutrition-and-distribution	Figure-7.1	low-water-conditions-on-mississippi-river	Current Location	Missing; Repeat ordinal 7.1 in GCIS?
food-safety-nutrition-and-distribution	Figure-7.1	mycotoxin-in-corn	Current Location	Missing; Repeat ordinal 7.1 in GCIS?
mental-health-and-well-being	Figure-8.1	climate-change-and-mental-health	Current Location
mental-health-and-well-being	Figure-8.2	the-impact-of-climate-change-on-physical-mental-and-community-health	Current Location
populations-of-concern	Figure-9.1	determinants-of-vulnerability	Current Location
populations-of-concern	Figure-9.2	intersection-of-social-determinants-of-health-and-vulnerability	Current Location
populations-of-concern	Figure-9.3	vulnerability-to-the-health-impacts-of-climate-change-at-different-lifestages	Current Location
populations-of-concern	Figure-9.4	mapping-social-vulnerability	Current Location
populations-of-concern	Figure-9.5	mapping-communities-vulnerable-to-heat-in-georgia	Current Location
appendix-1--technical-support-document	Figure-A1.1	scenarios-of-future-temperature-rise	Current Location
appendix-1--technical-support-document	Figure-A1.2	example-increasing-spatial-resolution-of-climate-models	Current Location
appendix-1--technical-support-document	Figure-A1.3	sensitivity-analysis-of-differences-in-modeling-approaches	Current Location
appendix-1--technical-support-document	Figure-A1.4	tsd-sources-of-uncertainty	Current Location

[lomky]

SQL to adjust the file locations has been generated. Just waiting to hear from Andrew on the status of the 4 missing Figures. I will then run the SQL on dev to confirm everything changes over correctly before coordinating with @amruelama to make changes on Stage.

List of before & after links by Chapter & file ID

[lomky]

Discovered some new content #444 and a bug fixes #445.

Waiting for the last two figures to be put into the S3

[lomky]

Last images are in the S3. I'm planning on running a test update on dev tomorrow and the real one on stage directly after, if successful.

[lomky]

File locations successfully updated in stage, reported in the watch tab.

It looks like I come through as unknown, either because I was in the DB directly, or under the ops user? Either way, going to file a ticket to address that so it reports the SQL user who made the changes rather than unknown.

@amruelama will spot check the changes. This will be in production after the next content push.

[lomky]

Useful SQL query used to find all images and figures associated with a report, and their file info:

select
    NULL as fig_id,
    image.identifier as img_id,
    figure.chapter_identifier as chptr_id,
    figure.report_identifier as rpt_id,
    file.identifier as file_id,
    file.file as file_uri,
    file.location as file_loc
from figure 
join image_figure_map as if_map
    on figure.identifier = if_map.figure_identifier
join image 
    on if_map.image_identifier = image.identifier
join publication as pub
    on image.identifier = pub.fk -> 'identifier'
join publication_file_map as pf_map
    on pub.id = pf_map.publication_id
join file
    on pf_map.file_identifier = file.identifier
where 
    figure.report_identifier = 'usgcrp-climate-human-health-assessment-2016'
GROUP BY fig_id, img_id, chptr_id, rpt_id, file_id
UNION
select
    figure.identifier as fig_id,
    NULL as img_id,
    figure.chapter_identifier as chptr_id,
    figure.report_identifier as rpt_id,
    file.identifier as file_id,
    file.file as file_uri,
    file.location as file_loc
from figure 
join publication as pub
    on figure.identifier = pub.fk -> 'identifier'
join publication_file_map as pf_map
    on pub.id = pf_map.publication_id
join file
    on pf_map.file_identifier = file.identifier
where 
    figure.report_identifier = 'usgcrp-climate-human-health-assessment-2016'
GROUP BY fig_id, img_id, chptr_id, rpt_id, file_id
ORDER BY chptr_id ASC, img_id DESC, fig_id DESC;

NB: produces one false positive for a figure that was used in two reports. Not to be used for write queries.

[amruelama]

Content push is done and you should be able to see these changes on prod @lomky

[lomky]

@amruelama @bakamine I spot checked a dozen of them files across the chapters, both figure and image linked, and everything looks good to me!

Closing as complete! Hurray!