Find nearest geo-object by lat/lon (The Pythagorean theorem or The Haversine Formula)

mysql console.sql

## формула Пифагора
mysql> SELECT
    id, name, address, latitude, longitude, (
      3959 * acos (
      cos ( radians(59.95238300) )
      * cos( radians( latitude ) )
      * cos( radians( longitude ) - radians(30.44850400) )
      + sin ( radians(59.95238300) )
      * sin( radians( latitude ) )
    )
) AS distance
FROM station
ORDER BY distance;
+----+---------------------------------+-----------------------------------------------------------------------------------+-------------+-------------+--------------------+
| id | name                            | address                                                                           | latitude    | longitude   | distance           |
+----+---------------------------------+-----------------------------------------------------------------------------------+-------------+-------------+--------------------+
| 49 | Таврический парк | Таврическая улица, 8, Санкт-Петербург, Россия | 59.94796000 | 30.37762700 | 2.4713719998696284 |
| 48 | На Невском             | Невский проспект, 49, Санкт-Петербург, Россия  | 59.93238300 | 30.34850400 | 3.7266045918833943 |
+----+---------------------------------+-----------------------------------------------------------------------------------+-------------+-------------+--------------------+
2 rows in set (0.04 sec)





##формула гаверсинусов
##http://www.arubin.org/files/geo_search.pdf
mysql> SELECT
	id, name, address, latitude, longitude,
	6371 * 2 * ASIN(
		SQRT(
			POWER(SIN((latitude - ABS(59.95238300)) * PI()/180 / 2), 2)
			+ COS(latitude * PI()/180)
			* COS(ABS(59.95238300) * PI()/180)
			* POWER(SIN((longitude - 30.44850400) * PI()/180 / 2), 2)
		)
	) as distance
FROM station
ORDER BY distance;
+----+---------------------------------+-----------------------------------------------------------------------------------+-------------+-------------+-------------------+
| id | name                            | address                                                                           | latitude    | longitude   | distance          |
+----+---------------------------------+-----------------------------------------------------------------------------------+-------------+-------------+-------------------+
| 49 | Таврический парк | Таврическая улица, 8, Санкт-Петербург, Россия | 59.94796000 | 30.37762700 | 3.977042437752394 |
| 48 | На Невском             | Невский проспект, 49, Санкт-Петербург, Россия  | 59.93238300 | 30.34850400 | 5.997018906962777 |
+----+---------------------------------+-----------------------------------------------------------------------------------+-------------+-------------+-------------------+
2 rows in set (0.04 sec)