Calculate the distance between two-point latitude and longitude under MySQL

The formula is as follows, unit meters:

1th latitude and Longitude: lng1 LAT1

2nd latitude and Longitude: lng2 lat2

Round (6378.138*2*asin (sqrt (POW (LAT1*PI ()/180-lat2*pi ()/180)/2), 2) +cos (Lat1*pi ()/180) *cos (LAT2*PI ()/180) * Pow (Sin ((Lng1*pi ()/180-lng2*pi ()/180)/2), 2)) *1000)

For example:

SELECT Store_id,lng,lat, ROUND (6378.138*2*asin (SQRT (POW (22.299439*pi ()/180-lat*pi ()/180)/2, 2) +cos ( 22.299439*pi ()/180) *cos (Lat*pi ()/180) *pow (SIN ((114.173881*pi ()/180-lng*pi ()/180)/2), 2))) *1000) as Juli
From Store_info
ORDER by Juli DESC
LIMIT 316

The resources are as follows:

Http://www.cnblogs.com/ycsfwhh/archive/2010/12/20/1911232.html

Calculate distance based on two-point latitude and longitude

How are these longitude determined? The earth is constantly spinning around its axis (the earth's axis is an imaginary line through both the north and the South Poles and the center of the Earth), and the Earth's waist draws a large circle perpendicular to its 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. Second, from the North Pole to the Antarctic point, you can draw a lot of north-south direction of the Earth's equator perpendicular to the large circle, known as 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." In the east of it is longitude, a total of 180 degrees, the west of it for the western longitude, a total of 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. For example, what is the latitude and longitude of Beijing? We can easily find out from the map is 116 degrees east longitude 24 points, north latitude 39 degrees 54 points. Ships sailing in the sea can determine the position and direction of the ship as long as the longitude of the location is measured. Latitude has a total of 90 degrees. The equator is 0 degrees, aligned to the Poles, the smaller the circle, the greater the degree. The horizontal line is latitude, and the vertical line is longitude. Of course, we can calculate the equation of four yuan and two times. Both longitude and latitude are an angle. Longitude is a two-sided angle, which is the angle of two meridians. Because all meridians are the same length, in order to measure longitude to select a starting point, after 1884 years of International conference, decided to take the meridian line of a major afternoon cross of the Royal Observatory (formerly) in the outskirts of London, Thames, R., the South Bank of the United Kingdom to the beginning meridian, known as the Prime Meridian. The Prime Meridian plane is the starting plane, and the end face is the local meridian plane. The longitude of a point is the angle between the meridian plane and the Prime Meridian plane. Measured on the equator, from the Prime Meridian plane as the starting point, respectively east to West Measurement, the east measure value is called the longitude degree, the westward measure is called the longitude degree. Thus, the longitude of one land is the place for the Prime Meridiandirection and angular distance. The Prime meridian is 0° longitude, the maximum longitude is 180°, the maximum longitude is 180°, the east and west longitude 180 ° meridian is the same meridian, so regardless of longitude or west longitude, and collectively, 180° meridians. Latitude is a line face angle. The starting plane is the equatorial plane, and the line is the local ground normal. The so-called normal, that is perpendicular to the reference flat sphere surface of the line. The latitude of a place is the angle between the normal of the ground and the equatorial plane. Latitude is measured on the local meridian, measured from the equator to the south, north to north, and to the south as latitude. Thus, the latitude of a land is the direction and angular distance of the equator. The equator is the 0° latitude, the maximum latitude is 90°, that is the North Pole, and the maximum latitude is 90°, which is the Antarctic point.

Interchange by latitude

Degrees (DDD): E 108.90593 degrees N 34.2163 degrees

How to convert degrees (DDD):: 108.90593 degrees to degrees seconds (DMS) Longitude e 108 degrees 54 minutes 22.2 seconds? The conversion method is to change the 108.90593 integer digits to 108 (degrees), use 0.90593*60=54.3558, take integer digits 54 (min), The 0.3558*60=21.348 is then rounded to 21 (seconds), which translates to 108 degrees 54 minutes and 21 seconds.

The same will be the degree of seconds (DMS): East longitude e 108 degrees 54 minutes 22.2 seconds conversion into degrees (DDD) method is as follows: 108 degrees 54 minutes 22.2 seconds =108+ (54/60) + (22.2/3600) = 108.90616 degrees

Because of the reason that the decimal digit is retained in the calculation, there is a certain error in the positive and negative calculation, but the error effect is not great. The 1-second error is a few meters. GPS car Friends can use the above method to convert to their own desired unit coordinates.

Converted to meters by latitude

Latitude is divided into 60 points, each divided into 60 seconds and fractional seconds.

The latitude line projects seemingly horizontal parallel lines on the graph, but is actually a circle of different radii. All locations with the same specific latitude are on the same parallel.
The equatorial latitude is 0°, dividing the planets into the southern and northern hemispheres.
Latitude refers to the line angle between a point and the Earth's spherical surface and the equatorial plane of the earth, and its value is between 0-90 degrees. The latitude of the point north of the equator is called Latitude, which is recorded as N, and the latitude of the point south of the equator is said to be S.
Latitude values in the region between 0-30 degrees called the low latitude region, the latitude value in the area between 30-60 degrees is called the middle latitude region, the latitude value in the area between 60-90 degrees is called the high latitude region.
The equator, the southern Tropic, the Tropic of Cancer, the Antarctic Circle and the Arctic Circle are special parallels.
Latitude 1 seconds in length
The total meridian length of the earth is about 40008km. Average:
Latitude 1 degrees = approximately 111km
Latitude 1 min = approximately 1.85km
Latitude 1 sec = approximately 30.9m

Calculates the distance between two points based on the latitude and longitude of any two points on Earth.

The earth is a nearly standard ellipsoid with an equatorial radius of 6378.140 km, a polar radius of 6356.755 km and an average radius of 6371.004 km. If we assume that the earth is a perfect sphere, then its radius is the average radius of the Earth, recorded as R. If the 0-degree meridian is used as a benchmark, the surface distance between the two points can be calculated based on the latitude and longitude of any two points on the Earth's surface (this ignores the error caused by the Earth's surface topography, which is only a theoretical estimate). The latitude and longitude of 1th A is (Lona, LatA), the latitude and longitude of 2nd B is (LONB, LATB), according to the datum of 0 degrees longitude, the positive value of longitude (longitude), the longitude is negative (-longitude), north latitude takes 90-latitude value (90- Latitude), 90+ latitude value (90+latitude), the two points after the above treatment are counted as (Mlona, Mlata) and (Mlonb, MLATB). Then, according to triangular derivation, we can get the following formula to calculate the distance of two points:

C = sin (mlata) *sin (MLATB) *cos (mlona-mlonb) + cos (mlata) *cos (MLATB)

Distance = R*arccos (C) *pi/180

Here, R and distance units are the same, if the use of 6371.004 km as a radius, then distance is the unit, if you want to use other units, such as mile, also need to do unit conversion, 1-kilometer =0.621371192mile

If the longitude is treated only as positive or negative, and the latitude is not 90-latitude (assuming that the northern hemisphere is the only application in the southern hemisphere), then the formula will be:

C = sin (LatA) *sin (LATB) + cos (LatA) *cos (LATB) *cos (MLONA-MLONB)

Distance = R*arccos (C) *pi/180

The above can be introduced through a simple triangular transformation.

If the input and output of trigonometric functions are in radians, then the formula can also be written:

C = sin (lata*pi/180) *sin (latb*pi/180) + cos (lata*pi/180) *cos (latb*pi/180) *cos ((MLONA-MLONB) *pi/180)

Distance = R*arccos (C) *pi/180

That is

C = sin (lata/57.2958) *sin (latb/57.2958) + cos (lata/57.2958) *cos (latb/57.2958) *cos ((MLONA-MLONB)/57.2958)

Distance = R*arccos (c) = 6371.004*arccos (c) kilometer = 0.621371192*6371.004*arccos (c) mile = 3958.758349716768*arccos (c ) Mile

In practical application, it is generally through an individual zip code to find the corresponding regional center of latitude and longitude, and then based on these latitude and longitude to calculate the distance between each other, so as to estimate the approximate distance between certain groups of the range ( For example, the distribution of hotel passengers-the latitude and longitude of each traveler's postal code and the distance range computed by the latitude and longitude of the hotel-and so on, so a database is a useful resource for querying latitude and longitude by postal code.
Attached: C # code:

Private Const Double Earth_radius = 6378.137;//Earth radius
private static Double rad (double D)
{
return d * math.pi/180.0;
}

public static double Getdistance (double lat1, double lng1, double lat2, double lng2)
{
Double RADLAT1 = rad (LAT1);
Double radLat2 = rad (LAT2);
Double A = RADLAT1-RADLAT2;
Double b = rad (lng1)-Rad (LNG2);

Double s = 2 * Math.asin (MATH.SQRT (Math.pow (Math.sin (A/2), 2) +
Math.Cos (RADLAT1) *math.cos (RADLAT2) *math.pow (Math.sin (B/2), 2));
s = S * earth_radius;
s = Math.Round (S * 10000)/10000;
return s;
}

Calculate the distance between two-point latitude and longitude under MySQL