Storing geo data

Question

Hi I'm using ms sql 2005 to store some latlng data. It is currently being stored in a nvarchar column in the following form - "35.6949965,139.7555035" My questions are. Is this the best way to store this kind of data? If I do store the latlng in this way is it going to come and bite me later, or will it mean just that I will have to do any processing outside of the database?

Any pointers appreciated,

Answer 1

It's almost certainly not the best way to store it. If you use a pair of floating-point columns - latitude and longitude - then you can perform some functions within the database itself. As an example, here's how to calculate the distance between two points:

http://www.zipcodeworld.com/samples/distance.mssql.html

Or, as a trivial example,

SELECT * FROM table WHERE latitide<40;

would find everywhere north of New York.

Answer 2

Ideally, you'd update to MSSQL 2008 and use the new GEOGRAPHY data type. This would allow you to use all kinds of nifty features, like spatial indexes that efficiently do queries like "give me all points within a 100 mile radius of 35.6949965,139.7555035".