We help IT Professionals succeed at work.

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

arthurh88
arthurh88 asked
on
597 Views
Last Modified: 2018-04-06
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

Dr. KlahnPrincipal Software Engineer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
that helps!