Find locations in table that are within a certain radius distance of a given latitude/longitude

Question

We have a table named areas with column - id, name, lattitude, longitude and position. The position column is of POINT type in MySQL and has latitude, longitude value.

To get areas within 25 kilometers of given latitude, longitude i.e. (28.638753, 77.073803) and order them based on proximity to this point, we are using the following query as recommended here by Google.

SELECT id, name, ( 6371 * acos( cos( radians(28.638753) ) * cos( radians( Lattitude ) ) * cos( radians( Longitude ) - radians(77.073803) ) + sin( radians(28.638753) ) * sin( radians( Lattitude ) ) ) ) AS distance 
FROM areas 
HAVING distance < 25 
ORDER BY distance asc;

The table is:

CREATE TABLE `areas` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Lattitude` decimal(18,6) DEFAULT NULL,
  `Longitude` decimal(18,6) DEFAULT NULL,
  `position` point NOT NULL,
  PRIMARY KEY (`ID`),
  SPATIAL KEY `sx_areas_position` (`position`)
)

The explain query gives following. Please note that rows = 21750 as it's test data. Actual data on production will be 100k+ rows.

Following is the index information:

This query is really slow when run with > 100k of records (takes up to 500 milliseconds). Is there any way to optimize it i.e.using spatial index?

Answer 1

There are lots of Q&A here. As a first cut, use a "bounding box".

ANALYZE TABLE and OPTIMIZE TABLE are irrelevant.

You mention Lattitude, but you don't have such a column.

Either go with point and SPATIAL, or go with latitude and longitude; it is probably folly to mix them.

Answer 2

Why don't you use the spatial capabilities of MySQL?

That would look like that:

SELECT *
FROM areas
WHERE ST_Distance(ST_Transform(position, [SRID]), ST_Transform(ST_GeomFromText('Point(28.638753 77.073803)', 4326)), [SRID]) < 25000

ST_GeomFromText() creates a point in space with your lat/lon coordinates. [SRID] is the EPSG code representing the geographic system used to place that point. 4326 is the conventional unprojected geographic system using the WGS84 datum (GPS works with this system).

Then you check for a certain distance with all the points in your table but you have to check with a [SRID] with meters unit. You would have to use a more or less local projection for that. In Eastern Quebec, I use 32198 or 32188 often. ST_Transform() projects your points into a system using meters as units if you use an appropriate SRID.

Note that it could be slow to process if you have a really large table of points since you have to transform every points in the table.

Answer 3

Use GIS

First,

1. Add a column pt which is ST_SRID(ST_Point(longitude,latidue),4326)
2. Drop your old longitude and latidue columns.
3. CREATE SPATIAL INDEX spatidx ON areas (pt);

Then do something like this,

SELECT ST_AsText(pt)
FROM areas
WHERE ST_Within(
  ST_Buffer( ST_SRID(ST_Point(77.073803,28.638753), 4326), 25000 ),
  pt
);