sql - 如何在 SQL Server 中随时间插入纬度/经度？

Question

我有两个表格，经过简化如下所示。

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

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

执行简单的 LEFT JOIN，例如:

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

这会产生所有值，但在 Timestamp 上不完全匹配的情况下存在 NULL。

我的目标是为值表中的每个值获取插值位置。我需要以某种方式在查询中插入值，这就是我陷入困境的地方。

编辑:我发现我可以添加一个 CASE 语句来检查 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

您看过空间工具库吗？ link
他们有一个插值函数，看起来正是您所需要的:

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

我修改了我的第一个示例，现在计算两个最近点的中点，但我认为上面的库是一个更好的选择。要使用我的函数，您需要传入这些 NULL 位置的时间戳，函数返回中点作为几何位置。

这可能对处于类似情况的人有用:

   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