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

Database Administration用户

提问于 2013-09-10 04:13:09

回答 2查看 10.4K关注 0票数 3

下面是我的场景：

应用程序"A“产生与计划路线相对应的lat和长点。视路线距离而定，它可能有几千个点。我知道地理和几何数据类型，即使他们推出了SQL 2008，我还没有见过有人使用它们，也不确定使用它的好场景是什么。除了应用程序"A“生成的点外，我还需要存储与实际路由对应的App "B”中的点。在全部存储完毕后，我需要找到偏离计划路线的地方。

sql-server

sql-server-2012

spatial

回答 2

Database Administration用户

回答已采纳

发布于 2013-09-11 08:53:43

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

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

一种更彻底的方法(如果不是NP-完全的话)将寻找实际路线返回到其他事情上的位置。

但是，如果我们所需要的只是简单的漂移计算，如果我们只需要简单地计算漂移，那么我们就可以使用SQL Server空间数据类型进行以下操作:如果实际路由经过，我们就可以丢弃额外的目的地；或者，如果实际路由处于运行状态，则重复最后一个实际端点，忽略路由返回的任何段，并假定停止if实际上是顺序的。

请注意，这种计算方法还惩罚了实际的路线，这些路线在开始时只偏离，在剩下的路线上从不偏离，并且奖励那些在最后点之前保持在轨道上的人。

另一个警告是，下面表设计中的字段是多余的。

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

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

要同时在Server "Spatial结果“选项卡中显示计划的、实际的和漂移的路由段，请执行以下操作：

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