Hello. Could you please help me with finding all coordinates

within a radius using a spatial index, given that I have a table with coordinates?

SET @lng = 37.57925;
SET @lat = 55.807456;
SET @radius = 140 / 111320; -- Radius in degrees
SET @buffer = ST_Buffer(ST_GeomFromText(CONCAT('POINT(', @lng, ' ', @lat, ')')), @radius);

SELECT
camera_id,
ST_X(coordinates) AS lng,
ST_Y(coordinates) AS lat,
ST_Within(coordinates, @buffer) AS within,
ST_Contains(@buffer, coordinates) AS contains,
6371000 * 2 * ASIN(SQRT(
POWER(SIN((@lat - ST_Y(coordinates)) * PI() / 180 / 2), 2) +
COS(ST_Y(coordinates) * PI() / 180) * COS(@lat * PI() / 180) *
POWER(SIN((@lng - ST_X(coordinates)) * PI() / 180 / 2), 2)
)) AS distance,
SQRT(POW((ST_Y(coordinates) - @lat) * 111111, 2) +
POW((ST_X(coordinates) - @lng) * COS(RADIANS(@lat)) * 111111, 2)) AS distance2
FROM
camera_location
WHERE
camera_id IN (2, 91284);

+---------+---------+---------+------+--------+------------------+------------------+
|camera_id|lng |lat |within|contains|distance |distance2 |
+---------+---------+---------+------+--------+------------------+------------------+
|2 |37.57925 |55.807456|1 |1 |0 |0 |
|91284 |37.578632|55.806313|0 |0 |132.83352326404489|132.73309974118803|
+---------+---------+---------+------+--------+------------------+------------------+

The distance between the point (37.578632, 55.806313) and the center (37.57925, 55.807456) is 132 meters, but there is a problem that the polygon built with ST_Buffer at 140 meters from the point does not contain this point.

SET @lng = 37.57925;
SET @lat = 55.807456;
SET @radius = 140; -- Radius in meters
SET @buffer = ST_Buffer(ST_GeomFromText(CONCAT('POINT(', @lng, ' ', @lat, ')'), 4326), @radius / 111320);

SELECT ST_AsText(@buffer) AS buffer;

Your first ST_GeomFromText is missing the SRID.

it does not help ((( -- Does a circle with center at POINT(37.57925 55.807456) and radius 160 meters contain the point POINT(37.578865 55.806046) -> it returns false SELECT ST_Contains(ST_Buffer(ST_GeomFromText('POINT(37.57925 55.807456)', 4326), 160 / 111320), st_geomfromtext('POINT(37.578865 55.806046)', 4326)); -- However, the distance between them equals 158.6200064427625 meters: SELECT 6371000 * 2 * ASIN(SQRT( POWER(SIN((55.806046 - 55.807456) * PI() / 180 / 2), 2) + COS(55.807456 * PI() / 180) * COS(55.806046 * PI() / 180) * POWER(SIN((37.578865 - 37.57925) * PI() / 180 / 2), 2) )) AS distance_meters; -- distance: 158.6200064427625

Please show us the result of 160 / 111320 140 / 111320

Please try again but casting these integers to decimal data type with several decimal places select 160 / 111320 is different from select cast(160 as decimal(20, 10)) / 111320

mysql> select 160/111320; +------------+ | 160/111320 | +------------+ | 0.0014 | +------------+ 1 row in set (0,00 sec) mysql> select 140/111320; +------------+ | 140/111320 | +------------+ | 0.0013 | +------------+ 1 row in set (0,00 sec)

---------------------------------------+ cast(160 as decimal(20, 10)) / 111320 | ---------------------------------------+ 0.00143729787999 | ---------------------------------------+

it returns null ( SELECT ST_Contains(ST_Buffer(ST_GeomFromText('POINT(37.57925 55.807456)', 4326), cast(160 as decimal(20, 10)) / 111320), st_geomfromtext('POINT(37.578865 55.806046)', 4326));

The issue was with the st_buffer function, which has really poor accuracy. https://gis.stackexchange.com/a/449445 It's much better to use st_distance_sphere.