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,
sto

StackExchange Database Administrators Q#49594, answer score: 4