Algorithm for obtaining a square by coordinates?

Question

PHP

The essence of the question is, we have an array of coordinate points (the user's route in the format Point (lat, lng)), and we need to take the saved points from the database that lie not far from our route (conditionally 100m).

points in the database have fields (lat,lng, name...).

To pick up points close to one is not a problem did so (

"SELECT
                   *,
                    (
                    6371 *
                    acos(cos(radians($lat)) *
                    cos(radians(dots.lat)) *
                    cos(radians(dots.lng) -
                    radians($lng)) +
                    sin(radians($lat)) *
                    sin(radians(dots.lat)))
                    ) AS distance,
                    FROM dots
                    HAVING distance < 0.1
                    ORDER BY distance LIMIT 0, 20

;"

)

but the question was how to pick up many points at once with one request?

the only thing that came to mind so far is to make a selection by the square

of the dots - the points of our route are the
green dots - the
rectangle you need from the database, the actual sampling area is

conditionally like here. look for high and low points, and select points that fit.

the only question is how to determine the corners of the square for the sample and how to write the sample itself?
Or maybe there is some way more efficient and more accurate?
I will be grateful for any help

Answer 1

Sergey Sokolov , in short, he seems to have done it))

SET @home = ST_GeomFromText('LINESTRING(
//Список наших точек через которые идет маршрут 
53.862263 27.483486,
53.871521 27.493636,
53.875594 27.496447,
53.880938 27.500744,
53.882699 27.505121,
53.884246 27.504435,
53.891446 27.500293,
53.896630 27.497375,
53.905348 27.496302,
53.912531 27.495487,
53.918726 27.496882)');
SELECT *, ST_AsText(geo) as Coord,
    // Дистанция считается странно получается вроде правильно , но не уверен насчет точности . 
   // В оригинале в доках написано умножать ее на .001 для км и .00067... для миль
   // но у меня так не работало и показывало до объекта 0,003км хотя по факту до него 300м вот я решил 
         умножать ее на 100 (хотя хз может оно там сантиметрах считает) 
    ST_DISTANCE(@home, geo) * 100 AS dist
    FROM dots
   // дистанция от маршрута в радиусе которой нам нужны точки (в км ) 
    HAVING dist < 1
    ORDER BY dist
    LIMIT 30

and in the end it will be something like this

will return green dots, but not red ones.
like so )

Answer 2

I would process line segments (pairs of points), and use the formula for the distance from a point to a line plus the projection of a point onto a line to understand whether the point is near the middle of the line or near the ends.

Answer 3

Don't want to use MySQL's spatial data capabilities? See feature list .