How can I effectively select all cities (points) within a radius of X km of a given coordinates (point) and order them by distance from MySQL table?
I'm using MySQL 5.6 with the following table structure (imported from Open Street Maps):
CREATE TABLE `cities` (
`OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
`SHAPE` geometry NOT NULL,
`osm_id` varchar(10) COLLATE utf8_general_ci DEFAULT NULL,
`code` decimal(4,0) DEFAULT NULL,
`fclass` varchar(20) COLLATE utf8_general_ci DEFAULT NULL,
`population` decimal(10,0) DEFAULT NULL,
`name` varchar(100) COLLATE utf8_general_ci DEFAULT NULL,
UNIQUE KEY `OGR_FID` (`OGR_FID`),
SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=MyISAM;
This table stores the coordinates of the cities as POINTS in the SHAPE column (geometry type). The number of cities is quite a large number (at least for the whole country).
I was thinking extracting the point coordinates from SHAPE column to separate latitude and longitude columns, then using Haversine formula, to calculate distance, but this wouldn't be much effective.
Maybe a simple query to limit the cities to an envelope (square region of a similar size) first?
How would you approach this?
What would be the final SQL?
