How to use MySQL's geometry type to handle longitude and latitude distance problems

Create a table

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 you must use the ST_GeomFromText function, and POINT() contains: longitude + space + latitude

Query

1. Check the latitude and longitude

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

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 the latitude and longitude in POINT() are now separated by commas.

3. Search for locations within 1000m and sort them from far to near

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.