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