views:

312

answers:

2

Thanks to Wikileaks, here in the UK we can now access our longitude and latitude co-ordinates for each post code wikileaks postcodes uk IMPORTANT. Do not use this data in public applications - There's concern that the data has been deliberately corrupted and you could be held as being in breach of copyright law. Try using data from an open source site like this Postcode UK site

Now, it's possible to calculate distances between each point using the following calculation (originally from the fine book Pro Mysql):

The distance d between two points (x1,y1) and (x2,y2) can be calculated from the following equation (so long as x values are latitude and y values are longitude in radians, r is the radius of the sphere which is 3956 in miles):

d= acos(sin(x1)sin(x2)+cos(x1)cos(x2)*cos(y2-y1)) * r

Now is this good enough, or should I use the new GIS data types and functions, and if so, how do I convert my longitude and latitude references to the Point data type? I realise that because the Earth is not a perfect sphere, so the distance calculation I quote above is not perfect; however it's good enough for my purposes. Would using the new GIS functionality a) make calculation of distance quicker b) make the distance calculation more exact?

+1  A: 

Well, to answer your "B" question, because your equation assumes a perfect sphere, you're going to be a little off (and it would, most likely, get progressively worse the farther the two points are from one another). If GIS accounts for this, then you would get the distance calculations to be more exact.

As for converting from lat/long to point, I could have sworn that this was provided within the Google Maps API. (See the GLatLng reference.)

JasCav
Thx for that. I was really looking for options within Mysql, but using the API interface can be a useful alternative.
DBMarcos99
+1  A: 

To focus on (a):

In the past, I've precomputed parts, storing the lat, long, xaxis, yaxis and zxais, where the x, y & z are defined as:

xaxis = cos(radians(Lat)) * cos(radians(Lon))
yaxis = cos(radians(Lat)) * sin(radians(Lon))
zaxis = sin(radians(Lat))

The distance can then be calculated using SQL loosely like (acos( xaxis * $xaxis + yaxis * $yaxis + zaxis * $zaxis ) * 6367.0 / 1.852) (where those starting with a $ are precomputed for the start point in question in the same manner as above)

Pre-computing in this manner pushes the relatively expensive trig to a one time event, and simplifies the query.

Rowland Shaw
Fantastic - I've always been a fan of denormalizing data storage where it aids calculation time. thanks!
DBMarcos99
Interesting that you use 6367 as the figure for the Earth's radius in kilometres - This figure seems to vary between 6356 and 6378 according to different sources. Anyway, I'm marking this as the accepted answer
DBMarcos99