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)

Cheers

Answer 1

UPDATE:

Heres what I did to solve it: table.radius * 1.609) / 111 >= sqrt( POW( clat - 40, 2 ) + POW( clng - ( - 95 ) , 2 ))

table.radius stored as miles.

---

Here is the temporary solution i've come up with. I'm not sure if its the best but seems to be working:

Original MySQL select for haversine:

SELECT id, ( 3959 * ACOS( COS( RADIANS( 37 ) ) * COS( RADIANS( lat ) ) * COS( RADIANS(  `long` ) - RADIANS( -122 ) ) + SIN( RADIANS( 37 ) ) * SIN( RADIANS(  `long` ) ) ) ) AS distance
FROM table
HAVING distance <25
ORDER BY distance
LIMIT 0 , 20;

I've modified it to work with my problem above, by just taking the point i'm passing in, creating a circle based off that point, and the radius that is stored, and looking for center points of circles i have in the db as such:

SELECT id, ( 3959 * ACOS( COS( RADIANS( 37 ) ) * COS( RADIANS( lat ) ) * COS( RADIANS(  `long` ) - RADIANS( -122 ) ) + SIN( RADIANS( 37 ) ) * SIN( RADIANS(  `long` ) ) ) ) AS distance
FROM table
HAVING distance <(`table.radius`*1609.344)
ORDER BY distance
LIMIT 0 , 20;

This seems to be returning the expected results in the area within the circle. Please if anyone has other suggestions on this let me know.

Cheers

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

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