Reconciling Canadian and American tidal stations and predictions

python
floatingtrails
kayaking
Author

corey

Published

October 19, 2025

floatingtrails stores tidal predictions and station data in a SQLite database.

from pathlib import Path
import pandas as pd
import sqlite3

conn = sqlite3.connect(Path.home().joinpath("Documents/paddling-charts/tidegen/tides/data/tides.db"))

The database only has 2 tables:

pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table';", conn)
name
0 stations
1 predictions

In this post, I want to fill the stations table with information from Canadian tidal stations but Canadian stations lack some of the attributes that American stations have and require different attributes than do American stations.

To illustrate, here’s a sample of the stations table:

pd.read_sql_query("SELECT * FROM STATIONS LIMIT 1;", conn)
station lat lng id bin name flags refStation timeZoneId timeZoneUTC maxTimeAdj minTimeAdj maxValueAdj minValueAdj risingZeroTimeAdj fallingZeroTimeAdj depth depthType meanFloodDir meanEbbDir
0 1610367 21.87 -160.235 1610367 None Nonopapa, Niihau Island 5 1612340 Pacific/Honolulu UTC-10:00 -16.0 -11.0 0.77 0.77 None None None None None None

Meanwhile, a sample Canadian station “looks” like this:

from datetime import date
import requests

st_lawrence = requests.get("https://api.iwls-sine.azure.cloud-nuage.dfo-mpo.gc.ca/api/v1/stations/5cebf1e13d0f4a073c4bbeb9/metadata").json()

st_lawrence
{'id': '5cebf1e13d0f4a073c4bbeb9',
 'code': '00755',
 'officialName': 'St. Lawrence',
 'alternativeNames': 'Great St. Lawrence',
 'latitude': 46.916789,
 'longitude': -55.39005,
 'type': 'PERMANENT',
 'status': 'OK',
 'operating': True,
 'expectedProductivityPerHour': 60,
 'owner': 'CHS-SHC',
 'stationOwner': {'id': '5ce598ed487b84486892825c',
  'code': 'CHS-SHC',
  'version': 1,
  'acronymEn': 'CHS',
  'acronymFr': 'SHC',
  'nameEn': 'Canadian Hydrographic Service',
  'nameFr': 'Service hydrographique du Canada'},
 'chsRegionCode': 'ATL',
 'provinceCode': 'NL',
 'classCode': 'A',
 'isTidal': True,
 'timeZoneCode': 'Canada/Newfoundland',
 'tideTableId': '5da0907154c1370c6037fcce',
 'isTideTableReferencePort': False,
 'tideTypeCode': 'SD',
 'timeSeries': [{'id': '5cebf1e13d0f4a073c4bbeb5',
   'code': 'wlo',
   'nameEn': 'Water level official value',
   'nameFr': "Niveau d'eau, valeur officielle",
   'phenomenonId': '5ce598df487b84486892821c',
   'owner': 'CHS-SHC'},
  {'id': '5cebf1e13d0f4a073c4bbeb6',
   'code': 'wlp',
   'nameEn': 'Water level predictions',
   'nameFr': "Prédictions de niveaux d'eau",
   'phenomenonId': '5ce598df487b84486892821c',
   'owner': 'CHS-SHC'},
  {'id': '5d9dd7cc33a9f593161c3ffc',
   'code': 'wlp-hilo',
   'nameEn': 'High and Low Tide Predictions',
   'nameFr': 'Prédictions de pleines et basses mers',
   'phenomenonId': '5ce598df487b84486892821c',
   'owner': 'CHS-SHC'},
  {'id': '5cebf1e13d0f4a073c4bbeb7',
   'code': 'wlf',
   'nameEn': 'Water level forecasts generated by the FMS',
   'nameFr': "Prévisions de niveaux d'eau générées par le FMS",
   'phenomenonId': '5ce598df487b84486892821c',
   'owner': 'CHS-SHC'}],
 'datums': [{'code': 'CGVD28', 'offset': -1.32, 'offsetPrecision': 2},
  {'code': 'NAD83_CSRS', 'offset': 1.43, 'offsetPrecision': 2}],
 'heights': [{'heightTypeId': '5cec2eba3d0f4a04cc64d5ce',
   'value': 2.63,
   'valuePrecision': 2},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d1',
   'value': 3.63,
   'valuePrecision': 2,
   'date': '2021-09-10'},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d2',
   'value': 1.39,
   'valuePrecision': 2},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d3',
   'value': 0.33,
   'valuePrecision': 2},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d4',
   'value': 2.17,
   'valuePrecision': 2},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d5',
   'value': 0.72,
   'valuePrecision': 2},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d6',
   'value': -0.4,
   'valuePrecision': 1,
   'date': '2021-09-11'},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d7',
   'value': 2.67,
   'valuePrecision': 2},
  {'heightTypeId': '5cec2eba3d0f4a04cc64d5d8',
   'value': 0.28,
   'valuePrecision': 2},
  {'heightTypeId': '65316afc3cf474827e39a7ef',
   'value': 2.07,
   'valuePrecision': 2},
  {'heightTypeId': '65316afc3cf474827e39a7f0',
   'value': 0.74,
   'valuePrecision': 2}]}

There is a basic intersection here in the fields that identify the stations in their countries’ respective systems and locate the stations, but Canadian stations don’t use time adjustment fields such as maxTimeAdj and minTimeAdj that are required to interpolate tidal predictions for most US tidal stations.

To reconcile these differences, I want to change the schema to co-locate the shared fields and separate the country-specific information. Our starting point is the current state of the database schema:

erDiagram
    STATIONS ||--|{ PREDICTIONS : has
    STATIONS {
        text station
        real lat
        real lng
        text id
        text bin
        text name
        integer flags
        text refStation
        text timeZoneId
        text timeZoneUTC
        real maxTimeAdj
        real minTimeAdj
        real maxValueAdj
        real minValueAdj
        real risingZeroTimeAdj
        real fallingZeroTimeAdj
        real depth
        text depthType
        real meanFloodDir
        real meanEbbDir
    }
    PREDICTIONS {
        text station
        text time
        integer flags
        real depth
        real value
        real magnitude
        real dir
    }

The first change I want to make is to distill the stations table to its essential identifying and geographic fields. My one addition will be to include the [ISO-3] countryCode, such as CAN or USA.

erDiagram
    STATIONS ||--|{ PREDICTIONS : has
    STATIONS {
        text station PK
        text countryCode PK
        real lat
        real lng
        text id
        text bin
        text name
        integer flags
        text refStation
        text timeZoneId
        text timeZoneUTC
        real depth
        text depthType
    }
    PREDICTIONS {
        text station PK
        text countryCode PK
        text time PK
        integer flags
        real depth
        real value
        real magnitude
        real dir
    }

However, this loses some information that’s necessary to calculate high-granularity tidal predictions for substations, so I’ll refactor the time adjustment information to its own, NOAA-specific table.

erDiagram
    STATIONS ||--|{ PREDICTIONS : has
    STATIONS ||--o{ TIME_ADJUSTMENTS : has
    STATIONS {
        text station PK
        text countryCode PK
        real lat
        real lng
        text id
        text bin
        text name
        integer flags
        text timeZoneId
        text timeZoneUTC
        real depth
        text depthType
    }
    PREDICTIONS {
        text station PK
        text countryCode PK
        text time
        integer flags
        real depth
        real value
        real magnitude
        real dir
    }
    TIME_ADJUSTMENTS {
        text station PK
        text countryCode PK
        text refStation
        real maxTimeAdj
        real minTimeAdj
        real maxValueAdj
        real minValueAdj
        real risingZeroTimeAdj
        real fallingZeroTimeAdj
    }

2 things to note:

  1. I’m not totally certain that the rising and falling zero time adjustments actually help calculate tides, so I may drop those later.
  2. TIME_ADJUSTMENTS uses a different node type than PREDICTIONS because not all stations have time adjustments; a round join indicates 0 or more related records, while a crow’s foot indicates 1 or more related records.

My next post will focus on updating the PREDICTIONS table to assimilate Canadian tidal predictions.