[sql-server] What's the best way to store co-ordinates (longitude/latitude, from Google Maps) in SQL Server?

I'm designing a table in SQL Server 2008 that will store a list of users and a Google Maps co-ordinate (longitude & latitude).

Will I need two fields, or can it be done with 1?

What's the best (or most common) data-type to use for storing this kind of data?

Fair Warning! Before taking the advice to use the GEOGRAPHY type, make sure you are not planning on using Linq or Entity Framework to access the data because it's not supported (as of November 2010) and you will be sad!

Update Jul 2017

For those reading this answer now, it is obsolete as it refers to backdated technology stack. See comments for more details.

What you want to do is store the Latitude and Longitude as the new SQL2008 Spatial type -> GEOGRAPHY.

Here's a screen shot of a table, which I have.

alt text http://img20.imageshack.us/img20/6839/zipcodetable.png

In this table, we have two fields that store geography data.

• Boundary: this is the polygon that is the zip code boundary
• CentrePoint: this is the Latitude / Longitude point that represents the visual middle point of this polygon.

The main reason why you want to save it to the database as a GEOGRAPHY type is so you can then leverage all the SPATIAL methods off it -> eg. Point in Poly, Distance between two points, etc.

BTW, we also use Google's Maps API to retrieve lat/long data and store that in our Sql 2008 DB -- so this method does work.

NOTE: This is a recent answer based on recent SQL server, .NET stack updates

latitute and longitude from google Maps should be stored as Point(note capital P) data in SQL server under geography data type.

Assuming your current data is stored in a table Sample as varchar under columns lat and lon, below query will help you convert to geography

alter table Sample add latlong geography
go
update Sample set latlong= geography::Point(lat,lon,4326)
go

PS: Next time when you do a select on this table with geography data, apart from Results and Messages tab, you will also get Spatial results tab like below for visualization

SQL Server has support for spatial related information. You can see more at http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx.

Alternativly you can store the information as two basic fields, usually a float is the standard data type reported by most devices and is accurate enough for within an inch or two - more than adequate for Google Maps.

If you are just going to substitute it into a URL I suppose one field would do - so you can form a URL like

http://maps.google.co.uk/maps?q=12.345678,12.345678&z=6

but as it is two pieces of data I would store them in separate fields

I hate to be a contrarian to those who said "here is a new type, let's use it". The new SQL Server 2008 spatial types have some pros to it - namely efficiency, however you can't blindly say always use that type. It really depends on some bigger picture issues.

As an example, integration. This type has an equivilent type in .Net - but what about interop? What about supporting or extending older versions of .Net? What about exposing this type across the service layer to other platforms? What about normalization of data - maybe you are interested in lat or long as standalone pieces of information. Perhaps you've already written complex business logic to handle long/lat.

I'm not saying that you shouldn't use the spatial type - in many cases you should. I'm just saying you should ask some more critical questions before going down that path. For me to answer your question most accurately I would need to know more about your specific situation.

Storing long/lat separately or in a spatial type are both viable solutions, and one may be preferable to the other depending on your own circumstances.

The way I do it: I store the latitude and longitude and then I have a third column which is a automatic derived geography type of the 1st two columns. The table looks like this:

CREATE TABLE [dbo].[Geopoint]
(
    [GeopointId] BIGINT NOT NULL PRIMARY KEY IDENTITY, 
    [Latitude] float NOT NULL, 
    [Longitude] float NOT NULL, 
    [ts] ROWVERSION NOT NULL, 
    [GeographyPoint]  AS ([geography]::STGeomFromText(((('POINT('+CONVERT([varchar](20),[Longitude]))+' ')+CONVERT([varchar](20),[Latitude]))+')',(4326))) 
)

This gives you the flexibility of spatial queries on the geoPoint column and you can also retrieve the latitude and longitude values as you need them for display or extracting for csv purposes.

I don't know the answer for SQL Server but...

In MySQL save it as FLOAT( 10, 6 )

This is the official recommendation from the Google developer documentation.

CREATE TABLE `coords` (
  `lat` FLOAT( 10, 6 ) NOT NULL ,
  `lng` FLOAT( 10, 6 ) NOT NULL ,
) ENGINE = MYISAM ;

If you are using Entity Framework 5 < you can use DbGeography. Example from MSDN:

public class University  
{ 
    public int UniversityID { get; set; } 
    public string Name { get; set; } 
    public DbGeography Location { get; set; } 
}

public partial class UniversityContext : DbContext 
{ 
    public DbSet<University> Universities { get; set; } 
}

using (var context = new UniversityContext ()) 
{ 
    context.Universities.Add(new University() 
        { 
            Name = "Graphic Design Institute", 
            Location = DbGeography.FromText("POINT(-122.336106 47.605049)"), 
        }); 

    context. Universities.Add(new University() 
        { 
            Name = "School of Fine Art", 
            Location = DbGeography.FromText("POINT(-122.335197 47.646711)"), 
        }); 

    context.SaveChanges(); 

    var myLocation = DbGeography.FromText("POINT(-122.296623 47.640405)"); 

    var university = (from u in context.Universities 
                        orderby u.Location.Distance(myLocation) 
                        select u).FirstOrDefault(); 

    Console.WriteLine( 
        "The closest University to you is: {0}.", 
        university.Name); 
}

https://msdn.microsoft.com/en-us/library/hh859721(v=vs.113).aspx

Something I struggled with then I started using DbGeography was the coordinateSystemId. See the answer below for an excellent explanation and source for the code below.

public class GeoHelper
{
    public const int SridGoogleMaps = 4326;
    public const int SridCustomMap = 3857;

    public static DbGeography FromLatLng(double lat, double lng)
    {
        return DbGeography.PointFromText(
            "POINT("
            + lng.ToString() + " "
            + lat.ToString() + ")",
            SridGoogleMaps);
    }
}

https://stackoverflow.com/a/25563269/3850405

Store both as float, and use unique key words on them.i.em

create table coordinates(
coord_uid counter primary key,
latitude float,
longitude float,
constraint la_long unique(latitude, longitude)
);