Mysql – Fetch rows which belongs to a certain geo location

MySQLspatial

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.

Best Answer

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.

Related Question