我有一个工作的PHP脚本,它获取经度和纬度值,然后将它们输入到MySQL查询中.我想把它做成MySQL.这是我目前的PHP代码:
if ($distance != "Any" && $customer_zip != "") { //get the great circle distance
//get the origin zip code info
$zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
$result = mysql_query($zip_sql);
$row = mysql_fetch_array($result);
$origin_lat = $row['lat'];
$origin_lon = $row['lon'];
//get the range
$lat_range = $distance/69.172;
$lon_range = abs($distance/(cos($details[0]) * 69.172));
$min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
$max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
$min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
$max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
$sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";
}
有谁知道如何完全成为MySQL？我已经浏览了一下互联网,但大部分关于它的文献都令人困惑.
来自Google Code FAQ - 使用PHP,MySQL和Google Maps创建商店定位器:
这是SQL语句,它将找到距离37,-122坐标25英里半径范围内最近的20个位置.它根据该行的纬度/经度和目标纬度/经度计算距离,然后仅询问距离值小于25的行,按距离对整个查询进行排序,并将其限制为20个结果.要以公里而不是里程搜索,请将3959替换为6371.
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));
纬度和纬度经度.
所以
SELECT
acos(
cos(radians( $latitude0 ))
* cos(radians( $latitude1 ))
* cos(radians( $longitude0 ) - radians( $longitude1 ))
+ sin(radians( $latitude0 ))
* sin(radians( $latitude1 ))
) AS greatCircleDistance
FROM yourTable;
是你的SQL查询
以Km或英里为单位获得结果,将结果乘以地球的平均半径(3959英里,6371公里或3440海里)
您在示例中计算的是边界框.如果将坐标数据放在启用空间的MySQL列中,则可以使用MySQL的内置功能来查询数据.
SELECT
id
FROM spatialEnabledTable
WHERE
MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))
如果将辅助字段添加到坐标表,则可以缩短查询的响应时间.
像这样:
CREATE TABLE `Coordinates` ( `id` INT(10) UNSIGNED NOT NULL COMMENT 'id for the object', `type` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'type', `sin_lat` FLOAT NOT NULL COMMENT 'sin(lat) in radians', `cos_cos` FLOAT NOT NULL COMMENT 'cos(lat)*cos(lon) in radians', `cos_sin` FLOAT NOT NULL COMMENT 'cos(lat)*sin(lon) in radians', `lat` FLOAT NOT NULL COMMENT 'latitude in degrees', `lon` FLOAT NOT NULL COMMENT 'longitude in degrees', INDEX `lat_lon_idx` (`lat`, `lon`) )
如果您正在使用TokuDB,那么如果在任一谓词上添加聚类索引,您将获得更好的性能,例如,如下所示:
alter table Coordinates add clustering index c_lat(lat); alter table Coordinates add clustering index c_lon(lon);
你需要以度为单位的基本lat和lon以及以弧度为单位的sin(lat),以弧度为单位的cos(lat)*cos(lon)和以弧度为单位的cos(lat)*sin(lon).然后你创建一个mysql函数,像这样:
CREATE FUNCTION `geodistance`(`sin_lat1` FLOAT,
`cos_cos1` FLOAT, `cos_sin1` FLOAT,
`sin_lat2` FLOAT,
`cos_cos2` FLOAT, `cos_sin2` FLOAT)
RETURNS float
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
RETURN acos(sin_lat1*sin_lat2 + cos_cos1*cos_cos2 + cos_sin1*cos_sin2);
END
这给你的距离.
不要忘记在lat/lon上添加索引,这样边界装箱可以帮助搜索而不是减慢速度(索引已经添加到上面的CREATE TABLE查询中).
INDEX `lat_lon_idx` (`lat`, `lon`)
给定一个只有lat/lon坐标的旧表,你可以设置一个脚本来更新它:(php using meekrodb)
$users = DB::query('SELECT id,lat,lon FROM Old_Coordinates');
foreach ($users as $user)
{
$lat_rad = deg2rad($user['lat']);
$lon_rad = deg2rad($user['lon']);
DB::replace('Coordinates', array(
'object_id' => $user['id'],
'object_type' => 0,
'sin_lat' => sin($lat_rad),
'cos_cos' => cos($lat_rad)*cos($lon_rad),
'cos_sin' => cos($lat_rad)*sin($lon_rad),
'lat' => $user['lat'],
'lon' => $user['lon']
));
}
然后优化实际查询以仅在真正需要时进行距离计算,例如通过从内部和外部限制圆(井,椭圆).为此,您需要为查询本身预先计算几个指标:
// assuming the search center coordinates are $lat and $lon in degrees // and radius in km is given in $distance $lat_rad = deg2rad($lat); $lon_rad = deg2rad($lon); $R = 6371; // earth's radius, km $distance_rad = $distance/$R; $distance_rad_plus = $distance_rad * 1.06; // ovality error for outer bounding box $dist_deg_lat = rad2deg($distance_rad_plus); //outer bounding box $dist_deg_lon = rad2deg($distance_rad_plus/cos(deg2rad($lat))); $dist_deg_lat_small = rad2deg($distance_rad/sqrt(2)); //inner bounding box $dist_deg_lon_small = rad2deg($distance_rad/cos(deg2rad($lat))/sqrt(2));
鉴于这些准备,查询就像这样(PHP):
$neighbors = DB::query("SELECT id, type, lat, lon,
geodistance(sin_lat,cos_cos,cos_sin,%d,%d,%d) as distance
FROM Coordinates WHERE
lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d
HAVING (lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d) OR distance <= %d",
// center radian values: sin_lat, cos_cos, cos_sin
sin($lat_rad),cos($lat_rad)*cos($lon_rad),cos($lat_rad)*sin($lon_rad),
// min_lat, max_lat, min_lon, max_lon for the outside box
$lat-$dist_deg_lat,$lat+$dist_deg_lat,
$lon-$dist_deg_lon,$lon+$dist_deg_lon,
// min_lat, max_lat, min_lon, max_lon for the inside box
$lat-$dist_deg_lat_small,$lat+$dist_deg_lat_small,
$lon-$dist_deg_lon_small,$lon+$dist_deg_lon_small,
// distance in radians
$distance_rad);
解析上面的查询可能会说它没有使用索引,除非有足够的结果来触发这样的.当坐标表中有足够的数据时,将使用索引.您可以将FORCE INDEX(lat_lon_idx)添加到SELECT以使其使用索引而不考虑表大小,因此您可以使用EXPLAIN验证它是否正常工作.
使用上面的代码示例,您应该通过距离以最小的错误实现对象搜索的工作和可伸缩实现.
我必须详细解决这个问题,所以我会分享我的结果.这使用带zip表latitude和longitude表的表.它不依赖于Google地图; 相反,你可以将它适应任何包含lat/long的表.
SELECT zip, primary_city,
latitude, longitude, distance_in_mi
FROM (
SELECT zip, primary_city, latitude, longitude,r,
(3963.17 * ACOS(COS(RADIANS(latpoint))
* COS(RADIANS(latitude))
* COS(RADIANS(longpoint) - RADIANS(longitude))
+ SIN(RADIANS(latpoint))
* SIN(RADIANS(latitude)))) AS distance_in_mi
FROM zip
JOIN (
SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r
) AS p
WHERE latitude
BETWEEN latpoint - (r / 69)
AND latpoint + (r / 69)
AND longitude
BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint))))
AND longpoint + (r / (69 * COS(RADIANS(latpoint))))
) d
WHERE distance_in_mi <= r
ORDER BY distance_in_mi
LIMIT 30
查看该查询中间的这一行:
SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r
这zip将在纬度/长点42.81/-70.81的50.0英里内搜索表中最近的30个条目.当您将其构建到应用程序中时,您可以在此处放置自己的点和搜索半径.
如果你想在公里的工作,而不是英里,改69到111.045和改变3963.17,以6378.10在查询中.
这是一篇详细的文章.我希望它对某人有所帮助. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/
京公网安备 11010802040832号 | 京ICP备19059560号-6 