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

Question

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?

Roseberry

Answer 1

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

Miscall

Answer 2

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.

Unprovided

Since the original answer was posted, I found this article jasonfollas.com/blog/archive/2010/02/14/… discussing a possible workaround. – Rhona

Warning is no longer valid. EF now supports Geography types. – Reject

Nerveless EF does support Spatial Types, it uses different type set to WCF Data Services, thus there are not compatible – Li

hibernate 5 spatial does not still for example :( – Acidic

Fair Warning still applies Entity Framework Core 2.0 github.com/aspnet/EntityFrameworkCore/issues/1100 – Tynishatynwald

Answer 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 ;

Sclerotic

The question clearly states SQL Server, not MySQL. And you certainly wouldn't want a table with just latitude and longitude on their own like that. – Grizelda

Agreed -bad answer. Use the new GEOGRAPHY spatial type. – Irresolute

The link has moved too, to code.google.com/apis/maps/articles/phpsqlajax.html – Atelier

I wouldn't use float because of precision issues. Use decimal(9,6). – Playwright

There are actual cases where where lat and lng outperform georgraphy, even with high density indexes in SQL 2014. For example: find all point is withing a rectangle. Only I'm not sure, I see that Google Maps now use 7 instead of 6 digits? – Spindly

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

Desrosiers

great for what I was looking for, do you have anything for tracks/lines – Rother

Another approach which could also work, depending on your scenario, is storing the long and lat, and then just dynamically creating the geography object on the fly at runtime. – Danit

Great idea but be aware that you cannot create spatial indexes on computed columns if that is someone's intention. – Jacket

@Jacket I think you can if you make it a persisted computed column. – Masry

@Masry True that might work! I would worry about performance implications but it could work in certain situations. Let us know if it works for you. – Jacket

@Jacket Actually it doesn't work! Although I can create the index, the performance is horrendous! Not sure if I'm doing something wrong as surely if an index can be created it can be used... But all my spatial queries just time out, which is worse than the non-indexed behaviour. – Masry

@Masry Interesting, thanks for the update. I read a post from someone doing something similar, they acted like it worked for them but performance was very poor. They could have had a slightly different situation than this though. – Jacket

Turns out performance is still horrendous even when I convert the column to a normal column, so I've created my own post: #39374401 – Masry

Thanks and +1, your answer helped me. But I think it would be better to use Point instead of STGeomFromText. For example: [geography]::Point([Latitude], [Longitude], 4326). – Hebbel

Answer 5

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.

Interference

GIS and spatial data processing have a long history and standard textual, binary representations since the 2000s at least. You'll end up with all the problems you mentioned if you don't use the spatial types and the standard representations – Akira

Answer 6

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.

Irresolute

And what if you're not on 2008 yet, or what if you use SQLCE? The latter doesn't support the GEOGRAPHY type... – Exempt

If SqlCE or < 2008 supports binary, it's possible to store the results are varbinary and then use the Spatial tools library dll to do spatial calculations against this binary data representation in your .NET code. Not the best solution, but still a possible solution to some problems. (nuget for sql spatial .. to grab that dll). – Irresolute

urgh :( thanks for nothing imageshack. I've not used IS for years :( imgur.com all the way! – Irresolute

-1, this answer is incomplete without the image. Please consider replacing it with a new image or a textual table description, or deleting this answer. – Christinachristine

Answer 7

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.

Yawn

Answer 8

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

Afflict

Answer 9

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://mcmap.net/q/88480/-coordinatesystemid-on-dbgeography

Faustinafaustine

Answer 10

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

Facial

This my case. I need to store the coordinates in only one field and separated by a comma. I think one could use a TEXT as a field type. What do you think? – Kumkumagai

Answer 11

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

Rogation

To make sure that there is only 1 unique set of latitude and longitude pair. You don't want to store coordinate {0,0} twice in your table, don't you? – Rogation

You probably don't want to have a separate coordinates table like this at all, especially with the uniqueness constraint it's a maintainence nightmare handling the case that two locations refer to the same point, not to mention cleaning up unreferenced rows. – Grizelda

> You probably don't want to have a separate coordinates table like this at all - Not at all? Never? How would you store that in 1 field? What about the millions of people NOT using SQL 2008 Spatial type? – Hecklau

Having such constraint is a bad decision. Suppose Bob lives in House A, and will move to House B, the house where Alice used to live. Soon Bob will not be able to save his address (location), because Alice didn't update hers yet - or never will. – Nitrate

@Hecklau - that's not what he said. Read his comment. He said there should be no reason to store a pair of values in a separate table. Just put the lat/long on the original table and save the overhead of a second table and all of the JOINS. – Masry