SQL: Best data type for lat & long coordinates? Float vs Decimal?

arthurh88
arthurh88 used Ask the Experts™
on
Im importing a new zip code table into SQL and was curious if there is a best way to store the lat and long?  I was inclined to store as decimal with 10 precision  (i.e. 43.901848      -101.246893)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dr. KlahnPrincipal Software Engineer

Commented:
Well, no matter how it's stored, part of it must be stored as floating.

In degrees-minutes-seconds (DMS) format, hours and minutes can be integer, but seconds must be floating point.  Storage = 2*I + 1*FP

In decimal format, one floating point will do.  Storage = 1*FP

So storing in DMS format will take more storage than storing in floating point.  And doing "nearness" calculations is easier in decimal floating point.  If you don't see a need (or only an infrequent need) to display in DMS format, then decimal is the way to go.

Author

Commented:
Hi Dr. Klahn.  Do I need to do anything particular in my SQL table design other than "decimal(10, 7)" ?  I'm not sure how I specify 1*FP for that decimal field.
Dr. KlahnPrincipal Software Engineer

Commented:
Sorry to say that I'm not an expert in SQL.  I weighed in on this question strictly from a data structures viewpoint.  But I'm sure if you leave the question up, someone will be able to help you with that issue.
Starting SQL Server 2012 spatial data types are now available within database. However they are implemented as in .NET CLR.

Details are here.

Author

Commented:
that helps!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial