ThingSampleObservation

[jirhiker]

https://www.figma.com/board/1ksfxywBgyD8a81nDujNLw/ThingSampleObservation?node-id=0-1&p=f&t=c0y5grAphmrWhiz4-0

Questions

1. Can an observation exist without an associated sample?
2. What is the sample when taking a groundwater level measurement?
3. Option 1 has the potential for an observation to be linked to Thing A directly but Thing B via its associated sample
4. Is the water level form a multi step process where the user 1) selects a locations 2) selects or adds a new sample 3) adds a new observation?

[ksmuczynski]

My thoughts:

1. When I think about a sample, I think about a visit to a monitoring station. Given this context, you would not be able to take a water level measurement without visiting a well.
2. Not sure I fully understand the question, but my take is that the sample for a water level measurement would include the following:

• Type/matrix of sample (groundwater)
• Date/time sample was collected/observed
• Sample method (steel tape)
• Sampler name
• other thing associated with the visit

The observation table would just store info about the specific observation/analysis

• parameter name (depth to water, i.e. water level)
• value
• detection limits
• lab metadata
• • lab name (ACZ, TestAmerica, Field (if parameter is a field collected parameter like DTW, pH, etc))

4. Yes, I think that workflow makes sense.

[jirhiker]

regarding sample method; there is a difference between sample method and observation method, in many cases, but seemingly not for groundwater level measurements. Is "steel tape" used for both sample_method and observation_method. Or is sample_method="manual" and observation.sensor = Sensor("steel tape")

[ksmuczynski]

My first thought would be to leave observation_method blank, or maybe populate it with something like Field. sample_method indicates how the sample was collected (steel tape,, e-probe, bailer, etc); analytic_method indicates how the observation/parameter was analyzed, typically something performed by a lab. For example, the EPA has a number of standard analytical methods for analyzing groundwater (EPA 300.0, EPA 200.8, etc). I'm not opposed to using the sample_method value in the analytic_method field for water level measurements, though.

Do we need a sensor field in the Observation table? What information is being captured here that might be different than what's captured in the analytic_method field? Also, if sensor is essentially equipment, could this be captured by having a sensor_id field in the Sample table that relates back to the primary Sensor table?

[jirhiker]

Do we need a sensor field in the Observation table? What information is being captured here that might be different than what's captured in the analytic_method field? Also, if sensor is essentially equipment, could this be captured by having a sensor_id field in the Sample table that relates back to the primary Sensor table?

A single sample can have many different types of observations performed on it. Therefore each Observation must have an associated "sensor_id", e.g. the equipment used to make the measurement. The equipment used and the analytic_method are distinct as a single piece of equipment can perform many different types of analyses.

[jirhiker]

What are your thoughts regarding continuous measurements? Is a sample created for every observation made by a transducer?

[ksmuczynski]

This is a good question and something I need to think about more. I'm having trouble remembering how I worked with high-volume data in the past...In any case, it's something I can look into, how best to store/manage high-volume data.

[ksmuczynski]

I fed AI a simple version of our current schema and asked it to evaluate its strengths and weaknesses, mostly as it relates to storing high-volume data. Here is its initial response:

Strengths 👍

• Well-Normalized: The separation of Locations, Stations (Things), Samples, and Analyses (Observations) is a classic and effective design. It reduces data redundancy and improves data integrity. For example, station details are stored once, not repeated for every sample.
• Flexible: The Analyses (Observations) table uses an Entity-Attribute-Value (EAV) model (parameter_name, value), which is extremely flexible. It allows you to store any number of different analytical results for a single sample without changing the database structure.
• Clear Hierarchy: The relationships are logical and easy to understand: a location has stations (things), stations (things) have sampling events, and sampling events have results.

Faults 👎

• Inefficient for Time-Series Data: The schema is designed for infrequent, discrete events (e.g., a technician collects a water bottle once a month). It would perform poorly with a transducer that generates a reading every 15 minutes.
• • High Overhead: Storing each transducer reading would require creating a new row in Samples and another in Analyses (Observations). This transactional overhead would be massive and slow down data ingestion to unacceptable levels.
• • Storage Bloat: The EAV model in the Analyses (Observations) table would store the text Depth to Water for every single data point, creating enormous storage waste compared to a dedicated column.
• • Slow Queries: Querying a year's worth of 15-minute data would involve joining across several tables and filtering through billions of rows in the Analyses (Observations) table, which would be exceptionally slow.

Incorporating High-Volume Data

The existing schema is insufficient. Do not force high-volume sensor data into your current Samples and Analyses (Observations) tables. The performance will be poor, and it will pollute tables that are well-suited for their original purpose.

The best approach is to add new tables specifically designed to handle this data type, creating a parallel structure that integrates with your existing Stations (Things) table.

Recommended Schema Additions

Here is how you can augment your existing schema. You would add three new tables that work alongside your current ones.

1. Add an Instruments Table

This table tracks the specific equipment used for measurements, which is crucial for data quality.

    InstrumentID INT PRIMARY KEY AUTO_INCREMENT,
    Model VARCHAR(100),
    SerialNumber VARCHAR(100) UNIQUE,
    InstrumentType VARCHAR(100) NOT NULL -- e.g., 'Pressure Transducer', 'Manual e-Tape'
);

2. Add a Deployments Table

This is the critical link. It records which instrument was active at which station during a specific period.

    DeploymentID INT PRIMARY KEY AUTO_INCREMENT,
    StationID INT NOT NULL,         -- Foreign Key to your existing Stations table
    InstrumentID INT NOT NULL,      -- Foreign Key to the new Instruments table
    DeploymentDate TIMESTAMP NOT NULL,
    RemovalDate TIMESTAMP,          -- NULL if currently deployed
    FOREIGN KEY (StationID) REFERENCES Stations(StationID),
    FOREIGN KEY (InstrumentID) REFERENCES Instruments(InstrumentID)
);

3. Add a TimeSeries_Data Table

This is the new, lean table for all high-frequency data. It is optimized for fast writes and time-based queries.

    TimestampUTC TIMESTAMP NOT NULL,
    DeploymentID INT NOT NULL,
    Value DECIMAL(10, 4) NOT NULL, -- The measured value (e.g., depth, pressure)
    QualityFlag VARCHAR(50) DEFAULT 'Verified',
    PRIMARY KEY (DeploymentID, TimestampUTC), -- Composite primary key ensures uniqueness
    FOREIGN KEY (DeploymentID) REFERENCES Deployments(DeploymentID)
);

Recommendation: For maximum performance, you should make TimeSeries_Data a hypertable using a PostgreSQL extension like TimescaleDB. This partitions the data by time automatically and provides specialized functions for time-series analysis, making ingestion and querying extremely fast.

Final Integrated Workflow

Discrete Lab Data: Continue using your existing workflow: Locations → Stations (Things)→ Samples → Analyses (Observations). This is what it's designed for.

High-Volume Sensor Data: Use the new workflow: Stations (Things) → Deployments → TimeSeries_Data. This path is optimized for speed and efficiency.

This hybrid approach preserves the integrity of your existing schema while adding a highly performant, scalable solution for your sensor data needs.

[jacob-a-brown]

3. Option 1 has the potential for an observation to be linked to Thing A directly but Thing B via its associated sample

I like Option 1 more than I like Option 2. I think that it would be more difficult to retrieve observations of a specific parameter from a thing if we proceed with Option 2. This is because a sample may have multiple observations (e.g. a water sample may be analyzed for specific conductivity, nitrate, pH, and arsenic). If we proceed with Option 2 and make an endpoint to get observations we would have to

1. select all samples associated with a thing
2. select all observations associated with those samples
3. filter those samples by parameter

While this could be done with subqueries, those can quickly become slow and inefficient.

By proceeding with Option 1 we could just select all observations with the correct thing_id and parameter in one fell swoop, making the queries much simpler.

[jacob-a-brown]

That's at least my intuition without implementing either schema.

[jirhiker]

select(Observation).join(Sample).where(Sample.thing_id=10, Observation.observed_property='pH')
or
select(Observation).join(Sample).where(Sample.thing_id=10, Observation.observed_property.in_('pH', 'TDS'))

[jacob-a-brown]

Well that's much simpler... I suppose if every observation has an associated sample, like how @ksmuczynski has outlined (with groundwater), then that ought to work well.