Databases
StackOverflow2013
Postsdbo
POHow do I utilise longitude/latitude values efficiently in Mysql?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I utilise longitude/latitude values efficiently in Mysql?
    text
    copied!<p>Thanks to Wikileaks, here in the UK we can now access our longitude and latitude co-ordinates for each post code <a href="http://wikileaks.org/wiki/UK_government_database_of_all_1,841,177_post_codes_together_with_precise_geographic_coordinates_and_other_information,_8_Jul_2009" rel="nofollow noreferrer">wikileaks postcodes uk</a> 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 <a href="http://www.npemap.org.uk/data/" rel="nofollow noreferrer">Postcode UK site</a></p> <p>Now, it's possible to calculate distances between each point using the following calculation (originally from the fine book Pro Mysql):</p> <blockquote> <p>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):</p> <p>d= acos(sin(x1)*sin(x2)+cos(x1)*cos(x2)*cos(y2-y1)) * r </p> </blockquote> <p>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?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload