Skip to content

Commit ff34ed5

Browse files
jirhikerjirhiker
authored
Merge pull request #930 from DataIntegrationGroup/fix/chemistry-results-collection-date
fix(chemistry): key results on when the water was collected
2 parents ed392d3 + f7a3cf7 commit ff34ed5

5 files changed

Lines changed: 458 additions & 6 deletions

File tree

‎alembic/versions/3f9c1b7d2a64_chemistry_views_time_on_collection_date.py‎

Lines changed: 243 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,243 @@
1+
"""key the water-chemistry views on when the water was collected
2+
3+
d9e0f1a2b3c4 gave every lab result the time COALESCE(analysis date, collection
4+
date). A lab rarely runs a whole sample on one day -- anions one day, cations
5+
another, trace metals a month later -- so one trip to a well came out as
6+
several points in time. AR-0102 was sampled once in 2019, on Apr 09; its 87
7+
results carried eight different dates between Apr 09 and May 24. Every
8+
consumer of the view inherited that:
9+
10+
* the EDR water_chemistry collection served one sample as eight time steps;
11+
* GET /chemistry/results filtered its start_time/end_time window on analysis
12+
date, so a sample collected in December and analysed in January was filed
13+
under the wrong year, and could be split across two;
14+
* the owner-facing chemistry report counted "8 samples" for 2019 and printed
15+
"arsenic, measured May 24" for water drawn on Apr 09.
16+
17+
This revision flips the precedence: `datetime` is the sample's
18+
CollectionDate, falling back to the analysis date only where no collection
19+
date was recorded (previously the fallback ran the other way). Field
20+
parameters are unchanged -- they only ever had the collection date.
21+
22+
The analysis date is not thrown away. It is carried as its own
23+
`analysis_date` column (NULL for field parameters, which have none), so a
24+
consumer that wants lab turnaround can still read it without it standing in
25+
for the time of sampling.
26+
27+
Everything else -- the four-table union, release gating, id scheme,
28+
indexes, comments -- is carried over from d9e0f1a2b3c4 unchanged. Both views
29+
are materialized, so they are dropped and rebuilt; the rebuild populates
30+
them, and the nightly pg_cron refresh (x2y3z4a5b6c7) discovers them from the
31+
catalog as before.
32+
33+
Revision ID: 3f9c1b7d2a64
34+
Revises: e7f8a9b0c1d2
35+
Create Date: 2026-09-10 11:00:00.000000
36+
"""
37+
38+
import importlib.util
39+
from pathlib import Path
40+
from typing import Sequence, Union
41+
42+
from alembic import op
43+
from sqlalchemy import text
44+
45+
# revision identifiers, used by Alembic.
46+
revision: str = "3f9c1b7d2a64"
47+
down_revision: Union[str, Sequence[str], None] = "e7f8a9b0c1d2"
48+
branch_labels: Union[str, Sequence[str], None] = None
49+
depends_on: Union[str, Sequence[str], None] = None
50+
51+
PUBLIC_VIEW = "ogc_water_chemistry"
52+
INTERNAL_VIEW = "ogc_internal_water_chemistry"
53+
54+
VIEW_COMMENTS = {
55+
PUBLIC_VIEW: (
56+
"Public water-chemistry analyses (by analyte) for EDR, sourced from "
57+
"the legacy NMA chemistry tables. datetime is the sample collection "
58+
"date; analysis_date is when the lab ran the result."
59+
),
60+
INTERNAL_VIEW: (
61+
"All water-chemistry analyses (by analyte) for internal EDR, sourced "
62+
"from the legacy NMA chemistry tables. datetime is the sample "
63+
"collection date; analysis_date is when the lab ran the result."
64+
),
65+
}
66+
67+
# Same latest-location shape the other ogc_* views use (d5e6f7a8b9c0).
68+
_LATEST_LOCATION_CTE = """
69+
SELECT DISTINCT ON (lta.thing_id)
70+
lta.thing_id,
71+
lta.location_id,
72+
lta.effective_start
73+
FROM location_thing_association AS lta
74+
WHERE lta.effective_end IS NULL
75+
ORDER BY lta.thing_id, lta.effective_start DESC
76+
"""
77+
78+
79+
def _result_family(
80+
*,
81+
id_prefix: str,
82+
table: str,
83+
analyte_column: str,
84+
value_column: str,
85+
unit_column: str,
86+
date_column: str | None,
87+
) -> str:
88+
"""One SELECT over a legacy chemistry table, normalized to a common shape.
89+
90+
``date_column`` is the table's analysis date, or None for
91+
NMA_FieldParameters, which has none.
92+
"""
93+
if date_column:
94+
observed_at = f'COALESCE(csi."CollectionDate", r.{date_column})'
95+
analysis_date = f"r.{date_column}::timestamp"
96+
else:
97+
observed_at = 'csi."CollectionDate"'
98+
analysis_date = "NULL::timestamp"
99+
100+
return f"""
101+
SELECT
102+
'{id_prefix}-' || r.id AS id,
103+
csi.id AS sample_id,
104+
csi.thing_id AS thing_id,
105+
csi."PublicRelease" AS sample_public_release,
106+
{observed_at} AS datetime,
107+
{analysis_date} AS analysis_date,
108+
r.{value_column}::double precision AS value,
109+
r.{unit_column} AS unit,
110+
NULLIF(trim({analyte_column}), '') AS parameter_name
111+
FROM "{table}" AS r
112+
JOIN "NMA_Chemistry_SampleInfo" AS csi
113+
ON csi.id = r.chemistry_sample_info_id
114+
WHERE r.{value_column} IS NOT NULL
115+
"""
116+
117+
118+
def _result_families() -> str:
119+
families = [
120+
_result_family(
121+
id_prefix="maj",
122+
table="NMA_MajorChemistry",
123+
analyte_column='COALESCE(r."Analyte", r."Symbol")',
124+
value_column='"SampleValue"',
125+
unit_column='"Units"',
126+
date_column='"AnalysisDate"',
127+
),
128+
_result_family(
129+
id_prefix="min",
130+
table="NMA_MinorTraceChemistry",
131+
analyte_column="COALESCE(r.analyte, r.symbol)",
132+
value_column="sample_value",
133+
unit_column="units",
134+
date_column="analysis_date",
135+
),
136+
_result_family(
137+
id_prefix="rad",
138+
table="NMA_Radionuclides",
139+
analyte_column='COALESCE(r."Analyte", r."Symbol")',
140+
value_column='"SampleValue"',
141+
unit_column='"Units"',
142+
date_column='"AnalysisDate"',
143+
),
144+
_result_family(
145+
id_prefix="fld",
146+
table="NMA_FieldParameters",
147+
analyte_column='r."FieldParameter"',
148+
value_column='"SampleValue"',
149+
unit_column='"Units"',
150+
date_column=None,
151+
),
152+
]
153+
return "\n UNION ALL\n".join(families)
154+
155+
156+
def _create_water_chemistry_view(view_name: str, public_only: bool) -> str:
157+
release_filter = (
158+
"""
159+
AND t.release_status = 'public'
160+
AND results.sample_public_release IS NOT FALSE"""
161+
if public_only
162+
else ""
163+
)
164+
return f"""
165+
CREATE MATERIALIZED VIEW {view_name} AS
166+
WITH latest_location AS (
167+
{_LATEST_LOCATION_CTE}
168+
),
169+
results AS (
170+
{_result_families()}
171+
)
172+
SELECT
173+
results.id AS id,
174+
t.id AS thing_id,
175+
t.name AS station_name,
176+
t.thing_type AS thing_type,
177+
ST_X(l.point) AS longitude,
178+
ST_Y(l.point) AS latitude,
179+
results.datetime AS datetime,
180+
results.analysis_date AS analysis_date,
181+
results.value AS value,
182+
results.unit AS unit,
183+
results.parameter_name AS parameter_name,
184+
results.sample_id AS sample_id,
185+
t.release_status AS release_status
186+
FROM results
187+
JOIN thing AS t ON t.id = results.thing_id
188+
JOIN latest_location AS ll ON ll.thing_id = t.id
189+
JOIN location AS l ON l.id = ll.location_id
190+
WHERE results.parameter_name IS NOT NULL
191+
AND results.datetime IS NOT NULL{release_filter}
192+
"""
193+
194+
195+
def _create_indexes(view_name: str) -> None:
196+
# Unique id index: required for REFRESH MATERIALIZED VIEW CONCURRENTLY.
197+
op.execute(text(f"CREATE UNIQUE INDEX ux_{view_name}_id ON {view_name} (id)"))
198+
op.execute(text(f"CREATE INDEX ix_{view_name}_thing_id ON {view_name} (thing_id)"))
199+
op.execute(text(f"CREATE INDEX ix_{view_name}_datetime ON {view_name} (datetime)"))
200+
op.execute(
201+
text(
202+
f"CREATE INDEX ix_{view_name}_parameter_name "
203+
f"ON {view_name} (parameter_name)"
204+
)
205+
)
206+
207+
208+
def _rebuild(create_view, create_indexes, comments) -> None:
209+
for view_name, public_only in ((PUBLIC_VIEW, True), (INTERNAL_VIEW, False)):
210+
op.execute(text(f"DROP MATERIALIZED VIEW IF EXISTS {view_name}"))
211+
op.execute(text(create_view(view_name, public_only)))
212+
create_indexes(view_name)
213+
op.execute(
214+
text(
215+
f"COMMENT ON MATERIALIZED VIEW {view_name} IS "
216+
f"'{comments[view_name]}'"
217+
)
218+
)
219+
220+
221+
def _load_previous_revision():
222+
# Restore d9e0f1a2b3c4's definitions from that file rather than a copy
223+
# here that could drift from it.
224+
path = Path(__file__).with_name(
225+
"d9e0f1a2b3c4_edr_water_chemistry_from_legacy_tables.py"
226+
)
227+
spec = importlib.util.spec_from_file_location("_edr_chemistry_legacy", path)
228+
module = importlib.util.module_from_spec(spec)
229+
spec.loader.exec_module(module)
230+
return module
231+
232+
233+
def upgrade() -> None:
234+
_rebuild(_create_water_chemistry_view, _create_indexes, VIEW_COMMENTS)
235+
236+
237+
def downgrade() -> None:
238+
previous = _load_previous_revision()
239+
_rebuild(
240+
previous._create_water_chemistry_view,
241+
previous._create_indexes,
242+
previous.VIEW_COMMENTS,
243+
)

‎api/chemisty.py‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,11 @@ def get_water_chemistry_results(
6464
come from the public view, so an unreleased thing or a sample flagged
6565
`PublicRelease = false` is not served here regardless of who is asking.
6666
67+
`observation_datetime` is when the sample was collected, so every result
68+
from one sample shares it; `analysis_date` is when the lab ran that result.
69+
The time window and the default sort are on the collection date: a sample
70+
belongs to the year the water was drawn, not the year the lab got to it.
71+
6772
`start_time` is inclusive and `end_time` exclusive, so a calendar year is
6873
`start_time=YYYY-01-01&end_time=YYYY+1-01-01` with no risk of picking up a
6974
result recorded at midnight on New Year's Day of the following year.

‎db/chemistry_views.py‎

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,8 @@
1616
"""Read-only mappings over the legacy water-chemistry views.
1717
1818
`ogc_water_chemistry` and `ogc_internal_water_chemistry` are materialized views
19-
built in d9e0f1a2b3c4 by unioning the four legacy NMA chemistry tables
19+
built in d9e0f1a2b3c4 (and rekeyed on collection date in 3f9c1b7d2a64) by
20+
unioning the four legacy NMA chemistry tables
2021
(NMA_MajorChemistry, NMA_MinorTraceChemistry, NMA_Radionuclides,
2122
NMA_FieldParameters) into one analyte-per-row shape. They were added for the OGC
2223
EDR mount; these mappings let the REST API serve the same rows, which is where
@@ -55,8 +56,13 @@ class _WaterChemistryResultColumns:
5556
value: Mapped[float | None] = mapped_column("value", Float)
5657
unit: Mapped[str | None] = mapped_column("unit", String)
5758
# Named `datetime` in the view; exposed under the name the observation
58-
# endpoints already use so clients do not need a second field name.
59+
# endpoints already use so clients do not need a second field name. It is
60+
# the sample's collection date (3f9c1b7d2a64), falling back to the
61+
# analysis date only where no collection date was recorded.
5962
observation_datetime: Mapped[datetime] = mapped_column("datetime", DateTime)
63+
# When the lab ran this result. NULL for field parameters, which were read
64+
# at the well and have no analysis of their own.
65+
analysis_date: Mapped[datetime | None] = mapped_column("analysis_date", DateTime)
6066
release_status: Mapped[str | None] = mapped_column("release_status", String)
6167

6268

‎schemas/chemistry.py‎

Lines changed: 14 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,13 @@ class WaterChemistryResultResponse(BaseModel):
3434
parameter_name: str
3535
value: float | None = None
3636
unit: str | None = None
37+
# When the water was collected. Every result from one sample shares it,
38+
# which is what lets a client count samples or group results by visit.
3739
observation_datetime: datetime
40+
# When the lab ran this result -- often days or weeks after collection, and
41+
# different for different analytes in the same sample. None for field
42+
# parameters.
43+
analysis_date: datetime | None = None
3844
# Which legacy table the result came from. A field measurement was read at
3945
# the wellhead and a lab one was not, which is the distinction an
4046
# owner-facing report has to draw -- and the legacy tables are the only
@@ -45,9 +51,9 @@ class WaterChemistryResultResponse(BaseModel):
4551

4652
model_config = ConfigDict(from_attributes=True)
4753

48-
@field_validator("observation_datetime")
54+
@field_validator("observation_datetime", "analysis_date")
4955
@classmethod
50-
def assume_utc(cls, value: datetime) -> datetime:
56+
def assume_utc(cls, value: datetime | None) -> datetime | None:
5157
"""Stamp naive legacy timestamps as UTC.
5258
5359
The legacy tables store collection and analysis dates without a zone --
@@ -57,12 +63,16 @@ def assume_utc(cls, value: datetime) -> datetime:
5763
any server west of Greenwich, and a report for that year would then come
5864
back empty.
5965
"""
66+
if value is None:
67+
return None
6068
if value.tzinfo is None:
6169
return value.replace(tzinfo=timezone.utc)
6270
return value.astimezone(timezone.utc)
6371

64-
@field_serializer("observation_datetime")
65-
def serialize_observation_datetime(self, value: datetime) -> str:
72+
@field_serializer("observation_datetime", "analysis_date")
73+
def serialize_datetime(self, value: datetime | None) -> str | None:
74+
if value is None:
75+
return None
6676
return value.astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")
6777

6878

0 commit comments

Comments
 (0)