3

I have environmental sensors collecting some raw data from environmental stations. In addition to the sensors, there is a physical gauge measuring water height (staff gauge).

I'm trying to set up the staff gauge calculation, so that it will take the most recent offset reading from Table 2 and apply it to any new measurements coming in, without changing the previously calculated values.

Table 1

station_time
Air_temp_celcius
Rainfall_mm
pressure_mH20
water_temp_celcius
sensor_depth_mH20
staff_gauge_height_m (calculated, sensor_depth_mH20 + Offset from Table2)

Table 2

Observed_time
Offset_m
comments

My thinking was to have a function to pull the offset

SELECT TOP 1(Offset)
FROM Table2
ORDER BY Observed_time DESC

Is there a way to have it only update new values coming into Table 1 without updating previous values when a new offset is entered? New offsets are entered into Table 2 very sporadically as field crews visit the site.

Additional information from comments:

There is no direct relationship between the two tables, other than the latest offset is used to calculate the staff gauge value. It should always use the most recent value.

I have specific stations and specific station offset tables. I generalized the table structure: table1_stream1 and table2_stream1_offset. As for the offset storage on the table, redundancy. Always was taught you normalize as much as possible and don't duplicate data in multiple tables if you can avoid it. I no longer work on coding and databases as much as I'd like, but some stuff sticks.

Are you saying if you have a station called "Antartica" and a station called "Africa" then you have the following tables: Antartica_stream1, Antartica_stream1_offset, Africa_stream1, and Africa_stream1_offset, etc.?

Correct. Given stations are polling hourly and not all locations carry the exact same sensor package (or sensors are in different order). Also individual stations may go offline due to technical or environmental issues.

  • 1
    I deal with the same environmental data at work. From your description i think you're talking about river levels and compensating for shifts in river bed after you've done a gauging. The issue that I can see is that changes happen over time. And so the correction is added over time. It's known as ramping. In order to do that you need a start and end date. I'm struggling to picture the design but i think you could derive a ramping amount to apply per day/week/month and adjust on the fly. – Sir Swears-a-lot Aug 26 '15 at 7:00
  • @Peter You are correct. It's also some adjustment due to temperature as well. Using AquiStar PT-12 sensors. We minimized ramping by embedding a stand pipe in the river bed, but sediment does still build up. Our ramp up mostly occurs on high water events in fall/winter. – Calvinthesneak Aug 26 '15 at 16:23
  • Are you building your own db time series app from scratch? There are a number of commercial packages that can do this for you. Acquarius, Hilltop etc. – Sir Swears-a-lot Aug 26 '15 at 20:59
3

Given the additional information that Aaron Bertrand didn't have access to when he posted his answer I would suggest a different tack.

Instead of putting logic/business significance in table names I would have general table names and put the logic/business significance in attributes/data in the tables. This should make it easier to expand functionality, and maintain your data. Furthermore you can extract useful information much easier.

The following is a rough schema that captures the direction I recommend and will probably need to be adapted to your exact needs:

CREATE TABLE dbo.WeatherStation
(
    WeatherStationId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50) NOT NULL -- This is where you put the name of the station instead of in the table.
)

CREATE TABLE dbo.SensorReading
(
    SensorReadingId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    WeatherStationId INT NOT NULL FOREIGN KEY REFERENCES dbo.WeatherStation(WeatherStationId), -- Match a reading to the station
    ReportedTime DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(), -- When the time was reported to the database
    <Other columns like temp, pressure, etc.>
)

CREATE TABLE dbo.SensorOffset
(
    SensorOffsetId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    WeatherStationId INT NOT NULL FOREIGN KEY REFERENCES dbo.WeatherStation(WeatherStationId), -- Match a reading to the station like you do now
    Offset DECIMAL(20, 10) NOT NULL -- Adjust precision/datatype as needed
    Comment NVARCHAR(500) NULL,
    Created DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME() -- This would need to be unique per weather station
)

Now you can add a new station without duplicating table schema, you can easily compare data from related stations, etc.

Even if you didn't want/can't change your schema, I would recommend putting the calculation in a view. That is more obvious in my opinion than a trigger, and it would be easier to trouble shoot for me. Something like the following should work with my schema above:

;WITH CurrentOffset_CTE AS
(
    SELECT
        WeatherStationId
        , MAX(Created) AS Created
    FROM dbo.SensorOffset
    GROUP BY
        WeatherStationId
)
SELECT
    WS.Name
    , SR.ReportedTime
    , CASE WHEN SR.<reading> IS NOT NULL THEN SR.<reading> + SO.Offset ELSE NULL END AS <reading>
    , <repeat same pattern as above for the various readings>
FROM dbo.WeatherStation WS
    INNER JOIN dbo.SensorReading SR ON SR.WeatherStationId = WS.WeatherStationId
    INNER JOIN CurrentOffset_CTE CO ON CO.WeatherStationId = WS.WeatherStationId
    INNER JOIN dbo.SensorOffset SO ON SO.WeatherStationId = CO.WeatherStationId AND SO.Created = CO.Created

This will be easy to troubleshoot, hard to miss, and obvious to future maintainers. You could modify this code to work for your current schema too, but would have to duplicate it for each station. In that case I would still recommend this approach for the above stated reasons.

  • The view solution is probably what I will use, simply because merging millions of rows into one table doesn't seem like a good performance option. It is however the best practices. I have picked yours as the correct answer as it is the most comprehensive solution, but both solutions do answer my original question. – Calvinthesneak Aug 26 '15 at 16:32
2

Probably easiest would be an INSTEAD OF INSERT trigger.

CREATE TRIGGER dbo.FixTable1
  ON dbo.Table1
  INSTEAD OF INSERT 
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @offset INT; -- decimal? Something else?

  SELECT TOP (1) @offset = offset_m
    FROM dbo.Table2
    -- WHERE ...?
    ORDER BY [timestamp] DESC; -- terrible column name btw

  INSERT dbo.Table1(other cols, staff_gauge_height_m)
    SELECT other cols, sensor_depth_mH20 + @offset
     FROM inserted;
END
GO

It gets a bit more complicated if there are station-specific offsets (your question was maybe simplified; if so, don't do that).

Also, why not just store the current offset at the time of insert? You can always get col1 + col2 at query time without storing the calculation (just seems a bit redundant to embed one column's constant value into another's).

  • You're right about column name. changed it to "Observed time" – Calvinthesneak Aug 25 '15 at 18:10
  • 1
    @Calvin you're not eliminating redundancy, you're just trading one piece of redundant information for another. If you really want to normalize, you'll only store each offset once, in Table2, but you'll have a unique identifier (say, an IDENTITY column) that can be used to indicate in Table1 which offset should be used to calculate the sum for this row. Now the only redundant thing is the surrogate identifier (which unfortunately is needed to link the rows). – Aaron Bertrand Aug 25 '15 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.