主页 / dba / 问题 / 49594
Accepted
Vladimir Oselsky
Asked: 2013-09-11 04:13:09 +0800 CST

存储与计划路线相关的地理坐标的最佳方法是什么？

  • 772

这是我的场景：

应用程序“A”产生与计划路线相对应的纬度和经度点。根据路线距离，它可能有数千个点。我知道 Geography 和 Geometry 数据类型，甚至认为它们与 SQL 2008 一起出现我还没有看到有人使用它们，并且不确定使用它的好场景是什么。除了应用程序“A”生成的点之外，我还需要存储应用程序“B”中与实际路线相对应的点。全部存储后，我需要找到与计划路线的偏差。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    这可能是一个 NP 完全问题，http ://en.wikipedia.org/wiki/NP-complete 将其与旅行推销员问题进行比较，http ://en.wikipedia.org/wiki/Travelling_salesman_problem 我不确定如果它是 NP-Complete，因为我的大学课本在存储中，而且自从我的复杂性课程以来已经有一段时间了。

    并不是说我们不能从@kenwilsondba 建议的表方法开始使用 SQL Server 空间数据类型来做一些简单的“漂移数学”。

    更彻底的（如果不是 NP-Complete 的话）方法将寻找实际路线在其他事情中回到正轨的位置。

    但是，如果我们需要的只是简单的漂移计算，我们可以使用 SQL Server 空间数据类型执行以下操作，并忽略路线回到正轨的任何路段，并假设停靠点 ID 实际上是连续的。

    请注意，这种计算方法还惩罚仅在开始时偏离并且在路线的其余部分从未偏离的实际路线，并奖励那些一直保持在轨道上直到最后点的人。

    另一个需要注意的是，下表设计中的字段是多余的。

    默认情况下，距离计算以米为单位。

    CREATE TABLE a_planned_point (
    route_id            INT, 
    stop_id             INT,
    lat                 DECIMAL(10,7), 
    long                DECIMAL(10,7), 
    pointspatialdata    GEOGRAPHY,    
    city                VARCHAR(20),
    state               CHAR(2) )

CREATE TABLE b_actual_point (
    route_id            INT, 
    stop_id             INT,
    lat                 DECIMAL(10,7), 
    long                DECIMAL(10,7), 
    pointspatialdata    GEOGRAPHY,    
    city                VARCHAR(20),
    state               CHAR(2) )

CREATE TABLE c_planned_segment (
    route_id            INT, 
    start_id            INT,
    stop_id             INT,
    lat_planned_stop    DECIMAL(10,7), 
    long_planned_stop   DECIMAL(10,7), 
    city_planned        VARCHAR(20),
    state_planned       CHAR(2), 
    segmentspatialdata  GEOGRAPHY)

CREATE TABLE d_actual_segment (
    route_id            INT, 
    start_id            INT,
    stop_id             INT,
    lat_actual_stop     DECIMAL(10,7), 
    long_actual_stop    DECIMAL(10,7), 
    city_actual         VARCHAR(20),
    state_actual        CHAR(2), 
    segmentspatialdata  GEOGRAPHY)

CREATE TABLE e_drift_segment (
    route_id            INT, 
    planned_stop_id     INT,
    actual_stop_id      INT,
    lat_planned_stop    DECIMAL(10,7), 
    long_planned_stop   DECIMAL(10,7), 
    city_planned        VARCHAR(20),
    state_planned       CHAR(2), 
    lat_actual_stop     DECIMAL(10,7), 
    long_actual_stop    DECIMAL(10,7), 
    city_actual         VARCHAR(20),
    state_actual        CHAR(2), 
    distance_drift      FLOAT,
    segmentspatialdata  GEOGRAPHY)

INSERT INTO a_planned_point (route_id, stop_id, lat, long, pointspatialdata, city, state) VALUES
    (1, 0, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'), 
    (1, 1, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ'),
    (1, 2, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'), 
    (1, 3, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'), 
    (1, 4, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY'), 
    (1, 5, 42.37, -71.03,  CAST('POINT(-71.03 42.37)' AS GEOGRAPHY),  'Boston', 'MA')

INSERT INTO b_actual_point (route_id, stop_id, lat, long, pointspatialdata, city, state) VALUES
    (1, 0, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'), 
    (1, 1, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'), 
    (1, 2, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ'),
    (1, 3, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'), 
    (1, 4, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY')



INSERT INTO c_planned_segment (
    route_id,
    start_id, 
    stop_id,
    lat_planned_stop,
    long_planned_stop,
    city_planned,  
    state_planned,
    segmentspatialdata
    )
    SELECT 
        x.route_id, 
        x.stop_id, 
        y.stop_id, 
        y.lat, 
        y.long, 
        y.city, 
        y.state, 
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+ 
            CAST(y.long AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM 
        a_planned_point x
    LEFT OUTER JOIN 
        a_planned_point y
    ON     
        y.stop_id = x.stop_id + 1 
    WHERE 
        y.stop_id IS NOT NULL   
        and 
        x.route_id = 1
    ORDER BY x.stop_id

INSERT INTO d_actual_segment (
    route_id, 
    start_id, 
    stop_id, 
    lat_actual_stop,
    long_actual_stop,  
    city_actual, 
    state_actual,
    segmentspatialdata 
    )
    SELECT 
        x.route_id, 
        x.stop_id, 
        y.stop_id, 
        y.lat, 
        y.long, 
        y.city, 
        y.state, 
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+ 
            CAST(y.long AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM 
        b_actual_point x
    LEFT OUTER JOIN 
        b_actual_point y
    ON     
        y.stop_id = x.stop_id + 1 
    WHERE 
        y.stop_id IS NOT NULL   
        and 
        x.route_id = 1
    ORDER BY x.stop_id



INSERT INTO e_drift_segment (
    route_id, 
    planned_stop_id, 
    actual_stop_id, 
    lat_planned_stop, 
    long_planned_stop, 
    city_planned, 
    state_planned,
    lat_actual_stop,
    long_actual_stop,
    city_actual,
    state_actual,
    distance_drift,
    segmentspatialdata 
    )
    SELECT 
        x.route_id, 
        x.stop_id, 
        y.stop_id, 
        x.lat,
        x.long, 
        x.city, 
        x.state,
        y.lat, 
        y.long, 
        y.city, 
        y.state,
        x.pointspatialdata.STDistance(y.pointspatialdata), 
        CAST('LINESTRING(' + CAST(x.long AS VARCHAR) +' '+ CAST(x.lat AS VARCHAR) +', '+ 
            CAST(y.long + 0.0000001 AS VARCHAR) +' '+ CAST(y.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
    FROM 
        a_planned_point x
    LEFT OUTER JOIN 
        b_actual_point y
    ON     
        y.stop_id = x.stop_id 
    WHERE 
        x.stop_id IS NOT NULL   
        and 
        y.stop_id IS NOT NULL   
        and
        x.route_id = 1
    ORDER BY x.stop_id

    要找到以米为单位的总漂移量：

    select sum(distance_drift) from e_drift_segment where route_id = 1

    要在 SQL Server 的“空间结果”选项卡中同时显示计划的、实际的和漂移的路段：

    select segmentspatialdata from c_planned_segment where route_id = 1
union all
select segmentspatialdata from d_actual_segment where route_id = 1
union all
select segmentspatialdata from e_drift_segment where route_id = 1
    • 4

  2. 推荐使用两张桌子。表 A 为计划路线，表 B 为实际路线。一旦您填充了两个表，您就可以在某种标识符上连接这两个表，并使用 STDistance 方法来确定偏差。在这种情况下，我认为还有许多其他方法很有用。

    请参阅http://technet.microsoft.com/en-us/library/cc280766.aspx

    • 1

相关问题