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

Question

I have two tables simplified as shown 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) ); 

Running a simple LEFT JOIN, for example:

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

This gives all values, but there is NULL if there is no exact match on the Timestamp .

My goal is to get the interpolated position for each value in the value table. I need to somehow interpolate the values ​​in my query, and it is here that I am stuck.

EDIT: I have since discovered that 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; 

Answer 1

Have you looked at the spatial tool library? They have an interpolation function that looks the way you need it:

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

I correct my first example to calculate the middle of the two closest points, but I think the above library is a much better option. To use my function, you pass timestamps for these NULL positions, and the function returns the midpoint as the position of the geometry.

This might be useful 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 

Answer 2

I think you should modify your query to return 3 sets of values ​​in each row:

it should be the biggest time and position that is smaller than the current one,

current time and position

and the smallest time and position is greater than the current one.

this way, each line will have enough information to do the math ...