To calculate the distance to the nearest objects from another object, you need to store the coordinates in MySQL. After reading other topics, I realized that it is best to use the POINT type and create a SPATIAL index to speed up the search.

How to create a query, which of the objects table (having the coord field of the POINT type) select, for example, the 5 nearest objects to another point and calculate the distances for them?

And the second question is how to calculate the distance from the objects to the Black Sea (more precisely, to its nearest coast). So far, I realized that I need to describe his polygon.

1 answer 1

The problem is that to calculate the distance on the earth by coordinates, a complex formula is used:

Calculation of distance along the arc

There are its implementations, for example, on php . Naturally, its calculation in mysql can seriously hit performance. Such a formula is necessary because the distances along it are calculated along an arc:

enter image description here

It should also be noted that one degree of longitude has a different length depending on the latitude. So at the equator 1 degree longitude ~ 111km, and gradually decreases to zero when moving to the poles.

If you use a simple query, as suggested here , it will be wrong though, because 1 degree of latitude is not equal to 1 degree of longitude. You can neglect the curvature of the earth, if the distances between objects are small. Then you can calculate distances using the simple Pythagorean formula, but you need to add coefficients calculated for a specific point on the earth's surface.

So for the Black Sea, 1 degree of longitude will be ~ 81.3 km, and 1 degree of latitude is always the same, ~ 111.3km.

You can calculate the distance to the coast by calculating the distance to its points and choosing the smallest one.

  • Thanks for the detailed answer! With the first question already figured out. As I wrote above, I used the point type and the st_distance_sphere function, which takes into account different latitude and longitude values. And for the second question I’ll probably create another topic, as I understood that for this purpose it is better to use api maps. - fewdji