Best way of storing GPS coordinates in database

Question

Is there a best practices as to what format GPS coordinates should be stored in the database and then just convert it to the required format when requested?

I have the following:

S33° 56' 51.972" E18° 51' 25.83"

Is it good to store it like this in the database or in another format? I'm just wondering because it contains a degree symbol and minutes and seconds symbols.

Would the above needed to be saved as an NVARCHAR(50) or will VARCHAR(50) be sufficient?

I am using SQL Server but this should be applicable to all forms of databases.

Solution 1

At first, you should to convert your DMS (Degrees, Minutes, Seconds) coordinates to floating point numbers. Here is the algorythm: how to convert between degrees, minutes, seconds to Decimal coordinates.

After that, use two fields of DECIMAL type for storing your latitude and longitude values accordingly. Use something like DECIMAL(15,10). That gives you a total of 15 digits, 10 of which after the decimal point and five before decimal point. This data type is well suited for GPS coordinates and allows you to search records by geocoordinates.

Here is the info about DECIMAL type for SQL Server: http://msdn.microsoft.com/en-us/library/ms187746.aspx

Solution 2

A better approach, in my opinion, is to store a decimal value of GPS coordinates. So, the unit will be the degree, and it requires less fields in your database.

You can see here an example on how to do it: Convert Degrees/Minutes/Seconds to Decimal Coordinates

Solution 3

This only applies to SQL Server. There's a special DataType for that named geography. See http://msdn.microsoft.com/en-us/library/cc280766.aspx