A reverse of Haversine formula for MySQL?

Question

In my DB i store a center point, along with a radius (in meters).

I'm looking to pass in a lat/lng, and then have the mysql values i've stored create a circle to tell me if my point i passed in is within that circle. Is there something that would allow me to do this, similar to the haversine forumla (which would assume that my point was already in the db).

Haversine Formula: ( 3959 * acos( cos( radians(40) ) * cos( radians( lat ) ) * cos( radians( long ) - radians(-110) ) + sin( radians(40) ) * sin( radians( long ) ) )

db:

circleLatCenter, circleLngCenter, Radius

passing in> select id from foo where lat,lng in (make circle function: circleLat, circleLng, radius)

Answer 1

MySQL has a whole host of spatial data functions:

Spatial Extensions to MySQL

I think the section on measuring the relationships between geometries is what you're after:

Relationships Between Geometries

Answer 2

I've done similar geographical searches by computing the bounding box via great circle distance and querying the database for that. You still need another pass in your application to "round the corners" from bounding box to circle.

So, given a database of points, a search point (X,Y) and a distance D, find all points within D of (X,Y):

1. Compute deltaX, which is the point if you moved distance D along the Y axis.
2. Compute deltaY, which is the point if you moved distance D along the X axis.
3. Compute your bounding box: (X-deltaX,Y-deltaY),(X+deltaX,Y+deltaY)
4. Query database of points use SQL BETWEEN operator: SELECT * FROM TABLE WHERE X BETWEEN X-deltaX AND X+deltaX AND Y BETWEEN Y-deltaY AND Y+deltaY
5. Post-process the list of points returned, computing the actual great circle distance, to remove the points at the corners of the square that are not within your distance circle.

As a short-cut, I typically calculate degrees-per-mile for both lat and lon (at the equator, since the degrees-per-mile is different at the poles for lon), and derive deltaX and deltaY as (D * degrees-lat-per-mile) or degrees-lon-per-mile. The difference at the equator vs pole doesn't matter much, since I'm already computing actual distance after the SQL query.

FYI - 0.167469 to 0.014564 degrees-lon-per-mile, and 0.014483 degrees-lat-per-mile

Answer 3

I know this is a long-dead post, but, in case anyone ever comes across this, you don't need to create a "reverse haversine formula" at all. The Haversine formula gives the distance between point a and point b. You need the distance between point b and point a, for your calculation. These are the same value.

SELECT *, 
( 3959 * acos( cos( radians(40) ) * cos( radians( `circleLatCenter` ) ) * cos( radians( `circleLngCenter` ) - radians(-110) ) + sin( radians(40) ) * sin( radians( `circleLngCenter` ) ) ) as `haversine` 
FROM `table` WHERE 1=1 
HAVING `haversine` < `Radius`