Query points within a given radius in MySQL

Question

I have created the following MySQL table to store latitude/longitude coordinates along with a name for each point:

CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `location` point NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `location` (`location`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

I am trying to query:

• all points within an n mile radius of a given point;
• the distance of each returned point from the given point

All of the examples I have found refer to using a minimum bounding rectangle (MBR) rather than a radius. The table contains approximately 1 million points, so this need needs to be as efficient as possible.

I look forward to your suggestions.

Many thanks.

Answer 1

Have you looked into Hilbert curves solutions?

Answer 2

Radius is not efficiently indexable. You should use the bounding rectangle to quickly get the points you are probably looking for, and then filter points outside of the radius.

Answer 3

Thank you both for your answers.

I eventually found the solution at http://www.movable-type.co.uk/scripts/latlong-db.html.