The method of dealing with the distance between longitude and latitude by using the geometry type of MySQL

Build 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 `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 POINT () contains longitude + space + latitude.

Query

1. View latitude and longitude

SELECT address, ST_AsText (location) AS location FROM map

two。 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.

Notice that the latitude and longitude in POINT () are now separated by commas.

3. Query locations with a distance of less than 1000m, and sort them by far and near

The copy code is as follows: 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

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.