Correct 2 PQ category names and associated major category

[LizziOh]

During the initial PQ category name update (https://github.com/aodn/NRMN/pull/272) there were 2 mapping errors.

• Current category "Coral" should be renamed to "Dead coral" and its Major Category should also be "Dead coral". This applies to resolutions: RLS Basic, RLS Basic (Aggregate Obs), RLS Basic (Transparency), and RLS/CATAMI. (refer to row 469 in the attached to show mapping error)

• Current category "Tabular Acropora Coral" should be changed to "Tabular Coral (dead)" and its major category changed to "Dead coral". This applies to resolution RLS/CATAMI. (refer to row 546 in the attached)

The categories are discreet by name and should not have been merged during the original changes made so can be fixed.

RLS.PQ.category.match.xlsx

[bpasquer]

@LizziOh it appears that there is already a category Tabular Coral (dead) under the major category "Dead Coral" which currently applies to resolution RLS/Lord Howe Island. So should we just apply the existing one to RLS/CATAMI and delete "Tabular Acropora Coral"? Which also means bulk updating all PQs referring to this category/resolution pair (124 records)

[LizziOh]

@bpasquer essentially yes, we should apply the existing Tabular Coral (dead) (category_id; 910) to 124 observations that have the resolution_id: 6. and the category "Tabular Acropora Coral". (so there will be a new cat_res_id generated for this new combination of category 910 and resolution 8). The only strange thing is that I couldn't find the category_id for "Tabular Acropora Coral" to be changed. The closest seemed to be 907 which reads "Tabular Acropora Coral (live)" - but that wording doesn't exist in the M13 endpoint? is that because recent changes haven't been updated in the the M13 endpoint yet?

[bpasquer]

The category_id for "Tabular Acropora Coral" is 321.

[LizziOh]

Ah, thanks Bene, my mistake - they appeared ordered alphabetically but aren't in actual fact. I think the change should be fine then.

[bpasquer]

OK thanks for looking at that. I'll prepare the SQL update script in the next couple of weeks,

[bpasquer]

@LizziOh

essentially yes, we should apply the existing Tabular Coral (dead) (category_id; 910) to 124 observations that have the resolution_id: 6. and the category "Tabular Acropora Coral". (so there will be a new cat_res_id generated for this new combination of category 910 and resolution 8).

First, just to confirm in the above quote you meant resolution 7 (RLS/Lord Howe Island) not resolution 8 (RLS/Squidle) right?

Secondly, currently in TABLE pq_cat_res_ref we have:

cat_res_id	resolution_id	category_id
812	6	321

There is 2 approaches to fix this :

• update category_id to 910 (Tabular Coral (dead)) in the row displayed above or
• add a new cat_res_id and delete the above one. Both options also require to delete category_id 321 in pq_category_ref. My preference goes to option 1 as this is the only change that needs to be done(aside from the cahnge in pq_category_ref. The second approach requires to update the tables pq_cat_res and the pq_score.

What do you think @LizziOh ?

[LizziOh]

essentially yes, we should apply the existing Tabular Coral (dead) (category_id; 910) to 124 observations that have the resolution_id: 6. and the category "Tabular Acropora Coral". (so there will be a new cat_res_id generated for this new combination of category 910 and resolution 8).

First, just to confirm in the above quote you meant resolution 7 (RLS/Lord Howe Island) not resolution 8 (RLS/Squidle) right?

Sorry I think I meant resolution 6 (RLS/CATAMI), not 8. but as you say in the following, we only need to update 1 value - the category id, from 321 to 910 in the table you showed.

Secondly, currently in TABLE pq_cat_res_ref we have:

cat_res_id resolution_id category_id 812 6 321 There is 2 approaches to fix this :

• update category_id to 910 (Tabular Coral (dead)) in the row displayed above or
• add a new cat_res_id and delete the above one. Both options also require to delete category_id 321 in pq_category_ref. My preference goes to option 1 as this is the only change that needs to be done(aside from the cahnge in pq_category_ref. The second approach requires to update the tables pq_cat_res and the pq_score.

What do you think @LizziOh ?

Yes, let's go with option 1! cat_res_id: 812 , resolution_id: 6 , category_id: 910