CODEWITHSUNDEEP
geometrygeospatialmysql-5.6
adam78
adam78

Reputation: 10078

MySQL 5.6 Find points within a distance and order by distance ASC

I have the following table:

CREATE TABLE `places` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(256) DEFAULT NULL,
 `coordinates` point DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Louvre ", POINT(48.861105, 2.335337));

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Eiffel Tower", POINT(48.858271, 2.293795));

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Pere Lachaise", POINT(48.861131, 2.394683));

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Brooklyn", POINT(40.711089, -73.948391));

I want to return all places within 5 miles of the Louvre which should return 3 records (Louvre, Eiffel Tower, Pere Lachaise)

I run the following query but I end up with all 4 records. What I'm I doing wrong?

 SET @radius = 5 * 1609.344; -- convert miles to meters

 SELECT name, ST_Distance(coordinates,POINT(48.861105, 2.335337)) AS distance 
   FROM places
  WHERE ST_Within(coordinates, ST_Buffer(POINT(48.861105, 2.335337),@radius))
ORDER BY distance ASC

Note: I am aware MySQL 5.7 has the ST_Distance_Sphere() function to achieve the above but I'm stuck with 5.6 at the moment.

Any help appreciated.

Upvotes: 0

Views: 545

Answers (1)

Ryan Sparks
Ryan Sparks

Reputation: 1376

You can polyfill ST_Distance_Sphere using the Haversine Formula:

DELIMITER $$

CREATE FUNCTION `ST_Distance_Sphere` (point1 POINT, point2 POINT)

    RETURNS FLOAT
    no sql deterministic
    BEGIN
        declare R INTEGER DEFAULT 6371000;
        declare `φ1` float;
        declare `φ2` float;
        declare `Δφ` float;
        declare `Δλ` float;
        declare a float;
        declare c float;
        set `φ1` = radians(y(point1));
        set `φ2` = radians(y(point2));
        set `Δφ` = radians(y(point2) - y(point1));
        set `Δλ` = radians(x(point2) - x(point1));

        set a = sin(`Δφ` / 2) * sin(`Δφ` / 2) + cos(`φ1`) * cos(`φ2`) * sin(`Δλ` / 2) * sin(`Δλ` / 2);
        set c = 2 * atan2(sqrt(a), sqrt(1-a));

        return R * c;
    END$$

DELIMITER ;

Stolen from: https://www.bram.us/2018/03/01/mysql-st_distance_sphere-polyfill/

Upvotes: 1

Related Questions