System Design
  • Introduction
  • System Design Process
  • System Design Systematic Approach
  • System Design Topics
  • System Design Interview Tips
  • Object Oriented Design
  • System Design Problems
    • Designing an API Rate Limiter
    • Design News Feed
    • Design Recommendation System
    • Design Photo Sharing App
    • Design Location Based App
    • Design Messenger App
    • Design Twitter
    • Design Uber Lyft
    • Design Surge Pricing
  • Architect's Toolbox
    • Cache Design
    • Database and Cache
    • Pull vs Poll
    • Geo Location
    • Storage Estimation
    • ID Generator
    • Latency Numbers
    • Encoding Decoding Encryption Decryption
  • Systems Design Glossary
    • Consistent Hashing
    • Sharding or Partitioning
    • Database Indexes
    • Proxies
    • Caching
    • Queues
    • SQL vs. NoSQL
    • CAP Theorem
    • Distributed Messaging System
    • Long-Polling vs WebSockets vs Server-Sent Events
    • Producer and Consumer
    • Latency, Bandwidth and Throughput
    • Microservices Architecture
    • RESTful API
    • Concurrent Programming
  • Distributed System Resources
    • Distributed System Notes
  • Reference
Powered by GitBook
On this page
  • How accurately should I store latitude and longitude?
  • What is the ideal data type to use when storing latitude / longitude in a MySQL database?

Was this helpful?

  1. Architect's Toolbox

Geo Location

PreviousPull vs PollNextStorage Estimation

Last updated 5 years ago

Was this helpful?

decimal  degrees    distance
places
-------------------------------  
0        1.0        111 km
1        0.1        11.1 km
2        0.01       1.11 km
3        0.001      111 m
4        0.0001     11.1 m
5        0.00001    1.11 m
6        0.000001   0.111 m
7        0.0000001  1.11 cm
8        0.00000001 1.11 mm

ref :

In SQL database, Latitude and Longitude data types should use DECIMAL instead of FLOAT.

E.g. DECIMAL(9,6) depending on the precision required

###.######

OR

Use MySQL's spatial extensions with GIS.

Another practical way is to use integer or number.

We store latitude/longitude X 1,000,000 in our oracle database as NUMBERS to avoid round off errors with doubles.

Given that latitude/longitude to the 6th decimal place was 10 cm accuracy that was all we needed. Many other databases also store lat/long to the 6th decimal place.

Thus an Int data type could also serve the purpose. Say 168.123456 * 1 000 000 = 168 123 456 is still much less than the signed integer limit which is around 2 billion (2 * 10^9).

How accurately should I store latitude and longitude?
https://en.wikipedia.org/wiki/Decimal_degrees#Precision
What is the ideal data type to use when storing latitude / longitude in a MySQL database?