MySQL calculates the distance between two-point latitude and longitude with a specific SQL statement

Article reprinted address http://blog.sina.com.cn/s/blog_7bbfd5fd01017d1e.html thanks to the author.

On the basis of the original text, I added a SQL statement, easy to understand

MySQL distance calculation, Unit m, and sort lon longitude lat latitude the coordinate order shown on the general map is,latitude in front (range -90~90), longitude after (range -180~180)first create a new table with latitude and longitude

SET foreign_key_checks=0;--------------------------------Table structure for customer----------------------------- -drop TABLE IF EXISTS ' customer '; CREATE TABLE ' customer ' (' id ' int (one) unsigned not NULL auto_increment COMMENT ' self-increment primary key ', ' name ' varchar () NOT NULL COMM ENT ' name ', ' Lon ' double (9,6) not null COMMENT ' longitude ', ' lat ' double (8,6) not null COMMENT ' latitude ', PRIMARY KEY (' id ')) ENGINE =innodb auto_increment=8 DEFAULT charset=utf8 comment= ' merchant table ';--------------------------------Records of customer----- -------------------------insert INTO ' customer ' VALUES (' 1 ', ' Downtown ', ' 117.315575 ', ' 39.133462 '); insert INTO ' customer ' VALUES (' 2 ', ' Downtown ', ' 116.407999 ', ' 39.894073 '); INSERT into ' Customer ' VALUES (' 3 ', ' Baoding ', ' 115.557124 ', ' 38.853490 '); inse RT into ' Customer ' values (' 4 ', ' Shijiazhuang ', ' 114.646458 ', ' 38.072369 '); INSERT into ' Customer ' values (' 5 ', ' Changping District 1 ', ' 116.367180 ') , ' 40.009561 '); insert INTO ' Customer ' values (' 6 ', ' Haidian 2 ', ' 116.313425 ', ' 39.973078 '); insert INTO ' Customer ' values (' 7 ', ' HaidianZone 1 ', ' 116.329236 ', ' 39.987231 '); 

Then we started using MySQL's own function to calculate the Customer table, each place specific.

  Incoming parameters latitude 40.0497810000 longitude 116.3424590000

/* Parameters for  latitude latitude longitude asc ascending by near to far desc descending from far to near */select    *,    ROUND (        6378.138 * 2 * ASIN (            SQRT (                POW (                    S In (                        (                            40.0497810000 * PI ()/180-lat * PI ()/                        2)                    ,                    2                ) + COS (40.0497810000 * PI ()/ * COS (Lat * pi ()/116.3424590000) * POW (                    SIN (                        (                            * PI ()/180-lon * PI ()/                        2                    ) , 2)) * +) as    julifrom    CustomerOrder by    Juli ASC

At this point, we can clearly see the latitude 40.0497810000 longitude 116.3424590000 distance from each region in the Customer table (unit m)

The Latitude and longitude query tool used by PS http://www.gpsspg.com/maps.htm

MySQL calculates the distance between two-point latitude and longitude with a specific SQL statement