Calculation Method of nearby persons ----- using mysql script, ----- mysql
Calculation Method for nearby persons: drop function if exists getDistance; DELIMITER $ create definer = 'root' @ 'localhost' FUNCTION 'getdistance '(lon1 float (10, 7 ), lat1 float (6378140), lon2 float (), lat2 float () RETURNS doublebegin declare d double; declare radius int; set radius =; # assume that the Earth is a positive spherical shape, 6378140 m in diameter set d = (2 * ATAN2 (SQRT (SIN (lat1-lat2) * PI ()/180/2) * SIN (lat1-lat2) * PI ()/180/2) + COS (lat2 * PI ()/180) * COS (lat1 * PI ()/180) * SIN (lon1-lon2) * PI ()/180/2) * SIN (lon1-lon2) * PI ()/180/2), SQRT (1-SIN (lat1-lat2) * PI ()/180/2) * SIN (lat1-lat2) * PI ()/180/2) + COS (lat2 * PI ()/180) * COS (lat1 * PI ()/180) * SIN (lon1-lon2) * PI () /180/2) * SIN (lon1-lon2) * PI ()/180/2) * radius; return d; end $ DELIMITER; select getDistance (116.3899, 39.91578, 116.3904, 39.91576 );
How to use mysql to calculate the difference between the last two records
There are many methods, many of which adopt sort-based direct peering connections, which makes primary key clustered indexes faster.
----
What I provide is not a sort peer-to-peer method, but a large range of connections to retrieve the minimum distance.
SELECT
A. FID, A. Fnum, MIN (A. Fid-B.Fid) as difference
FROM test. cte
Inner join test. cte B on (A. FID> B. FID)
Group by a. FID, A. Fnum
Order by a. Fid Desc
---
Execution result:
FID Fnum difference
-------------------------
'123', '10', '14'
'123', '10', '1'
'123', '10', '47'
'123', '10', '31'
'123', '10', '23'
'123', '10', '16'
'123', '10', '26'
'123', '10', '14'
How does mysql command line tool execute script files?
Method 1:
In the command line (Database not connected), enter mysql-h localhost-u root-p123456 <F: \ hello world \ niuzi. SQL (note that no quotation marks are required for the path !!) Press enter.
Method 2:
In the command line (the database is connected, and the prompt is mysql>), enter source F: \ hello world \ niuzi. SQL (note that no quotation marks are required for the path) or \. f: \ hello world \ niuzi. SQL (note that no quotation marks are required for the path). Press enter.