5

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.

Preston Crawford
  • 330
  • 1
  • 5
  • 15
  • 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 Jul 09 '10 at 00:46
  • You are doing it wrong (or just complicated). Calculate the bounding box in PHP using https://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 Feb 25 '14 at 13:00

1 Answers1

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.

TheSteve0
  • 3,530
  • 1
  • 19
  • 25
  • 3
    This is no longer true as of MySQL 5.6.1: http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relations-between-geometric-objects.html#function_st-contains . But I can't (yet) attest to the performance of these functions. – Ben Regenspan Jan 25 '12 at 22:03
  • Agreed - things have changed with MySQL 5.6.1 but I haven't tested yet either – TheSteve0 Mar 21 '12 at 07:23
  • 1
    I see tons of functions listed at that resource. Can you clarify? – Travis Webb Jul 25 '12 at 15:33