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.
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.
Well, you asked how to store Latitude/Longitude and my answer is: Don't, you might consider using the WGS 84 ( in Europe ETRS 89 ) as it is the standard for Geo references.
But that detail aside I used a User Defined Type in the days before SQL 2008 finally include geo support.
I think it depends on the operations you'll be needing to do most frequently.
If you need the full value as a decimal number, then use decimal with appropriate precision and scale. Float is way beyond your needs, I believe.
If you'll be converting to/from degºmin'sec"fraction notation often, I'd consider storing each value as an integer type (smallint, tinyint, tinyint, smallint?).
