database-designdatabase-recommendationgeometryspatial
We are developing an app in that UI need to show a path where a truck driver drives the truck.
Driver mobile will continuously send (example every 10 seconds) their live location.
Which database is more suitable for storing location path (polylines).
Our team has experience of Mongo (with Geodata), Redis, MySQL
Is there any best database to store this kind of Geodata?
Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:
UOM Conversion
UOM From UOM To Cal Step Operator Factor Constant
Kilograms Pounds 1 * 2.2
Celsius Fahrenheit 1 * 1.8
Celsius Fahrenheit 2 + 32
This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.
That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.
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
Best Answer
Much depends on what you mean by "storing a route path"
(a) Do you mean storing the geographical shape of the road segments traversed by the truck ? That assumes you do a form of map-matching, i.e. correlate the GPS locations sent by the trucks with your road database.
(b) Do you mean constructing a track from the GPS points you receive from each truck ?
(c) Do you mean just storing the GPS coordinates in a table ?
You can use any database to store geographical information, whether that is individual points or complex shapes ... For points, a couple of X and Y numeric columns is all you need. For lines, you can use any of the available standard textual representations: OGC WKT, GML, GeoJson, even KML. They are typically stored in CLOB structures due to the potential size and complexity (essentially the number of vertices).
The real question is about the processing you do to construct those geographical shapes. Points (case c) are trivial. Constructing a line from points as a GPS track (case b) is more of an effort: building say a GeoJSON representation is easy: it just means adding a new point to the JSON string. What requires work is the calculations to throw out bad locations - too far off track to be anything but errors.
Doing the map matching (case a) is yet more work as it requires locating the proper road segment for each GPS location - considering the approximation of the coordinates and the history of preceding road segments. And of course you need a complete database of all road segments in the geographical area your application covers. That requires a fairly advanced database that has proper spatial types with indexing and spatial predicates. Few databases have this: Oracle has that as native capabilities. PostgreSQL has it via the PostGIS extension.
Then there is the question about what the application is all about: is it just to show a route on a map for each truck ? Do you need to replay a route ? View previous day routes ? Do route-deviation checks (match a route with the planned route and send alerts) ? Predict arrival times ? Compare the pace of a truck with the planned route (is in early ? is it delayed). All those again need a database with advanced spatial processing capabilities. Unless you do all this processing in some external engine and only use the database for storing the results ...