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?

Answer 1

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

http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

http://blogs.technet.com/andrew/archive/2007/11/26/sql-server-2008-spatial-data-types.aspx

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

Save it as FLOAT( 10, 6 ) NOT NULL

This is the recommendation from google itself: http://code.google.com/support/bin/answer.py?answer=65622&topic=11364

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

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.

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.

Answer 7

Dear Pure.Krome,

How do you manage to get the spatial data from googlmaps api into sql server 2008. I worked out a script to point out coordinates on googlemaps from sql server 2008, but I would like to insert the address and spatial data from a form into sql server 2008. I'd like to know how you do that.

Thanks!