CODEWITHSUNDEEP
mysqlgispostal-code
DBMarcos99
DBMarcos99

Reputation: 465

How do I utilise longitude/latitude values efficiently in Mysql?

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?

Upvotes: 3

Views: 967

Answers (2)

Rowland Shaw
Rowland Shaw

Reputation: 38130

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.

Upvotes: 2

JasCav
JasCav

Reputation: 34632

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

Upvotes: 1

Related Questions