# 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.

-
Might be relevant: In terms of comparing the planned vs. the actual routes you might want to have a look at Fréchet distance – Dror Atariah Aug 13 '15 at 12:34

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
``````
-

Recommend using two tables. Table A for the planned route and Table B for actual route. Once you have populated both tables you can join the two tables on some sort of identifier and use the STDistance method to determine deviations. There are also many other methods that I think would be useful in this situation.

-
So how would you structure the table? One row per route or one row per lat/long point on the route? – Vladimir Oselsky Sep 10 '13 at 19:19
I think that depends on your specific requirements. It could be done either way. I think a table structured with four columns: routeID, positionID, Lat and Long would do the trick. You could then join on routeID and PositionID and use the STDistance function. – KenWilson Sep 11 '13 at 0:13
Maybe I was not clear enough, the planned route I could get several thousands of points and from actual route I might only get several hundred points for the same route. If i use positionID, I wont be able to compare it to actual route because I'm not sure which point to compare it to, unless I try to compare to all the points and see which one is the closest and than try to figure out if it is on the route between points or off to the side. – Vladimir Oselsky Sep 11 '13 at 11:47
As I said I don't know your specific requirements so I made a guess. I still think two tables should do it and I would check out the link that I sent earlier. There is an intersect method you may find useful as well as many other useful methods. – KenWilson Sep 11 '13 at 12:02