mysql 空間計算

原文轉自：http://justcoding.iteye.com/blog/2224906

1. 利用GeoHash封裝成内置資料庫函數的簡易方案；

A:MySQL 内置函數方案，适合于已有業務，新增加LBS功能，增加經緯度字段方可，避免資料遷移

B:Mongodb 内置函數方案，适合中小型應用，快速實作LBS功能，性能優于A（推薦）

方案A: （MySQL Spatial）

1、先履歷一張表：（MySQL 5.0 以上 僅支援 MyISAM 引擎）

Sql代碼  

1. CREATE TABLE address (  
2.     address CHAR(80) NOT NULL,  
3.     address_loc POINT NOT NULL,  
4.     PRIMARY KEY(address)  
5. );  

空間索引：

Sql代碼  

1. ALTER TABLE address ADD SPATIAL INDEX(address_loc);  

插入資料：（注：此處Point(緯度,經度) 标準寫法）

Sql代碼  

1. INSERT INTO address VALUES('Foobar street 12', GeomFromText('POINT(30.620076 104.067221)'));  
2. INSERT INTO address VALUES('Foobar street 13', GeomFromText('POINT(31.720076 105.167221)'));  

查詢： 查找(30.620076,104.067221)附近 10 公裡

Sql代碼  

1. SELECT  *  
2.     FROM    address  
3.     WHERE   MBRContains  
4.                     (  
5.                     LineString  
6.                             (  
7.                             Point  
8.                                     (  
9.                                     30.620076 + 10 / ( 111.1 / COS(RADIANS(104.067221))),  
10.                                     104.067221 + 10 / 111.1  
11.                                     ),  
12.                             Point  
13.                                     (  
14.                                     30.620076 - 10 / ( 111.1 / COS(RADIANS(104.067221))),  
15.                                     104.067221 - 10 / 111.1  
16.                                     )   
17.                             ),  
18.                     address_loc  
19.                     )  

方案B:

1、先建立一張簡單的表user,兩條資料如下：

Js代碼  

1. {  
2.   "_id": ObjectId("518b1f1a83ba88ca60000001"),  
3.   "account": "[email protected]",  
4.   "gps": [  
5.     104.067221,  
6.     30.620076  
7.   ]  
8. }  
9. {  
10.   "_id": ObjectId("518b1dae83ba88d660000000"),  
11.   "account": "[email protected]",  
12.   "gps": [  
13.     104.07958,  
14.     30.653936  
15.   ]  
16. }  

其中，gps為二維數組，分别為經度，緯度

（注：此處必須按照（經度，緯度）順序存儲。我們平時表示經緯度，都是（緯度，精度），此處這種方式有木有很親民）

2、使用之前，先建立二維索引

//建立索引 最大範圍在經度-180~180

Js代碼  

1. db.user.ensureIndex({"gps":"2d"},{"min":-180,"max":180})  

//删除索引

Js代碼  

1. db.user.dropIndex({"gps":"2d"})  

3、Mongodb有兩中方式可以查找附近的XXX；其中方案2）會傳回距離（推薦）

1)标準查詢，為地球經緯度查詢内置；參數一為查詢條件利用$near查找附近，參數二$maxDistance為經緯弧度（1° latitude = 111.12 kilometers）即 1/111.12，表示查找附近一公裡。

Js代碼  

1. db.user.find({ gps :{ $near : [104.065847, 30.657554] , $maxDistance : 1/111.12} })  

2)執行命名方式，模拟成一個圓球；參數一指定geoNear方式和表名；參數二坐标，參數三是否為球形，參數四弧度（弧度=弧長/半徑 一千米的弧度1000/6378000），參數五指定球形半徑（地球半徑）

Js代碼  

1. db.runCommand({geoNear:'user', near:[104.065847, 30.657554], spherical:true, maxDistance:1000/6378000, distanceMultiplier:6378000});  

轉自：http://stackoverflow.com/a/1006668/4484798

2 利用谷歌方案

The SQL statement that will find the closest 20 locations that are within a radius of 30 miles to the 78.3232, 65.3234 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 30 miles, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

3959是地球半徑的英裡，6371是地球半徑的千米：http://baike.baidu.com/view/758812.htm

Sql代碼  

1. SELECT  
2.   id, (  
3.     3959 * acos (  
4.       cos ( radians(78.3232) )  
5.       * cos( radians( lat ) )  
6.       * cos( radians( lng ) - radians(65.3234) )  
7.       + sin ( radians(78.3232) )  
8.       * sin( radians( lat ) )  
9.     )  
10.   ) AS distance  
11. FROM markers  
12. HAVING distance < 30  
13. ORDER BY distance  
14. LIMIT 0 , 20;  

This is using the Google Maps API v3 with a MySQL backend which your already have.

https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

轉自：http://gis.stackexchange.com/a/31629

3 其他

Anyways, here’s the PHP formula for calculating the distance between two points (along with Mile vs. Kilometer conversion) rounded to two decimal places:

Php代碼  

1. function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') {  
2.       $theta = $longitude1 - $longitude2;  
3.       $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));  
4.       $distance = acos($distance);  
5.       $distance = rad2deg($distance);  
6.       $distance = $distance * 60 * 1.1515; switch($unit) {  
7.            case 'Mi': break; case 'Km' : $distance = $distance * 1.609344;  
8.       }  
9.       return (round($distance,2));  
10.  }  

It’s also possible to use MySQL to do a calculation to find all records within a specific distance. In this example, I’m going to query MyTable to find all the records that are less than or equal to variable $distance (in Miles) to my location at $latitude and $longitude:

Php代碼  

1. $qry = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*pi()/180))))*180/pi())*60*1.1515) as distance  
2.  FROM `MyTable`  
3.  WHERE distance >= ".$distance."  

For Kilometers:

Php代碼  

1. $qry = "SELECT *,(((acos(sin((".$latitude."*pi()/180)) * sin((`Latitude`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((".$longitude."- `Longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance  
2. FROM `MyTable`  
3. WHERE distance >= ".$distance."  

轉自：http://stackoverflow.com/a/8599305/4484798  & https://www.marketingtechblog.com/calculate-distance/