How do you use MySQL spatial queries to find all records in X radius? [closed]

Question

I have a table in a MySQL database with a spatial geometry column of type POINT. I'd like to be able to take a point at the center of a map and find all records within X miles (or whatever distance) of it. I can't seem to find a good example or explanation of how to do this that doesn't get heavily into geometric math. I'm happy to go that route, but would first like to try and solve it with a true spatial database.

Just wanted to note that right no I'm trying to solve this using the technique (not as precise, but just trying this for now) of a polygon with datapoints. My query looks like this. SELECT * from business WHERE MBRContains(GeomFromText('Polygon((30 -100, 30 -90, 25 -90, 25 -100, 30 -100))'), geolocation) I have a record in the table with a lat of 39 and a long of -94. As I understand it this should show up in the resultset and it doesn't. So that's another angle on the question. — Preston Crawford
– Preston Crawford, Commented Jul 9, 2010 at 0:46
You are doing it wrong (or just complicated). Calculate the bounding box in PHP using github.com/anthonymartin/GeoLocation.php and then query like WHERE lat BETWEEN minL AND maxL AND lon BETWEEN minO AND maxO. This is square area search instead of a circle but your web map is rectangular anyway, isn't it? — Slawa
– Slawa, Commented Feb 25, 2014 at 13:00

TheSteve0 · Accepted Answer · 2010-08-03 07:21:43Z

4

It won't work because MySQL hasn't actually implemented the code to make this work. They only use bounding box queries.

Notice there are no functions listed here: http://dev.mysql.com/doc/refman/5.5/en/functions-for-testing-spatial-relations-between-geometric-objects.html

I would reccomend using PostGIS or Spatialite instead.

answered Aug 3, 2010 at 7:21

TheSteve0

3,5262 gold badges21 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ben Regenspan Over a year ago

This is no longer true as of MySQL 5.6.1: dev.mysql.com/doc/refman/5.6/en/… . But I can't (yet) attest to the performance of these functions.

TheSteve0 Over a year ago

Agreed - things have changed with MySQL 5.6.1 but I haven't tested yet either

Travis Webb Over a year ago

I see tons of functions listed at that resource. Can you clarify?

Collectives™ on Stack Overflow

How do you use MySQL spatial queries to find all records in X radius? [closed]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related