What is best way to storing geo coordinates related to planned route? Here is my scenario:

Application "A" produces lat and long points that correspond to planned route. Depending on route distance it could have several thousands of points. I know of Geography and Geometry datatypes, even thought they came out with SQL 2008 I have not seen anyone use them yet and not sure what would be good scenario for using it. In addition to points that App "A" generates, I need to store points from App "B" that correspond to actual route. After it is all stored I need to find deviations from the planned route.
This might be an NP-Complete problem, http://en.wikipedia.org/wiki/NP-complete  Compare it to the travelling salesman problem, http://en.wikipedia.org/wiki/Travelling_salesman_problem   I'm not sure if it's NP-Complete since my college texts are in storage, and it's been a while since my complexity classes.

Not to say that we can't just do some simplistic "drift math" using SQL Server spatial data types starting with the table approach suggested by @kenwilsondba.

A more thorough, (if not NP-Complete,) approach would look for where the actual route went back on track amoung other things.

However, we could do the following utilizing SQL Server spatial data types if all we need is simplistic calculation of drift where we could just throw away extra destinations if the actual route goes over, or repeat the last actual end point if the actual route goes under, and disregard any segments where the route went back on track, and assume that the stop ids are actually sequential.

Note that this approach to calculation also penalizes actual routes that only stray in the beginning and never stray for the remainder of the route, and rewards those who stay on track until the final points.

Another caveat is that fields in the table design below are redundant.

The distance calculations are in meters by default.

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


To find the total amount of drift in meters:

select sum(distance_drift) from e_drift_segment where route_id = 1


To show both planed, actual and drift route segments at one time in the SQL Server "Spatial Results" tab:

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