MySQL user-defined function to obtain the distance between two points

CREATE DEFINER=`root`@`%` FUNCTION `f_getdistance`(in_from_x decimal(10,3), in_from_y decimal(10,3),in_to_x decimal(10,3),in_to_y decimal(10,3)) RETURNS decimal(20,10)BEGINdeclare line LineString;#select CONCAT('LineString(',in_from_x,' ',in_from_y,',',in_to_x,' ',in_to_y,')');RETURN GLength(GeomFromText( CONCAT('LineString(',in_from_x,' ',in_from_y,',',in_to_x,' ',in_to_y,')') )) * 111000;END

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

Calculate the Distance Based on the longitude and latitude of two points

How are these cable cables determined?

The earth is constantly rotating around the Earth's axis (the Earth's axis is a hypothetical line through the earth's north and south poles and the Earth's Center), draw a large circle perpendicular to the Earth's axis in the waist, so that every point in the circle is equal to the distance between the north and south poles. This circle is called the "Equator ". On the north and south sides of the Equator, draw many circles parallel to the equator, which are "latitude circles". The line segments that constitute these circles are called weft wires. We set the equator to zero latitude, 90 degrees to the north to the south, and north to the south of the equator. The North Pole is 90 degrees north latitude, and the South Pole is 90 degrees south latitude. The latitude and height also indicate the hot and cold weather of the climate, for example, there is no winter in the Equator and low latitude regions, there is no summer in the polar and high latitude regions, and the middle latitude regions have distinct seasons.

Second, from the Arctic point to the South Pole, we can draw many large circles perpendicular to the earth's equator in the North-South direction, which is called the "Meridian Circle". The line segments that constitute these circles are called the meridian lines. In the year 1884, the international regulation adopted the longitude line of the Greenwich Observatory in the suburbs of London as the starting point for calculating the longitude, that is, 0 minutes 0 seconds, longitude, also known as the "primary meridian ". In the east of it is the east longitude, a total of 180 degrees; In its West is the west longitude, a total of 180 degrees. Because the earth is circular, the longitude of the east longitude is 180 degrees and the longitude line of the west longitude is the same. Each country defines the 180-degree longitude line as the "International Date change line ". In order to avoid using two different dates in the same region, the international date variation slightly deviated in case of land.

Each longitude and latitude can be subdivided into 60 minutes, and each minute is divided into 60 seconds and decimal places in seconds. With the geographic line, we can determine the specific location of every place on the earth and express it on a map or globe. For example, what is the longitude and latitude of Beijing? We can easily find out from the map that the longitude is 116 degrees 24 minutes, And the latitude is 39 degrees 54 minutes. If the longitude of a ship sailing in the sea is measured, the position and Forward Direction of the ship in the ocean can be determined.

The latitude is 90 degrees. The equator is 0 degrees, arranged to two poles. The smaller the circle, the larger the degree.
The horizontal line is the latitude, and the vertical line is the longitude.
Of course, it can be computed by quadratic equation of four elements.

Longitude and latitude are both an angle.
Longitude is the angle between two sides and the plane of the longitude line. Since all the meridian lines are of the same length, in order to determine a starting point for the longitude, after consultation at the 1884 International Conference, it was decided to go through the Greenwich Mean Royal Observatory (old site) on the outskirts of London and the south bank of the River Bank of England) the meridian line of a Main Meridian Cross is the starting meridian line, called the primary Meridian. The primary meridian plane is the start plane and the end plane is the local meridian plane. The longitude of a point is the angle between the longitude plane of the point and the primary meridian plane. On the equator, the self-Primary meridian plane is used as the starting point and measured to the east and west respectively. The value to the east is called the east longitude, and the value to the west is called the west longitude. It can be seen that the longitude of a region is the direction and angular distance of the region to the primary Meridian. The prime meridian is 0 ° longitude, the maximum value of east longitude is 180 °, the maximum value of west longitude is 180 °, and the longitude line of east longitude and west longitude is the same meridian, and collectively referred to as 180 ° longitude line.

Latitude is a line angle. The START plane is the equator plane, and the line is the local ground normal. The so-called normal, that is, the line perpendicular to the reference flat surface. The latitude of a place is the angle between the normal of the place and the Equator plane. Latitude is measured on the local longitude line. It is measured from the equator to the South and the North. The North is called the north latitude, and the south is called the south latitude. It can be seen that the latitude of a region is the direction and angular distance of the region to the equator. The equator is a 0 ° weft, and the maximum value of the north latitude is 90 °, that is, the northern point; the maximum value of the south latitude is 90 °, that is, the southern pole.

Longitude and latitude Interchange

Degree (DDD): E 108.90593 degrees N 34.21630 degrees

How to convert degrees (DDD): 108.90593 degrees to degrees (DMS) Dong Jing e 108 degrees 54 minutes 22.2 seconds? The conversion method is to change the 108.90593 integer to 108 degrees, and use 0.90593*60 = 54.3558 to retrieve the entire digit 54 (points ), 0.3558*60 = 21.348 and then the integer is 21 (seconds), so it is converted to 108 degrees 54 minutes 21 seconds.

Similarly, the method for converting degrees into seconds (DMS): Dong Jing e 108 degrees 54 minutes 22.2 seconds to degrees (DDD) is as follows: 108 degrees 54 minutes 22.2 seconds = 108 + (54/60) + (22.2/3600) = 108.90616 degrees

Because the decimal point is retained During computation, there is a certain error in the positive and negative computation, but the error does not have a great impact. The error of one second is a few meters. GPS drivers can use the above method to convert the Unit coordinates they need.

 

Longitude and latitude to meter

The latitude is divided into 60 points, and each point is divided into 60 seconds and the decimal point of the second.

The latitude line is projected on a seemingly horizontal parallel line, but it is actually a circle with different radius. All locations with the same specific latitude are on the same weft.
The latitude of the equator is 0 °, and the planet is evenly divided into the Southern Hemisphere and the northern hemisphere.
Latitude refers to the line angle between a point and the Earth's ball center and the Earth's track surface. Its value ranges from 0 to 90 degrees. The latitude of a point located north of the equator is north latitude, which is recorded as n. the latitude of a point located south of the equator is called south latitude, and it is recorded as S.
The region with the latitude value between 0 and 30 degrees is called the low latitude area, and the region with the latitude value between 30 and 60 degrees is called the middle latitude area, the area with a latitude value between 60 and 90 degrees is called the high latitude area.
The equator, the return-to-south line, the return-to-north line, the Antarctic Circle, and the Arctic Circle are special latitudes.
Length of 1 second at latitude
The total meridian length of the Earth is about 40008 km. Average:
Latitude 1 degree = approximately 111 km
Latitude: 1 = approximately 1.85 km
Latitude 1 second = approximately 30.9 m

Calculate the distance between two points based on the longitude and latitude of any two points on the ball

The earth is a quasi-Standard Elliptical body. Its equator radius is 6378.140 km, the polar radius is 6356.755 km, and the average radius is 6371.004 km. If we assume that the Earth is a perfect sphere, its radius is the mean radius of the Earth, which is recorded as R. If the zero-degree longitude line is used as the basis, the distance between the two points can be calculated based on the latitude and longitude of any two points on the Earth's surface. (here, the error caused by the calculation of the earth's surface topography is ignored, only theoretical estimates ). Set the longitude and latitude of the first Vertex a to (Lona, Lata), and the longitude and latitude of the second vertex B to (lonb, latb) based on the zero-degree longitude line, the longitude is a positive value (longpolling), the longitude is a negative value (-longpolling), and the latitude is 90-latitude ), when the latitude value is 90 + (90 + latitude), the two points after processing are counted as (mlona, mlata) and (mlonb, mlatb ). According to the triangle derivation, the following formula is obtained for calculating the distance between two points:

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

Distance = r * arccos (c) * PI/180

Here, the units of R and distance are the same. If 6371.004 km is used as the radius, distance is the unit of kilometer. If other units such as mile are used, unit conversion is also required, 1Km = 0.621371192 mile

If only the longitude is processed plus and minus, And the latitude is not processed with 90-latitude (assuming they are all in the northern hemisphere, and the southern hemisphere is only available in Australia), 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 simple triangle transformation.

If the input and output values of trigonometric functions use radians, the formula can also be written as follows:

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, we generally use an individual zip code to find the longitude and latitude of the region center corresponding to the zip code, and then calculate the distance between each other based on these latitudes, to estimate the approximate distance between certain groups (for example, the distribution range of hotel passengers-the latitude and longitude corresponding to the zip code of each passenger and the distance range calculated by the hotel's latitude and longitude ), therefore, it is a useful resource to query such a database by postal code.
Appendix: 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;
}

How are these cable cables determined?

The earth is constantly rotating around the Earth's axis (the Earth's axis is a hypothetical line through the earth's north and south poles and the Earth's Center), draw a large circle perpendicular to the Earth's axis in the waist, so that every point in the circle is equal to the distance between the north and south poles. This circle is called the "Equator ". On the north and south sides of the Equator, draw many circles parallel to the equator, which are "latitude circles". The line segments that constitute these circles are called weft wires. We set the equator to zero latitude, 90 degrees to the north to the south, and north to the south of the equator. The North Pole is 90 degrees north latitude, and the South Pole is 90 degrees south latitude. The latitude and height also indicate the hot and cold weather of the climate, for example, there is no winter in the Equator and low latitude regions, there is no summer in the polar and high latitude regions, and the middle latitude regions have distinct seasons.

Second, from the Arctic point to the South Pole, we can draw many large circles perpendicular to the earth's equator in the North-South direction, which is called the "Meridian Circle". The line segments that constitute these circles are called the meridian lines. In the year 1884, the international regulation adopted the longitude line of the Greenwich Observatory in the suburbs of London as the starting point for calculating the longitude, that is, 0 minutes 0 seconds, longitude, also known as the "primary meridian ". In the east of it is the east longitude, a total of 180 degrees; In its West is the west longitude, a total of 180 degrees. Because the earth is circular, the longitude of the east longitude is 180 degrees and the longitude line of the west longitude is the same. Each country defines the 180-degree longitude line as the "International Date change line ". In order to avoid using two different dates in the same region, the international date variation slightly deviated in case of land.

Each longitude and latitude can be subdivided into 60 minutes, and each minute is divided into 60 seconds and decimal places in seconds. With the geographic line, we can determine the specific location of every place on the earth and express it on a map or globe. For example, what is the longitude and latitude of Beijing? We can easily find out from the map that the longitude is 116 degrees 24 minutes, And the latitude is 39 degrees 54 minutes. If the longitude of a ship sailing in the sea is measured, the position and Forward Direction of the ship in the ocean can be determined.

The latitude is 90 degrees. The equator is 0 degrees, arranged to two poles. The smaller the circle, the larger the degree.
The horizontal line is the latitude, and the vertical line is the longitude.
Of course, it can be computed by quadratic equation of four elements.

Longitude and latitude are both an angle.
Longitude is the angle between two sides and the plane of the longitude line. Since all the meridian lines are of the same length, in order to determine a starting point for the longitude, after consultation at the 1884 International Conference, it was decided to go through the Greenwich Mean Royal Observatory (old site) on the outskirts of London and the south bank of the River Bank of England) the meridian line of a Main Meridian Cross is the starting meridian line, called the primary Meridian. The primary meridian plane is the start plane and the end plane is the local meridian plane. The longitude of a point is the angle between the longitude plane of the point and the primary meridian plane. On the equator, the self-Primary meridian plane is used as the starting point and measured to the east and west respectively. The value to the east is called the east longitude, and the value to the west is called the west longitude. It can be seen that the longitude of a region is the direction and angular distance of the region to the primary Meridian. The prime meridian is 0 ° longitude, the maximum value of east longitude is 180 °, the maximum value of west longitude is 180 °, and the longitude line of east longitude and west longitude is the same meridian, and collectively referred to as 180 ° longitude line.

Latitude is a line angle. The START plane is the equator plane, and the line is the local ground normal. The so-called normal, that is, the line perpendicular to the reference flat surface. The latitude of a place is the angle between the normal of the place and the Equator plane. Latitude is measured on the local longitude line. It is measured from the equator to the South and the North. The North is called the north latitude, and the south is called the south latitude. It can be seen that the latitude of a region is the direction and angular distance of the region to the equator. The equator is a 0 ° weft, and the maximum value of the north latitude is 90 °, that is, the northern point; the maximum value of the south latitude is 90 °, that is, the southern pole.

Longitude and latitude Interchange

Degree (DDD): E 108.90593 degrees N 34.21630 degrees

How to convert degrees (DDD): 108.90593 degrees to degrees (DMS) Dong Jing e 108 degrees 54 minutes 22.2 seconds? The conversion method is to change the 108.90593 integer to 108 degrees, and use 0.90593*60 = 54.3558 to retrieve the entire digit 54 (points ), 0.3558*60 = 21.348 and then the integer is 21 (seconds), so it is converted to 108 degrees 54 minutes 21 seconds.

Similarly, the method for converting degrees into seconds (DMS): Dong Jing e 108 degrees 54 minutes 22.2 seconds to degrees (DDD) is as follows: 108 degrees 54 minutes 22.2 seconds = 108 + (54/60) + (22.2/3600) = 108.90616 degrees

Because the decimal point is retained During computation, there is a certain error in the positive and negative computation, but the error does not have a great impact. The error of one second is a few meters. GPS drivers can use the above method to convert the Unit coordinates they need.

 

Longitude and latitude to meter

The latitude is divided into 60 points, and each point is divided into 60 seconds and the decimal point of the second.

The latitude line is projected on a seemingly horizontal parallel line, but it is actually a circle with different radius. All locations with the same specific latitude are on the same weft.
The latitude of the equator is 0 °, and the planet is evenly divided into the Southern Hemisphere and the northern hemisphere.
Latitude refers to the line angle between a point and the Earth's ball center and the Earth's track surface. Its value ranges from 0 to 90 degrees. The latitude of a point located north of the equator is north latitude, which is recorded as n. the latitude of a point located south of the equator is called south latitude, and it is recorded as S.
The region with the latitude value between 0 and 30 degrees is called the low latitude area, and the region with the latitude value between 30 and 60 degrees is called the middle latitude area, the area with a latitude value between 60 and 90 degrees is called the high latitude area.
The equator, the return-to-south line, the return-to-north line, the Antarctic Circle, and the Arctic Circle are special latitudes.
Length of 1 second at latitude
The total meridian length of the Earth is about 40008 km. Average:
Latitude 1 degree = approximately 111 km
Latitude: 1 = approximately 1.85 km
Latitude 1 second = approximately 30.9 m

Calculate the distance between two points based on the longitude and latitude of any two points on the ball

The earth is a quasi-Standard Elliptical body. Its equator radius is 6378.140 km, the polar radius is 6356.755 km, and the average radius is 6371.004 km. If we assume that the Earth is a perfect sphere, its radius is the mean radius of the Earth, which is recorded as R. If the zero-degree longitude line is used as the basis, the distance between the two points can be calculated based on the latitude and longitude of any two points on the Earth's surface. (here, the error caused by the calculation of the earth's surface topography is ignored, only theoretical estimates ). Set the longitude and latitude of the first Vertex a to (Lona, Lata), and the longitude and latitude of the second vertex B to (lonb, latb) based on the zero-degree longitude line, the longitude is a positive value (longpolling), the longitude is a negative value (-longpolling), and the latitude is 90-latitude ), when the latitude value is 90 + (90 + latitude), the two points after processing are counted as (mlona, mlata) and (mlonb, mlatb ). According to the triangle derivation, the following formula is obtained for calculating the distance between two points:

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

Distance = r * arccos (c) * PI/180

Here, the units of R and distance are the same. If 6371.004 km is used as the radius, distance is the unit of kilometer. If other units such as mile are used, unit conversion is also required, 1Km = 0.621371192 mile

If only the longitude is processed plus and minus, And the latitude is not processed with 90-latitude (assuming they are all in the northern hemisphere, and the southern hemisphere is only available in Australia), 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 simple triangle transformation.

If the input and output values of trigonometric functions use radians, the formula can also be written as follows:

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, we generally use an individual zip code to find the longitude and latitude of the region center corresponding to the zip code, and then calculate the distance between each other based on these latitudes, to estimate the approximate distance between certain groups (for example, the distribution range of hotel passengers-the latitude and longitude corresponding to the zip code of each passenger and the distance range calculated by the hotel's latitude and longitude ), therefore, it is a useful resource to query such a database by postal code.
Appendix: 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;
}