5

What is the best way to store GPS coordinates (track) on server (MySQL or Oracle or maybe in any file)? How is it implemented it the GoogleMaps, for example? I want to save and compare tracks for same parts.

P.S. I have all necessary data.

Konstantin.Efimenko
  • 1,242
  • 1
  • 14
  • 38

1 Answers1

9

If I were you I'd use a TRACK and a POINT table.

The TRACK table would contain a row for each distinct track

  TRACK_ID      int not null  (PK)
  NAME          varchar(40)
  DESCRIPTION   varchar(255)
  other identifying information

The POINT table would contain multiple rows per track, one for each point in the track

  POINT_ID     int not null  (PK)
  TRACK_ID     int not null  (FK to TRACK)
  LAT          float  degrees  .. positive means north
  LONG         float  degrees  .. positive means east, negative means west
  ALT          float (elevation if you have it)
  TS           timestamp of point

A couple of notes about this. Keep the rows of the POINT table short; you will have lots of them and you want to be able to crunch them quickly. Also, don't succumb to the temptation of using double instead of float; the float data format has plenty of precision for a typical point (unless you are a land surveyor and know about stuff like universal transverse mercator projections).

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Don't use float for Lat/Long. You don't need floating points for simple decimal numbers and you will just end up with strange rounding errors like 56.56837000000000001 (even if that's not what you inserted into the database). Just use decimal (9,6) if your database supports it. – NickG Feb 23 '16 at 15:57
  • @NickG, I respectfully disagree. You're right about numbers like money, but GPS data is inherently measurement - based, that is, subject to its own kind of epsilon precision limitations. https://en.wikipedia.org/wiki/Machine_epsilon FLOAT has the appropriate precision for commercial GPS. – O. Jones Feb 23 '16 at 23:51
  • 1
    Then we'll have to agree to disagree :) The largest number you will never need is 180 and the the smallest (given the innate inaccuracy of GPS) is about 7 decimal places, so floating point representation is unnecessary. See: http://stackoverflow.com/questions/11041818/rails-3-float-or-decimal-for-gps-coordinates – NickG Feb 24 '16 at 08:51