no-image

Mysql: LBS實現查詢附近的人 (兩經緯度之間的距離)

                                    

1. 利用GeoHash封裝成內建資料庫函式的簡易方案;

A:Mysql 內建函式方案,適合於已有業務,新增加LBS功能,增加經緯度欄位方可,避免資料遷移

B:Mongodb 內建函式方案,適合中小型應用,快速實現LBS功能,效能優於A(推薦)

方案A: (MySQL Spatial)

1、先簡歷一張表:(MySQL 5.0 以上 僅支援 MyISAM 引擎)

CREATE TABLE address (
 
    address CHAR(80) NOT NULL,
 
    address_loc POINT NOT NULL,
 
    PRIMARY KEY(address)
 
);

 

空間索引:

ALTER TABLE address ADD SPATIAL INDEX(address_loc);

插入資料:(注:此處Point(緯度,經度) 標準寫法)

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

 

查詢: 查詢(30.620076,104.067221)附近 10 公里

SELECT  *
    FROM    address
    WHERE   MBRContains
                    (
                    LineString
                            (
                            Point
                                    (
                                    30.620076   10 / ( 111.1 / COS(RADIANS(104.067221))),
                                    104.067221   10 / 111.1
                                    ),
                            Point
                                    (
                                    30.620076 - 10 / ( 111.1 / COS(RADIANS(104.067221))),
                                    104.067221 - 10 / 111.1
                                    ) 
                            ),
                    address_loc
                    )

 

 

方案B:

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

{
  "_id": ObjectId("518b1f1a83ba88ca60000001"),
  "account": "simplephp1@163.com",
  "gps": [
    104.067221,
    30.620076
  ]
}
 
{
  "_id": ObjectId("518b1dae83ba88d660000000"),
  "account": "simplephp6@163.com",
  "gps": [
    104.07958,
    30.653936
  ]
}

其中,gps為二維陣列,分別為經度,緯度

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

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

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

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

//刪除索引

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

3、Mongodb有兩中方式可以查詢附近的XXX;其中方案2)會返回距離(推薦)

1)標準查詢,為地球經緯度查詢內建;引數一為查詢條件利用$near查詢附近,引數二$maxDistance為經緯弧度(1° latitude = 111.12 kilometers)即 1/111.12,表示查詢附近一公里。

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

2)執行命名方式,模擬成一個圓球;引數一指定geoNear方式和表名;引數二座標,引數三是否為球形,引數四弧度(弧度=弧長/半徑 一千米的弧度1000/6378000),引數五指定球形半徑(地球半徑)

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

 

SELECT
  id, (
    3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
        sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
  ) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
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:

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, 
$unit = 'Mi') {
      $theta = $longitude1 - $longitude2;
      $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2)))   
     (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
      $distance = acos($distance);
      $distance = rad2deg($distance);
      $distance = $distance * 60 * 1.1515; switch($unit) {
           case 'Mi': break; case 'Km' : $distance = $distance * 1.609344;
      }
      return (round($distance,2));
 }

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:

$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
 FROM `MyTable`
 WHERE distance >= ".$distance."

For Kilometers:

$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
FROM `MyTable`
WHERE distance >= ".$distance."

轉自:

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

更對參考:

Google Maps V3: 導航到指定地址 Draw (Plot) route between User’s current location and Specified location

Google Maps V3: 通過經緯度獲取地址資訊 Get address from Latitude and Longitude

jQuery地圖外掛-jqvmap

谷歌地圖查詢兩地開車路線 Google Maps API V3: DirectionsService (Driving Directions) example

谷歌地圖新增點選事件 Google Maps API V3: Add click event listener to all (multiple) markers

Google Maps API v3: Remove Markers 移除標記

點選谷歌地圖後獲取經緯度 Get Latitude and Longitude (Location Coordinates) using Google Maps OnClick event

GMaps.js:讓你快速整合 Google Maps 服務的 jQuery 外掛

Google Maps API V3: 通過郵編獲取經緯度 Get Location (Latitude and Longitude) from Zip Code (Pin Code) using JavaScript

 Google Maps API 以某一經緯度為中心,以某一長度位半徑畫圓 Draw the radius of a circle


(adsbygoogle = window.adsbygoogle || []).push({});

function googleAdJSAtOnload() {
var element = document.createElement(“script”);
element.src = “//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js”;
element.async = true;
document.body.appendChild(element);
}
if (window.addEventListener) {
window.addEventListener(“load”, googleAdJSAtOnload, false);
} else if (window.attachEvent) {
window.attachEvent(“onload”, googleAdJSAtOnload);
} else {
window.onload = googleAdJSAtOnload;
}