I've a table (locations) which contains records as given below:

id |     lat   |     lng   | radius_in_km
-----------------------------------------
1  | -18.17943 | 83.968051 |     5
2  | 44.5796   | 7.069637  |     10

Now, I've got a point (lat, lng) from Google and I need to fetch the records from locations table if the given lat, lng falls into radius of those rows. Please notice, the radius is dynamic/different for each row so I need to match the records according to their radius with the given point.

I've searched the stackoverflow network but couldn't find anything that works for me, any hints would be fine even not the code.

Keeping lat long like that isn't spatial. You need to make a point

Point(long,lat)

I would store the result of the above on the row, rather than individual lat/long cords. Regardless of what you store on the row, that's what you have to work with. Not storing that on the row will just be slower, as you'll have to calculate it every time.

Next what you want is the Bounding Box test. In MySQL they're designated with an MBR prefix.

SELECT *
FROM table
WHERE ST_Contains(
  ST_Buffer( Point(lng,lat), DISTANCE_IN_DEGREES ),
  Point(google.long, google.lat)
);

You'll have to calculate DISTANCE_IN_DEGREES yourself. MySQL doesn't have a geography type, so you can't just use a fixed distance like meters, miles, or feet. You have to do the math yourself. This is a lot of work in itself.

ST_Contains doesn't use an index. For that you'll need MBRContains. Or you could just use PostgreSQL, for that it'll look like this,

SELECT *
FROM table
WHERE ST_DWithin(
  ST_Buffer( ST_MakePoint(lng,lat)::geography, DISTANCE_IN_METERS ),
  ST_MakePoint(google.long, google.lat)::geography
);

Not just is that shorter, but from the docs

This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

So you don't have to use two functions like you would in MySQL MBRcontains (bounding box w/ index), and ST_Contains.

  • Thanks for your answer but I'm still confused. If I change the lat, lng to point and instead of storing radius as km, if I store the distance in degrees then which query should I use? – The Alpha Aug 20 '17 at 19:03
  • @TheAlpha both, however if you take it one step further and (1) store them as a point, (2) store the ST_Buffer(pt, radius) as a geom, and index the geom then the operation on MBRContains will use the index, potentially saving costly calculations with ST_Contains – Evan Carroll Aug 20 '17 at 20:37

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.