160608. mysql distance function st_distance

With the rapid popularization of various mobile terminals in recent years, in mobile mobile location app, nearby people, near the location of the function is very common, location-based services (LBS) and related applications are more and more, and support these applications, one of the most basic technology is based on location information processing.

st_distance function

The St_distance function was added from mysql5.6.1.

SET @g1 = Point (n), @g2 = Point (2,2);

Select St_distance (@g1, @g2);

#输出结果: 1.4142135623730951

SELECT st_distance (Point (1, 1), point (2,2)) * 111195

Output: 157253.47706807632 units: M

The results of the st_distance calculation are measured in degrees, which need to be multiplied by 111195 (Earth radius 6371000*pi/180) to convert the values to meters.

Of course you can also customize the distance function:

CREATE FUNCTION SLC (

LAT1 DOUBLE,

Lon1 DOUBLE,

Lat2 DOUBLE,

Lon2 DOUBLE

) RETURNS DOUBLE RETURN 6371 * ACOS (

cos (radians (LAT1)) * cos (radians (LAT2)) * cos (

Radians (Lon2)-radians (Lon1)

) + sin (radians (lat1)) * sin (radians (LAT2))

);

SELECT SLC (1,1,2,2) from DUAL

Output: 157.22543203804852 km

Application Scenarios:

Suppose I was at the coordinates: 117.069,35.86 need to query the service area within 50KM near me and arrange it by distance from near

SELECT

S.id,s.name,s.lng,s.lat,

(St_distance (Point (LNG, LAT), point (117.069,35.86)) *111195) As distance

From

Road_servicearea s

Having distance<50

ORDER by distance

Knowledge Science:

The earth is constantly spinning around its axis, drawing a large circle perpendicular to the earth's axis, so that every point on the circle is equal to the north and South poles, and this circle is called the equator. At the north and south sides of the equator, many circles parallel to the equator are drawn, that is, "weft rings"; the segments that make up these circles are called parallels. We set the equator at 0 degrees to the north and 90 degrees to the south, to the southern latitude south of the equator, to the north of the equator called latitude. North Pole is latitude 90 degrees, Antarctica is south latitude 90 degrees. Latitude also marked the climate of hot and cold, such as the equator and low latitudes of the region without winter, polar and high latitudes without summer, mid-latitude regions distinct four seasons.

From the North Pole to the Antarctic point, you can draw many large circles in the north and south that are perpendicular to the Earth's equator, called the "Warp Circle"; the segments that make up these circles are called meridians. Year 1884, the International rules of the Greenwich Observatory in the suburbs of London by longitude as the starting point for the calculation of longitude, that is, latitude of 0 minutes and 0 seconds, also known as the "Prime Meridian." East of it for longitude, a total of 180 degrees;

West of it, for 180 degrees. Because the earth is round, the longitude of 180 degrees east longitude and longitude 180 degrees West is the same meridian line. The 180-degree meridian is "International Date Line" in all countries. In order to avoid the use of two different dates in the same area, the International Date line has slightly deviated from the land. Each longitude and latitude can be subdivided into 60 points, each divided into 60 seconds and fractional seconds. Using longitude, we can determine the exact location of every place on the Earth and show it on a map or globe.

160608. mysql distance function st_distance