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

Solution 1:

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.

Solution 2:

Take a look at the new Spatial data-types that were introduced in SQL Server 2008. They are designed for this kind of task and make indexing and querying much easier and more efficient.

More information:

• MS TechNet: SQL Server 2008 Spatial Data Types,

• MSDN: Working with Spatial Data (Database Engine).

Solution 3:

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 ;

Solution 4:

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.