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

mysql geospatial spatial-query
Preston Crawford picture Preston Crawford · Jul 9, 2010 · Viewed 13.4k times · Source

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.

Answer

TheSteve0 picture TheSteve0 · Aug 3, 2010

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.

Related questions

Find nearest latitude/longitude with an SQL query

I have latitude and longitude and I want to pull the record from the database, which has nearest latitude and longitude by the distance, if that distance gets longer than specified one, then don't retrieve it. Table structure: id latitude …

Read More
Querying within longitude and latitude in MySQL

Before asking for specific code examples, I just wanted to ask whether it is possible to make a query something like this pseudo code: select items from table where lat/lon = -within x miles of a certain lat/lon point- …

Read More
MySQL INSERT/UPDATE on POINT column

I'm trying to populate my DB with geographical places of my country. One of my tables have 4 fields: ID[PK], latitude. longitude ande geoPoint EDIT `SCDBs`.`Punto_Geografico`; SET @lat = 18.469692; SET @lon = -63.93212; SET @g = 'POINT(@lat @lon)'; UPDATE …

Read More