How to upload records for the nearest cities of a certain radius?

Question

Yii

Hello. I have a list of events in a database with city name and full address. How can I unload all events from the database within a radius of, for example, 500 km from the current user? And how to get the user's coordinates if we use coordinate calculations?

How to implement it correctly?

Answer 1

1) use Yandex API to convert address to coordinates. (geocoder) go through the backend and convert addresses. then we convert it when adding a new event. Note that this used to violate the Yandex license agreement.
2) We look towards POINT. We observe the Spatial index if there is a lot of data / users (mysql 5.7.19+)
3) we read the docks for GIS functions. ST_Distance_Sphere . There are a couple of libraries in yii2 for working with point.
4) roughly calculate the user's city and coordinates - ip-api.com
5) accurately calculate the user's place using google maps and the location determination system. works more precisely, but there are more problems.
In fact, if something doesn’t work out, you can limit yourself to converting addresses to coordinates, using not a radius, but a square around the user, even if not exact.

Answer 2

Most likely you need to use js, or add a database with cities with a "radius" field and this field should have a value that is calculated from somewhere (for example, you have a store and this is 0, and from it you count to warehouses and warehouses will have a radius value equals the distance from the store to the warehouse), but this is a path with a static point, for a non-static one, I would use js.

Answer 3

If you have the user's coordinates and the event's coordinates, the request will be something like this (code in Yii1, but the idea should be clear)
$this->lat and $this->lng - latitude and longitude of the current user
$radius - radius in kilometers
lat, lng - fields with latitude and longitude of objects in the database (in my case cities)
$criteria = new CDbCriteria();
$criteria->select = "*, SQRT(
POW(110.6* (lat - " . $this->lat. "), 2) +
POW(110.6 * (" . $this->lng. " - lng) * COS(lat / 57.3), 2)) AS distance";
$criteria->having = "distance < " . $radius;
$towns = Town::model()->findAll($criteria);