Menu
When storing latitude or longitude data in an ANSI SQL compliant database, what datatype would be most appropriate? Should float be used, or decimal, or ...?
I'm aware that Oracle, MySql, and SQL Server have added some special datatypes specifically for handling geo data, but I'm interested in how you would store the information in a "plain vanilla" SQL database.
792
This is one of the most fundamental ways of storing geocoordinate data. Latitude & longitude values can be represented & stored in a SQL database using decimal points (Decimal degrees) rather than degrees (or Degrees Minutes Seconds).
p. precision you should use DECIMAL . Latitudes range from -90 to +90 (degrees), so DECIMAL(10,8) is ok for that, but longitudes range from -180 to +180 (degrees) so you need DECIMAL(11,8) .
Longitude has the range [-180;180] so it should be stored in DECIMAL(9,6) .
The most precise available option is DOUBLE . The most common seen type used is DECIMAL(8,6)/(9,6) .
For latitudes use: Decimal(8,6), and longitudes use: Decimal(9,6)
If you're not used to precision and scale parameters, here's a format string visual:
Latitude and Longitude ##.###### and ###.######
To 6 decimal places should get you to around ~10cm of accuracy on a coordinate.
146
We use float, but any flavor of numeric with 6 decimal places should also work.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
