1

I have done research on Google and read some answers on Stack Overflow. DECIMAL is a good choice for storing latitude and longitude data.

But people have different opinions about the numbers of digit before and after the decimal point.

Latitude range can be max 2 digits (-90 and 90) and longitude range can be max 3 digits (-180 and 180).

The numbers of digit after the decimal point that I get from a Restful API is 12 digits.

Thus the database columns can be as following.

lat DECIMAL(14, 12)
lon DECIMAL(15, 12)

I feel safe to set more max digits as following, in case numbers of digit increase.

lat DECIMAL(17, 13)
lon DECIMAL(17, 13)

This way, both lat and lon can store data up to xxxx.xxxxxxxxxxxxx, thus 4 digits before the decimal point and 13 digits after the decimal point. Does it have import on the performance in MySQL and it is not necessary? I feel stupid to set them so because lat range is -90 and 90 and lon range is -180 and 180, but I feel more safe. But at the same time I concern about the performance.

Or just set them to:

lat DECIMAL(14, 12)
lon DECIMAL(15, 12)

And no need to worry about anything because this is already completely safe for storing latitude and longitude data?

O Connor
  • 4,236
  • 15
  • 50
  • 91
  • I use decimal for store lat, lng (and query) for 50.000 point without problem .. decimal (16,12) .. the single digit more or less in declaration seems not significat for performance – ScaisEdge Mar 20 '19 at 11:01
  • is it always max 12 digits after the decimal point for lat and lon? – O Connor Mar 20 '19 at 11:09
  • Why not a [GIS Point](https://dev.mysql.com/doc/refman/5.7/en/gis-class-point.html)? The performance impact depends a lot on how you use it? So how are you using it? – danblack Mar 20 '19 at 11:10
  • I plan to use it to show Google maps on my website – O Connor Mar 20 '19 at 11:12
  • @OConnor .. what do you mean .. ??? .. 12 is for the significant part i need .. – ScaisEdge Mar 20 '19 at 11:39
  • Possible duplicate of [How many significant digits should I store in my database for a GPS coordinate?](https://stackoverflow.com/questions/1947481/how-many-significant-digits-should-i-store-in-my-database-for-a-gps-coordinate) – Peter O. Mar 20 '19 at 20:29
  • @scaisEdge for the lat and lon value, is it always maximum 12 digits after the decimal point or can be more than 12 digits sometimes? – O Connor Mar 22 '19 at 15:56
  • There is not a rigid limit but a pratical limit .. if 0.000001 (at the quatore) is nore or less 0,11 m 0.000000000001 .. are 0,0000001 m so 12 is for micro m – ScaisEdge Mar 22 '19 at 16:03
  • Also see https://stackoverflow.com/q/7167604/20860 for another similar question about digits of precision. – Bill Karwin Apr 09 '19 at 19:55

1 Answers1

1

Even 12 digits is ludicrous. I recommend one of the following:

DECIMAL(8,6)/(9,6) is sufficient to distinguish two persons standing next to each other. And I suspect GPS is not that precise. Total: 9 bytes for the two columns.

DECIMAL(6,4)/(7,4) is sufficient for houses or businesses, except that there is no vertical component. 7 bytes.

More discussion of lat/lng precision: http://mysql.rjweb.org/doc.php/latlng#representation_choices or https://stackoverflow.com/a/50126941/1766831

As for performance, there is not a lot of difference. Here are the points:

  • More decimal places takes more disk (and ram cache) space, so slows things down slightly.
  • More decimal places means more computation to work with the digits. Again, this is only a slight hit.

Other sizes you mentioned:

lat DECIMAL(17, 13)
lon DECIMAL(17, 13)

takes a total of 16 bytes. Meanwhile, this pair:

lat DECIMAL(14, 12)
lon DECIMAL(15, 12)

is only one byte smaller!

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I take your advise to use `DECIMAL(8,6)/(9,6)` because it comes from `15 or 16 bytes` to `9 bytes` and you said that it is sufficient to distinguish two persons standing next to each other. – O Connor Apr 12 '19 at 13:28