mysql圆的范围_MySQL大圆距离(Haversine公式)

silvio..

13

如果将辅助字段添加到坐标表,则可以缩短查询的响应时间.

像这样:

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验证它是否正常工作.

使用上面的代码示例,您应该通过距离以最小的错误实现对象搜索的工作和可伸缩实现.