2

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?

  • You have your data in lat/lng (decimal degrees) you will get the odd shapes with point buffers in northern and southern hemispheres see for an example planet.qgis.org/planet/tag/projection you need to buffer in meters or feet – Mapperz Feb 2 at 17:27
  • Looks like MySQL 8 supports ST_Transform (like postgis) dev.mysql.com/doc/refman/8.0/en/… – Mapperz Feb 2 at 17:35
  • @Mapperz could you elaborate, please? I've tried to use ST_Transform but it doesn't support transforming from a geometric SRID to a geographic one. (3741 - Transformation from SRID 0 is not supported.) – Tylerian Feb 3 at 2:33
  • @Mapperz Also, ST_Buffer doesn't support buffering a geographic point, so I can't buffer a point in meters. (3618 - st_buffer(POINT, ...) has not been implemented for geographic spatial reference systems.) – Tylerian Feb 3 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.