lbesnard
commented
3 years ago Current status of the data
list of unique platform codes and vessel names
select distinct(platform_code,vessel_name) from soop_co2.soop_co2_trajectory_map
+-------------------------------+
| row |
|-------------------------------|
| (FHZI,L'Astrolabe) |
| (VLHJ,"Southern Surveyor") |
| (VLMJ,"RV Investigator") |
| (VNAA,"Aurora Australis") |
| (VNAA,"SRV Aurora Australis") |
| (ZMFR,"RV Tangaroa") |
+-------------------------------+WMS view def
select * from pg_catalog.pg_views where viewname like 'soop_co2_trajectory_map'
+--------------+-------------------------+-------------+--------------------------------------------------------------------------+
| schemaname | viewname | viewowner | definition |
|--------------+-------------------------+-------------+--------------------------------------------------------------------------|
| soop_co2 | soop_co2_trajectory_map | soop_co2 | SELECT a.file_id, |
| | | | a.url, |
| | | | a.size, |
| | | | timezone('UTC'::text, a.time_coverage_start) AS time_coverage_start, |
| | | | timezone('UTC'::text, a.time_coverage_end) AS time_coverage_end, |
| | | | a.colour, |
| | | | a.geom, |
| | | | b.platform_code, |
| | | | b.cruise_id, |
| | | | b.vessel_name |
| | | | FROM (soop_co2.visualisation_wms a |
| | | | JOIN soop_co2.deployments b ON ((a.file_id = b.file_id))); |
+--------------+-------------------------+-------------+--------------------------------------------------------------------------+WFS view def
select definition from pg_catalog.pg_views where viewname like 'soop_co2_trajectory_data'
+-----------------------------------------------------------------------------------------------+
| definition |
|-----------------------------------------------------------------------------------------------|
| SELECT measurements.file_id, |
| visualisation_wms.vessel_name, |
| visualisation_wms.voyage_number AS cruise_id, |
| measurements.measurement, |
| timezone('UTC'::text, measurements."TIME") AS "TIME", |
| measurements."TIME_quality_control", |
| measurements."LATITUDE", |
| measurements."LATITUDE_quality_control", |
| measurements."LONGITUDE", |
| measurements."LONGITUDE_quality_control", |
| measurements."DfCO2", |
| measurements."DfCO2_quality_control", |
| measurements."fCO2ATM_UATM_INTERPOLATED", |
| measurements."fCO2ATM_UATM_INTERPOLATED_quality_control", |
| measurements."fCO2SW_UATM", |
| measurements."fCO2SW_UATM_quality_control", |
| measurements."H2OFLOW", |
| measurements."H2OFLOW_quality_control", |
| measurements."LICORflow", |
| measurements."LICORflow_quality_control", |
| measurements."Press_ATM", |
| measurements."Press_ATM_quality_control", |
| measurements."Press_Equil", |
| measurements."Press_Equil_quality_control", |
| measurements."PSAL", |
| measurements."PSAL_quality_control", |
| measurements."TYPE", |
| measurements."SUBFLAG", |
| measurements."TEMP_1", |
| measurements."TEMP_1_quality_control", |
| measurements."TEMP_2", |
| measurements."TEMP_2_quality_control", |
| measurements."WDIR", |
| measurements."WDIR_quality_control", |
| measurements."WSPD", |
| measurements."WSPD_quality_control", |
| measurements."xCO2ATM_PPM", |
| measurements."xCO2ATM_PPM_quality_control", |
| measurements."xCO2ATM_PPM_INTERPOLATED", |
| measurements."xCO2ATM_PPM_INTERPOLATED_quality_control", |
| measurements."xCO2EQ_PPM", |
| measurements."xCO2EQ_PPM_quality_control", |
| measurements.geom |
| FROM (soop_co2.measurements |
| JOIN soop_co2.visualisation_wms ON ((visualisation_wms.file_id = measurements.file_id))) |
| ORDER BY measurements."TIME"; |
+-----------------------------------------------------------------------------------------------+proposed _map and _data view def
WMS
SELECT a.file_id,
a.url,
a.size,
timezone('UTC'::text, a.time_coverage_start) AS time_coverage_start,
timezone('UTC'::text, a.time_coverage_end) AS time_coverage_end,
a.colour,
a.geom,
b.platform_code,
b.cruise_id,
CASE
WHEN b.platform_code = 'VNAA'
THEN 'Aurora Australis'
ELSE b.vessel_name
END AS vessel_name
FROM (soop_co2.visualisation_wms a
JOIN soop_co2.deployments b ON ((a.file_id = b.file_id)));WFS
SELECT measurements.file_id,
CASE
WHEN b.platform_code = 'VNAA'
THEN 'Aurora Australis'
ELSE b.vessel_name
END AS vessel_name ,
visualisation_wms.voyage_number AS cruise_id,
measurements.measurement,
timezone('UTC'::text, measurements."TIME") AS "TIME",
measurements."TIME_quality_control",
measurements."LATITUDE",
measurements."LATITUDE_quality_control",
measurements."LONGITUDE",
measurements."LONGITUDE_quality_control",
measurements."DfCO2",
measurements."DfCO2_quality_control",
measurements."fCO2ATM_UATM_INTERPOLATED",
measurements."fCO2ATM_UATM_INTERPOLATED_quality_control",
measurements."fCO2SW_UATM",
measurements."fCO2SW_UATM_quality_control",
measurements."H2OFLOW",
measurements."H2OFLOW_quality_control",
measurements."LICORflow",
measurements."LICORflow_quality_control",
measurements."Press_ATM",
measurements."Press_ATM_quality_control",
measurements."Press_Equil",
measurements."Press_Equil_quality_control",
measurements."PSAL",
measurements."PSAL_quality_control",
measurements."TYPE",
measurements."SUBFLAG",
measurements."TEMP_1",
measurements."TEMP_1_quality_control",
measurements."TEMP_2",
measurements."TEMP_2_quality_control",
measurements."WDIR",
measurements."WDIR_quality_control",
measurements."WSPD",
measurements."WSPD_quality_control",
measurements."xCO2ATM_PPM",
measurements."xCO2ATM_PPM_quality_control",
measurements."xCO2ATM_PPM_INTERPOLATED",
measurements."xCO2ATM_PPM_INTERPOLATED_quality_control",
measurements."xCO2EQ_PPM",
measurements."xCO2EQ_PPM_quality_control",
measurements.geom
FROM (soop_co2.measurements
JOIN soop_co2.visualisation_wms ON ((visualisation_wms.file_id = measurements.file_id))
LEFT JOIN soop_co2.deployments b ON ((measurements.file_id = b.file_id)))
ORDER BY measurements."TIME" ;I'll put a PR with these changes on the harvester. Can you confirm you're happy with this @bpasquer ?
bpasquer
Naming of the Aurora Australis has changed to SRV Aurora Australis in 2016 creating a second entry in the vessel name filter. To avoid duplicate entries in the vessel_name filter, vessel naming should be enforced in the harvester.