Ocean Watch provides indicators at the global and country level. These include high level indicators featured in the header on the introduction page and country profiles, as well as the ranks and indicator values in the "Value of Coastal Ecosystems" section.

The Ocean Watch Data Team can find a catalog of all indicator values from Ocean Watch on Sharepoint Sustainable Ocean Initiative\Documents\Ocean Watch\Development\Data\Indicator Tracking

In general, indicators are generated by querying one or more Ocean Watch datasets using the Carto API. Queries are parameterized so that a corresponding geostore identifiers can be inserted based on the page where the query is called. Queries are stored in the Ocean Watch JSON file. Additional parameters in the JSON specify the format (d3), unit, and description for each indicator. Due to ease of testing, many SQL queries return a formatted number and omit the formatting parameter in the JSON.

{
    "description": "Average proportion of biodiversity hotspots within protected areas",
    "query": "https://wri-rw.carto.com/api/v2/sql?q=SELECT ROUND(value) AS value FROM ocn_024a_rw0_key_biodiversity_area_protection INNER JOIN ow_aliasing_countries AS alias ON alias.alias = geoareaname INNER JOIN gadm36_0 gadm ON alias.iso = gadm.gid_0 WHERE timeperiod = 2020 AND gadm.{{geostore_env}} ILIKE '{{geostore_id}}'",
    "unit": "%"
}

Indicators are queried from underlying Ocean Watch and Resource Watch datasets stored on Carto using SQL SELECT statements. All SQL statements can and should be built and tested in Carto (on the page for a data table, flip switch from "Metadata" to "SQL"). Also note that SQL queries below are written with tabs and newlines for readability, but what gets entered into the JSON needs to be one long, unbroken string, with all items/words separated only by spaces.

There are two components that must be present in the SQL statement: (1) the value (2) the geostore identifier. Once you SQL statement has the components, it is ready to be tested and converted into a query.

The value is the numerical value produced by the query. It should correspond to the description of the indicator decided by the Ocean Watch Team. In the SQL statement, the data should be transformed (if necessary). Once the data have been transformed, the final result should be set to value using AS value in the SELECT statement. If this variable is set to another name, it will not be recognized by the application.

SELECT ROUND(value) AS value FROM ocn_024a_rw0_key_biodiversity_area_protection

The SQL statement must be parameterized so a geostore identifier can be inserted. The application will replace this parameter with the geostore identifier of the corresponding country. Thus the queries must include WHERE {{geostore_env}} = "{{geostore_id}}". geostore_env refers to the environment, either geostore_staging or geostore_prod. geostore_id refers to the unique 32 alphanumeric code for a country in the geostore. Please see the Geostore and Parameterized Queries section for more information.

Once you have written the appropriate SQL statement, you will use the statement to query the Carto API. To do this, simply add https://wri-rw.carto.com/api/v2/sql?q= in front of your query. Remember, the query passed to the API will not be executable without the geostore identifier being inserted, so keep these parameters swapped for actual values.

https://wri-rw.carto.com/api/v2/sql?q=SELECT ROUND(value) AS value FROM ocn_024a_rw0_key_biodiversity_area_protection INNER JOIN ow_aliasing_countries AS alias ON alias.alias = geoareaname INNER JOIN gadm36_0 gadm ON alias.iso = gadm.gid_0 WHERE timeperiod = 2020 AND gadm.geostore_prod ILIKE '9bc50cce7f5b6ebd0452a3b839708ba9'

Copy and paste the complete query url into your browser. If the result is as expected, swap the environment and the geostore identifier back to the parameterized versions

https://wri-rw.carto.com/api/v2/sql?q=SELECT ROUND(value) AS value FROM ocn_024a_rw0_key_biodiversity_area_protection INNER JOIN ow_aliasing_countries AS alias ON alias.alias = geoareaname INNER JOIN gadm36_0 gadm ON alias.iso = gadm.gid_0 WHERE timeperiod = 2020 AND gadm.{{geostore_env}} ILIKE '{{geostore_id}}'

Indicators are stored in the Ocean Watch JSON file. To update or change a indicator you will need to edit this file. You can do this on Github browser by pressing the pencil icon. You can edit the file directly in the browser, or copy the file content and edit in a text editor and then copy it back when you are finished.

In the staging component of the file, navigate to the section for the appropriate indicator. Replace the value of the query key with the query you have created. You may also change the format or unit values if needed. Once you have finished editing the file, submit a pull request. The developers at Vizzuality will review your changes and merge them if there are no issues. After your changes have been merged, check the staging site on a few countries to verify the change performs as expected.

If the changes pass your inspection, you can make the same edits to the JSON in the production section and submit a pull request.

For more information see Updating the Ocean Watch JSON section.

When the dataset underlying an indicator is updated, it is possible that the indicator will need to be updated as well. <b/r> Workflow example: Ocean Biodiversity

1. Update underlying dataset, ocn_024. See the dataset update workflow for more information.
2. Retrieve the existing query from the country-profile page in the production component of the Ocean Watch JSON file (most reliable) or the Indicator Tracking Sheet (maintained manually).

https://wri-rw.carto.com/api/v2/sql?q=SELECT ROUND(value) AS value FROM ocn_024a_rw0_key_biodiversity_area_protection INNER JOIN ow_aliasing_countries AS alias ON alias.alias = geoareaname INNER JOIN gadm36_0 gadm ON alias.iso = gadm.gid_0 WHERE timeperiod = 2020 AND gadm.{{geostore_env}} ILIKE '{{geostore_id}}'

3. Edit the SQL statement to reflect the udpated dataset. Here we change timeperiod = 2021.

SELECT ROUND(value) AS value FROM ocn_024a_rw0_key_biodiversity_area_protection 
INNER JOIN ow_aliasing_countries AS alias ON alias.alias = geoareaname 
INNER JOIN gadm36_0 gadm ON alias.iso = gadm.gid_0 
WHERE timeperiod = 2021 AND gadm.geostore_prod ILIKE '9bc50cce7f5b6ebd0452a3b839708ba9'

Follow the directions above to (4) test the SQL statement, (5) test the query, and (6) update the Ocean Watch json.

To create an up-to-date list of all the indicators on Ocean Watch, their queries, and other values use the get_indicators script.