mysql 圆心距离_MySQL大圆距离(Haversine公式)

春华秋衣

如果将助手字段添加到坐标表中，则可以提高查询的响应时间。就像这样：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)*罪恶(LON)。然后创建一个MySQL函数，smth如下所示：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上添加索引，这样边界装箱可以帮助搜索而不是减慢搜索速度(索引已经添加到上面的CREATETABLE查询中)。INDEX `lat_lon_idx` (`lat`, `lon`)给定一个只有lat/lon坐标的旧表，您可以设置一个脚本来像这样更新它：(PHP使用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);对上述查询的解释可能会说，除非有足够的结果触发索引，否则它不会使用索引。当坐标表中有足够的数据时，将使用索引。可以向SELECT中添加强制索引(LAT_LON_IDX)，使其使用索引而不考虑表大小，因此可以使用EXPLAIN验证它是否正确工作。有了上面的代码示例，您应该有一个工作的、可伸缩的对象搜索实现，且错误最小。