ERDDAP handling of ctd.qartod_flag_type

[JessyBarrette]

Issue

The ERDDAP CTD datasets will soon be directly linked to our database. This will simplify significantly our workflow.

One issue encoutered so far locally is related to the handling of the ctd.qartod_flag_type by ERDDAP which is trying to cast this data type to a integer. The error returned when trying to run a query from ERDDAP with a varaible of this type is the following.

How to we handle this situation?

[JessyBarrette]

@fostermh any idea how to handle that. I'm not 100% sure but it seems like we may have to define a method to cast those ctd.qartod_flag_type to integers?

[fostermh]

ctd.qartod_flag_type is a domain of integers so try casting the 'smallint' part to 'int' If you have an example sql query I can confirm how it should be writen.

[JessyBarrette]

Based on some exchanges with @fostermh, _flag_level_1 columns in the hakai databases are saved as ENUM of strings: Running the following queries gives us the assoicated errors:

SELECT x.* FROM ctd.ctd_data x
where salinity_flag_level_1 ==1

SQL Error [42883]: ERROR: operator does not exist: qartod_flag_type == integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 60

SELECT x.* FROM ctd.ctd_data x
where salinity_flag_level_1 =1

QL Error [42883]: ERROR: operator does not exist: qartod_flag_type = integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 60

Error position: line: 2 pos: 59

This query runs successfully

SELECT x.* FROM ctd.ctd_data x
where salinity_flag_level_1 = '1'

[JessyBarrette]

Changes were applied to the hakaidev database. A local test of a ERDDAP dataset was tested sucessfully as well as a test to the "ctd/process/flags/json" endpoint. All worked sucessfully.

Those changes will be made available in the next release to the hakai database. We can close the issue and reopen if needed.