CODEWITHSUNDEEP
sqlsql-serversql-server-2008geometryinterpolation
brianestey
brianestey

Reputation: 8302

How can I interpolate lat/long over time in SQL Server?

I've got two tables, simplified as seen below.

CREATE TABLE value(
    Timestamp DATETIME2,
    Value float NOT NULL,
    PRIMARY KEY(Timestamp)
);

CREATE TABLE location(
    Timestamp DATETIME2,
    Position GEOMETRY NOT NULL,
    PRIMARY KEY(Timestamp)
);

Performing a simple LEFT JOIN such as:

SELECT V.Timestamp, V.Value, L.Position 
FROM   value V
       LEFT JOIN location L ON V.Timestamp = L.Timestamp;

This yields all the values but there are NULL's present where there isn't an exact match on Timestamp.

My goal is to get an interpolated position for every value in the value table. I need to somehow interpolate values in my query and this is where I'm getting stuck.

EDIT: I've since discovered I can add a CASE statement to check for NULL

SELECT 
    V.Timestamp, 
    V.Value, 
    CASE WHEN L.Position IS NULL THEN 
        (<something clever here>) 
    ELSE 
        L.Position 
    END As Position

FROM value V
     LEFT JOIN location L ON V.Timestamp = L.Timestamp;

Upvotes: 4

Views: 1374

Answers (2)

nathan_jr
nathan_jr

Reputation: 9292

Have you looked at the Spatial Tools library? link
They have a interpolation function that looks to be what you need:

SqlGeometry InterpolateBetweenGeom(SqlGeometry start, SqlGeometry end, double distance)

I amended my first example to now calculate the midpoint of the two closest points, but I think the above library is a much better option. To use my function, you pass in the timestamps for those NULL postions and function returns the midpoint as a geom position.

This might be of use for someone in a similar situation:

   if object_id('dbo.fn_CalcMidPointByTime') is not null
        drop function dbo.fn_CalcMidPointByTime;
    go  
    create function [dbo].[fn_CalcMidPointByTime] (@baseTimestamp datetime2)
    returns geometry
    as
    begin
        declare @return geometry,
                @fromPoint geometry,
                @toPoint geometry;

        declare @stage table (i tinyint, position geometry);

        -- stage the high / low points
        insert into @stage (i, position)
            select  row_number() over(order by d.[Timestamp] asc), position.ToString()
            from    (   select top(2) [Timestamp]
                        from    dbo.Location
                        order by abs(datediff(ms, [Timestamp], @baseTimestamp)) asc
                    ) as d([Timestamp])
            join    dbo.Location l on
                    d.[Timestamp] = l.[Timestamp];

        select @fromPoint = position from @stage where i = 1;
        select @toPoint = position from @stage where i = 2;

        -- create linestring from the two points
        declare @ls geometry = geometry::Parse(@fromPoint.STUnion(@toPoint).ToString()).STConvexHull().ToString();

        -- draw a rectangle around the two points, and choose the center
        select @return = @ls.STEnvelope().STCentroid();
        return @return;
    end
    go

Upvotes: 3

Randy
Randy

Reputation: 16677

I think you should change your query to return 3 sets of values on every row:

these should be the largest time and position that is less than the current one,

the current time and position,

and the smallest time and position greater then the current one.

in this way, each row will have enough info to do the math...

Upvotes: 1

Related Questions