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
