CODEWITHSUNDEEP
mysql
Boberino
Boberino

Reputation: 21

SQL needed to get a subset of locations based on lat/long with a min/max distance constraint

I have MySQL table with 500 location records that has a similar structure to:

id, name, lat, long

Lat & long are decimal (float) location values.

My need is to return a random 100 record set that are a minimum 200 meters and a maximum 500 meters away from each other. I'm familiar with using the great circle formula to get the distance between two points. However, I have no idea how to write a select statement to compare all locations against each other to ensure the distance requirements for the random 100 selected? Any thoughts or help would be greatly appreciated. My only tools are a MySQL database so the solution needs to be written in MySQL SQL. Thank you in advance.

Upvotes: 1

Views: 88

Answers (2)

tklodd
tklodd

Reputation: 1079

SELECT * 
FROM (
    SELECT p.latitude1 AS latitude1, p.longitude1 AS longitude1, p.latitude2 AS latitude2, p.longitude2 AS longitude2,
    (((ACOS(SIN((latitude2*PI()/180)) * SIN((latitude1*PI()/180))+COS((latitude2*PI()/180)) * COS((latitude1*PI()/180)) * COS(((longitude2- longitude1)* PI()/180))))*180/PI())*60*1.1515) AS distance
    FROM places p
    )
WHERE distance > 200 AND distance < 500
ORDER BY RAND()
LIMIT 100

Upvotes: 1

Mike
Mike

Reputation: 1667

If you are only looking at 500 metres then take some short cuts and pretend this is an xy space and use simple trigonometry. Can you even get away with a square instead of donut ?

You could use that as the first cut and then to proper maths on the remainder.

Your second cut will now be small enough. Assign a random no to each and take top X. Voila

edit For the first part, cheat and use Google maps. Find out what fraction of a degree equates to 500 metres at the equator. Use x +/- that value and the same for y. Quick and dirty first cut. **ok it won't work at the poles! **

Upvotes: 0

Related Questions