There is a MySQL database with the following table:

CREATE TABLE IF NOT EXISTS `my_table` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` text NOT NULL, `coords` point NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The data is inserted into it like this:

 INSERT INTO `my_table` VALUES (NULL, 'name1', POINT(50.456257,30.511237)), (NULL, 'name2', POINT(50.456230,30.511425)), (NULL, 'name3', POINT(50.456244,30.510902));

Now the actual task is to select from the table only those records that are located no farther than 5 meters from point A (the coordinates of the point are known).

    1 answer 1

    Material here: Spatial extensions .

    The only thing that could dig it out is:

     X(p) - Возвращает значение X-координаты для p как число двойной точности. Y(p) - Возвращает значение Y-координаты для p как число двойной точности. Пример: SET @pt = 'Point(56.7 53.34)'; mysql> SELECT Y(GeomFromText( @pt )); +----------------------+ | Y(GeomFromText( @pt )) | +----------------------+ | 53.34 | +----------------------+

    Thus, you can write a trace request:

     select * from my_table where sqrt(pow(Y(coords),2) + pow(X(coords),2)) <= $searchRadius

    But there must be some kind of solution that is not, because searching in a given radius is quite a necessary thing.

    • I thought that this solution is not a ride. Since there are points in the coordinates on the ground, and the distance you have in meters. In the material I have cited there is some kind of mention of "configuration", and there is a function of distance, only it accepts not input for points, but just "configuration". Poke in that direction. - MuFF
    • That is the problem, that it is necessary to convert the report system from longitude and latitude into a report system that will be tied to the terrain and it can be said that it is “flat”. Then it is possible to use meters. And using different reporting systems is wrong here in the root. I will give an example. there is a prime meridian, and there is a 359th one. I think the problem is already clear, but besides that they are at the distance of one meridian from each other. But in meters at each point they will be at different distances from each other and will have zero distance at the poles, and at the equator the maximum is MuFF
    • It is necessary to develop a mathematical model with its own logic. And I think that if you keep longitude and latitude, and search by radius, then you need to do this logic, which will be stored in stored procedures. Getting the entire table to the application level is quite expensive, but it translates the radius into some longitude and latitude format at the application level - it doesn't work out exactly, and somehow it's stupid .. :) - MuFF