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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you are using Entity Framework 5  lt  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 lt University gt  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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2008

Examples related to latitude-longitude

Examples related to spatial