CODEWITHSUNDEEP
mysqltypesgeocoding
RayHT
RayHT

Reputation: 41

Latitude and Longitude datatype and storage format

I see two formats being used like: 41.45063 N and (N 40°48'27.34").

I assume: If i use first format then i need only 2 colums: Decimal, Direction
If i use the second format then i need 4 colums: Degree, Minute, Second, Direction

Which format is used to store in the database and how to convert from 1 format to the other. Also are we storing only the cordinates or even the direction like N,S,E,W?

I am using cordinates for local places in MySQL.

Upvotes: 0

Views: 2004

Answers (3)

Joey
Joey

Reputation: 354546

You can convert the second format into the first trivially. There are sixty minutes in a degree, and sixty seconds in a minute. In your example of 40°48′27.34″ it's 40 + (48 ∙ 60 + 27.34) / 3600 ≈ 40.807594°.

So you need two columns, one for longitude, one for latitude (you know, you can just encode direction into the sign of the number).

Upvotes: 4

Andrew - OpenGeoCode
Andrew - OpenGeoCode

Reputation: 2287

RayHT

The formats you are are referring to are known as decimal format and DMS (Degrees, Minutes, Seconds). There is a straight forward function for converting back-n-forth. Just google 'Degrees to Decimal conversion' for the language your using (PHP, Javascript, etc).

When storing decimal format, the sign of the value indicates direction. For latitude, positive values are North and negative are South. For Longitude, positive values are East and negative values are West.

Here's a link to an online converter and a description of the basics that you might find useful in undertstanding how this works.

http://www.csgnetwork.com/gpscoordconv.html

Andrew Part of the "OpenGeoCode.Org" Team

Upvotes: 0

Marc B
Marc B

Reputation: 360702

40°48'27.34 is simply a floating point number that's been formatted for easier human consumption. Unless your data table had fields for "degrees", "minutes", and "seconds", you can't store that "human" format directly. However, storing it as a single floating point value of 41.45063 lets you reformat it into the human-friendly format on-demand.

There's no need for a direction field. You can assume that positive numbers indicate North and West, and negative numbers indicate South/East.

Upvotes: 1

Related Questions