5

I have a MySQL table with columns latitude, longitude, userId and radius. When the user provides his current location (lat, long) along with a radius, I want to query the table to provide him with the overlapping locations based on the two radii.

For example, if user gives me a latitude of 12.5; longitude of 73.5 and a radius of 5 miles; I should be able to retrieve all entries in the MySQL table in which both radii overlap.

My initial thought was to create a bounding box for each lat, long in the database (based on the radius) and then query the incoming location details based on this bounding box. Is this approach correct? Is there something I should be concerned about if I go down this path? Any help to guide me in the right direction would be much appreciated.

PS: The below link is what I am using as a reference.

http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates

iSee
  • 604
  • 1
  • 14
  • 31
  • 1
    Don't forget MySQL has built-in data types for this: https://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html – miken32 Oct 19 '15 at 20:49

2 Answers2

4

something like this should do the trick:

SELECT 
    *
FROM
    YOUR_TABLE
WHERE
    SQRT((input_lat - db_lat) * (input_lat - db_lat) + (input_long - db_long) * (input_long - db_long)) <= input_radius

i used this: Distance between two points

There is only one mising thing : translate the radius to the same units as coordinates

Content of the link (in case it get down)

This small operation calcuates the distance between two points. The routine can work in any number of dimensions, so you cold apply it to 2D or 3D.

In 2D Define your two points. Point 1 at (x1, y1) and Point 2 at (x2, y2).

xd = x2-x1
yd = y2-y1
Distance = SquareRoot(xd*xd + yd*yd)

In 3D Define your two points. Point 1 at (x1, y1, z1) and Point 2 at (x2, y2, z2).

xd = x2-x1
yd = y2-y1
zd = z2-z1
Distance = SquareRoot(xd*xd + yd*yd + zd*zd)

As you can see, this requires that you perform a square root. Square roots should be avoided like the plague if you want to write fast code. Only perform a Square Root if you really need to.

Ways to avoid Square Roots: If you don't need a very accurate distance, you can use a lookup table to calculate it.

If, for example, you are performing collision detection between spheres, and all you want to know is whether or not two have collided, then you do not need to use a square root. Simply change the piece of code

from: if SquareRoot(xdxd + ydyd) < Diameter

to: if (xdxd + ydyd) < (Diameter*Diameter)

Melon
  • 874
  • 8
  • 17
1

That's Euclidian distance, not great circle distance, which is what would have to be used to make a radius of 5 miles, since that's the distance maps use. Euclidian distance will have a strong error vs great circle the farther away the user is from the earth's equator.

A better solution would be to either use the new mysql gis functions, but it would still be slow, unless you do a subquery: find the closest Euclidian (or even Manhattan) spots and then calculate distance on those.

Also, remember that distance functions are monotonic, so you can make do without the final square root.

Vito Floresmeyer
  • 11
  • 2