views:

2265

answers:

6

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.

+11  A: 

I've been using Decimal(9,6) successfully.

dotjoe
+1: Avoid round-off error weirdness by using a fixed number of decimal places.
S.Lott
For their purposes Wikipedia suggests: "Avoid excessive precision (0.0001° is <11 m, 1′′ is <31 m)."http://en.wikipedia.org/wiki/Wikipedia:Obtaining_geographic_coordinatesTo make my fields extra precise, I've also decided to use six digits right of the decimal mark, and three on the left. I'm glad this answer confirms it was a good decision.
Dragoljub
A: 

We use float, but any flavor of numeric with 6 decimal places should also work.

Keith
A calculation might introduce extra digits which would only be noise. Also the decimal-to-binary transformation will make the final decimal place suspicious and possibly wrong. Fine for some purposes. Lousy for navigation.
S.Lott
S> Lott, I'd upvote your comment a Million times if I could, never use float for lat and long.
HLGEM
I don't think suggesting float deserves a down-rate - you made me second-guess and search elsewhere for an answer: http://stackoverflow.com/questions/551894/whats-the-best-way-to-store-co-ordinates-longitude-latitude-from-google-maps
Keith
+1  A: 

I would use a decimal with the proper precision for your data.

Sam
+1  A: 

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?).

jpj625
A: 

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.

Kasper
+1  A: 

In vanilla Oracle, the feature called LOCATOR (a crippled version of Spatial) requires that the coordinate data be stored using the datatype of NUMBER (no precision). When you try to create Function Based Indexes to support spatial queries it'll gag otherwise.