Mysql – How to utilise longitude/latitude values efficiently in Mysql

gisMySQLpostal-code

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?

Best Answer

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.

Related Topic