Avatar of arthurh88
arthurh88
 asked on

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

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)
Microsoft SQL Server

Avatar of undefined
Last Comment
arthurh88

8/22/2022 - Mon
Dr. Klahn

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.
arthurh88

ASKER
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. Klahn

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Nitin Sontakke

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
arthurh88

ASKER
that helps!