Method for dealing with longitude and latitude distance by using geometry type of MySQL

Table building

CREATE TABLE `map` (
 `id` int(11) NOT NULL,
 `address` varchar(255) NOT NULL DEFAULT '',
 `location` geometry NOT NULL,
 PRIMARY KEY (`id`),
 SPATIAL KEY `idx_location` (`location`)
)

Insert

INSERT INTO map (id, address, location) VALUES (1, 'somewhere', ST_GeomFromText('POINT(121.366961 31.190049)'));

Note that the ST_GeomFromText function must be used, and the inside of POINT () is: longitude + space + latitude

Query

Step 1 Check latitude and longitude

SELECT address, ST_AsText(location) AS location FROM map;

Step 2 Calculate the distance between two points

SELECT ST_Distance_Sphere(POINT(121.590347, 31.388094),location) AS distant FROM map;

The calculated result is in meters

Note that latitude and longitude are now separated by commas in POINT ()

3. Query the locations with a distance less than 1000m and sort them from far to near

SELECT id, address, ST_Distance_Sphere(POINT(121.590347, 31.388094),location) AS distant FROM map WHERE ST_Distance_Sphere(POINT(121.590347, 31.388094),location) < 1000 ORDER BY distant;