This question is targeted to MySQL 8.0 with its new spatial features.
Given the following table:
CREATE TABLE `places` (
`id` INT(11) UNSIGNED NOT NULL,
`location` POINT NOT NULL SRID 4326,
PRIMARY KEY (`id`),
SPATIAL KEY `location_idx` (`location`)
);
I want to retrieve all the places near to a Point (let's call it MY_POINT) within a certain distance in meters (let's call it MY_DISTANCE), I also want to retrieve the distance within that point, and I want to do it in the most optimal way, e.g: triggering a spatial index with a WHERE clause.
To get the distance, I must use the ST_Distance function to calculate the distance between MY_POINT and each of the query results;
To test if the point is within MY_DISTANCE, I must create a circle around MY_POINT with MY_DISTANCE as a radius and then check if MY_POINT intersects the polygon.
This is the query I've made so far:
SELECT
id,ST_Distance(MY_POINT, location) AS distance
FROM
places
WHERE
ST_Intersects(ST_SRID(ST_Buffer(MY_POINT, MY_DISTANCE / 111194.927), 4326), location) -- 111194.927 = degree of latitude in meters.
HAVING
distance <= MY_DISTANCE -- This fixes the inaccurate ST_BUFFER polygon in some cases.
ORDER BY
distance
ASC;
The problem is ST_BUFFER is making a weird and inaccurate circle (nothing to do with segments, rather I think it's because I calculate wrong the radius) and I'm missing some results.
Can anyone throw some light to this?
