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?

  • I think Postgres with PostGIS is currently the "state-of-the-art" when it comes to spatial features in relational databases. It's definitely way more capable than MySQL (and not only in that area) Sep 2 '20 at 9:47

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

  • My use case is: route-deviation checks (match a route with the planned route and send alerts. How to store route info and based on new gps location data for truck how to calculate deviation? And which advanced spacial database are u referring here. Please help me with pointers. And how to store route data in first place for easy processing?
    – nanosoft
    Aug 1 at 14:28
  • That would be Oracle (spatial processing is a standard out-of-the box feature). Or PostgreSQL with the PostGIS extension. Aug 3 at 7:24

If I remember correctly that was precisely the original commercial implementation case for Kinetica. MapD/ OmniSci also emphasises location processing as a use-case. Both are available as-a-service on their respective cloud offerings, or are available in-house when the required hardware is available.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.