3

I have a table that stores longitude / latitude / radius ( miles ) per row. What I am trying to figure out is how to select ONLY the rows that my current point is within.

This is the opposite of querying locations within a given radius of my current location, instead, I actually want to query locations that my current point is within.

Any help here would be greatly appreciated.

codeling
  • 11,056
  • 4
  • 42
  • 71
Shae Petersen
  • 58
  • 6
  • So you need to query if your value is between (lat-radius) and (lat+radius) and also between (lon-radius) and (lon+radius) ? – Jan Dragsbaek Dec 02 '11 at 08:27
  • No, that would be to find it inside of a radius*radius square around the point. @JanDragsbaek. – Madara's Ghost Dec 02 '11 at 08:29
  • Looking at the database, wouldn't it be faster to get those points out, and perform the math calculations on the php end? As i thinking the database will be rather slow at performing such mathematical tasks. – Jan Dragsbaek Dec 02 '11 at 08:31
  • Jan, I am not partial to doing the calculation within mysql, although the goal is to pull a single result of the location that i am within. I'm not sure how to do that without doing it in the query. – Shae Petersen Dec 02 '11 at 08:40

2 Answers2

5

This is easily a math problem. The distance between 2 points (x1, y1) and (x2, y2) can be calculated as follows:

statement

Simple Pythagoras. Query your database, point one is the point you want to find, point two is the one you get from the database, if the result of the above statement is smaller then the radius, it's in your range. (assuming all units match. You'll probably want to convert the lat/lon difference to km/whatever unit your radius is)

graph

Madara's Ghost
  • 172,118
  • 50
  • 264
  • 308
  • how does this factor in the locations radius? or does it? – Shae Petersen Dec 02 '11 at 08:45
  • 1
    You said that you've stored a radius of interest around each point. Here let's suppose the stored point is `(x1, y1)` and the point you're querying about is `(x2, y2)`. You just need to check whether @Truth's expression is less than the stored radius. – Hammerite Dec 02 '11 at 08:55
  • read the answer. If the result of the statement is smaller than radius, then it is within the range. – Madara's Ghost Dec 02 '11 at 08:58
1

Note that if you want to have an accurate radius calculation for good ol' earth latitudes/longitudes, you'd have to use more complicated functions than the simple pythagorean distance mentioned in the accepted answer; look for "great circle distance", as e.g. posted in another thread already; however, for your purposes, the pythagorean distance should be close enough I guess; it delivers a reasonably good approximation, except if your latitude/longitude positions are somewhere near the poles.

And, just for completeness, your problem is actually not the opposite to the question "What locations are in a certain radius around my location?" - it's the exact same, only from the other direction - but since distance calculations are symmetric, it actually doesn't matter which direction you check!

Community
  • 1
  • 1
codeling
  • 11,056
  • 4
  • 42
  • 71
  • It was actually a quite simple problem that I was over complicating in my head, all I had to do was get the distance between the 2 points ( using great circle ) then check if that distance was less than the radius of the location. – Shae Petersen Dec 02 '11 at 20:00