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
经纬360
(
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",
"gps": [
104.067221,
30.620076
]
}
{
"_id": ObjectId("518b1dae83ba88d660000000"),
"account": "simplephp6@163",
"gps": [
104.07958,
30.653936
]
}
其中，gps为⼆维数组，分别为经度，纬度
（注：此处必须按照（经度，纬度）顺序存储。我们平时表⽰经纬度，都是（纬度，精度），此处这种⽅式有⽊有很亲民）
2、使⽤之前，先建⽴⼆维索引
//建⽴索引最⼤范围在经度-180~180
sureIndex({"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});
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.
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.
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."
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议