Introduction to the method of processing longitude and latitude distances using MySQL's geometry type

This article brings you an introduction to the method of processing longitude and latitude distances using MySQL's geometry type. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Create 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 the ST_GeomFromText function must be used, andPOINT() Inside is: 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, the unit is meters
Note that the longitude and latitude in POINT() are now separated by commas

3. Query locations less than 1000m away, 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;

The above is the detailed content of Introduction to the method of processing longitude and latitude distances using MySQL's geometry type. For more information, please follow other related articles on the PHP Chinese website!