1

I have a table in a database with over a million geospatial points that we'll call 'flags'. I also have a feed of live 'vehicles' which report their latitude and longitude positions.

Every time a vehicle's position comes through I need to run code that does this:

Select all flags that are within a 100 metre radius of this vehicle's position.

Is this possible with SQL? If so how could I go about doing it?

jskidd3
  • 4,609
  • 15
  • 63
  • 127
  • wasnt my downvote but you should show what you have already tried to do – AnthonyBlake Jan 16 '14 at 08:55
  • @AnthonyBlake I'd love to but I can't show what I've already tried to do if I don't know where to start D: (hence why I asked the question) – jskidd3 Jan 16 '14 at 08:56
  • Are you using MySQL's [spatial extensions](http://dev.mysql.com/doc/en/spatial-extensions.html)? – eggyal Jan 16 '14 at 09:02
  • See http://stackoverflow.com/a/17032718. – eggyal Jan 16 '14 at 09:04
  • @eggyal Thanks very much! That looks very useful. Just need to update MySQL and I'll check it out. – jskidd3 Jan 16 '14 at 09:17
  • @eggyal Perhaps you could answer this question? http://stackoverflow.com/questions/21168380/use-mysql-spatial-extensions-to-check-if-table-of-points-are-inside-circle – jskidd3 Jan 16 '14 at 18:02

2 Answers2

1

Should be doable using the formula

(x-center_x)^2 + (y - center_y)^2 < radius^2

You have all the component parts to do

SELECT foo
  FROM point p
 WHERE (((p.x - vehicle.x) * (p.x - vehicle.x)) + ((p.y - vehicle.y) * (p.y - vehicle.y))) < 1000

Or somethign like that, I've taken a few syntax punts

owenrumney
  • 1,520
  • 3
  • 18
  • 37
  • 1
    This does not take advantage of MySQL's spatial extensions, nor is it geodesic (Pythagoras' theorem only works in Euclidean geometry). – eggyal Jan 16 '14 at 09:11
  • 2
    The distance of 100 meters is small enough that you can use an approximation (vs. Haversine formulae). But the responder's method is Pythagoras' theorem which is fine at the equator but will grow in error as you move toward the poles (due to curvature in longitude lines). I would recommend using Pythagoras' theorem on an equilateral projection which approximates the curvature along the longitude lines. See http://www.movable-type.co.uk/scripts/latlong.html – Andrew - OpenGeoCode Jan 17 '14 at 23:35
0

Check these resources:

  1. Selecting points within a bounding circle -- gives detailed information about optimal solution: select rectangular area and then filter out points that do not fall into the circle inside that square;
  2. Select coordinates which fall within a radius of a central point? -- similar question on stackoverflow.
Community
  • 1
  • 1
Sergiy Sokolenko
  • 5,967
  • 35
  • 37