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
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_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?