Eğer koordinatları tabloya yardımcı alanları eklerseniz, sorgu yanıt süresini artırabilirsiniz.
Bu gibi:
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`)
)
If you're using TokuDB, you'll get even better performance if you add clustering
indexes on either of the predicates, for example, like this:
alter table Coordinates add clustering index c_lat(lat);
alter table Coordinates add clustering index c_lon(lon);
You'll need the basic lat and lon in degrees as well as sin(lat) in radians, cos(lat)*cos(lon) in radians and cos(lat)*sin(lon) in radians for each point.
Then you create a mysql function, smth like this:
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
Bu size mesafeyi verir.
Sınırlayıcı boks (dizin zaten yukarıdaki CREATE TABLE sorguda ilave edilir) yerine onu yavaşlatmak arama yardımcı olabilir böylece enlem / boylam üzerinde bir dizin eklemek için unutmayın.
INDEX `lat_lon_idx` (`lat`, `lon`)
Sadece enlem / boylam koordinatları ile eski bir tablo göz önüne alındığında, bu gibi güncellemek için bir komut dosyası ayarlayabilirsiniz: (php meekrodb kullanarak)
$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']
));
}
Then you optimize the actual query to only do the distance calculation when really needed, for example by bounding the circle (well, oval) from inside and outside.
For that, you'll need to precalculate several metrics for the query itself:
// 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));
Bu hazırlıkları göz önüne alındığında, sorgu böyle bir şey (php) gider:
$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);
EXPLAIN on the above query might say that it's not using index unless there's enough results to trigger such. The index will be used when there's enough data in the coordinates table.
You can add
FORCE INDEX (lat_lon_idx)
to the SELECT to make it use the index with no regards to the table size, so you can verify with EXPLAIN that it is working correctly.
Yukarıdaki kod örnekleri ile bir çalışma ve minimum hata ile mesafeye göre nesne arama ölçeklenebilir uygulama olmalıdır.
