要搜索距指定位置最近的位置,请按距离排序
To search nearest locations to given locations, order by distance
推荐答案
我们正在使用double存储latitude和longitude.此外,我们仅通过一个触发器对所有值进行预计算(通过触发器),这些值仅在观察一个点时即可进行计算.我目前无法访问我们正在使用的公式,请稍后再添加.为实现最佳速度/精度平衡而进行了优化.
We are using double to store latitude and longitude. In addition we precomute (by triggers) all values which are precomputable when looking at one point only. I currently don't have access to the formula we are using, will add this later. This is optimized for an optimal speed / precision balance.
对于定义的区域搜索(给定x公里内的所有点),我们还会存储lat/lng值乘以1e6(1,000,000),因此我们可以通过比较整数范围(例如闪电般快)来限制为正方形.
For defined area searches (give me all points within x km) we additionally store the lat/lng value multiplied with 1e6 (1,000,000) so we can limit into a square by comparing integer ranges which is lightning fast e.g.
lat BETWEEN 1290000 AND 2344000 AND lng BETWEEN 4900000 AND 4910000 AND distformularesult < 20
这是PHP当前位置值的公式和预先计算.
Here's the formular and precalculation of values of the current place in PHP.
WindowSize是一个您必须使用的值,其度数因子为1e6,用于缩小中心附近正方形中的可能结果,加快结果查找的速度-不要忘记此值至少应为 >您的搜索半径大小.
WindowSize is a value you have to play with, it's degrees factor 1e6, used to narrow down the possible results in a square around the center, speeds up result finding - dont forget this should be at least your search radius size.
$paramGeoLon = 35.0000 //my center longitude $paramGeoLat = 12.0000 //my center latitude $windowSize = 35000; $geoLatSinRad = sin( deg2rad( $paramGeoLat ) ); $geoLatCosRad = cos( deg2rad( $paramGeoLat ) ); $geoLonRad = deg2rad( $paramGeoLon ); $minGeoLatInt = intval( round( ( $paramGeoLat * 1e6 ), 0 ) ) - $windowSize; $maxGeoLatInt = intval( round( ( $paramGeoLat * 1e6 ), 0 ) ) + $windowSize; $minGeoLonInt = intval( round( ( $paramGeoLon * 1e6 ), 0 ) ) - $windowSize; $maxGeoLonInt = intval( round( ( $paramGeoLon * 1e6 ), 0 ) ) + $windowSize;搜索我中心特定范围内的所有行
Searching all rows within a specific range of my center
SELECT `e`.`id` , :earthRadius * ACOS ( :paramGeoLatSinRad * `e`.`geoLatSinRad` + :paramGeoLatCosRad * `m`.`geoLatCosRad` * COS( `e`.`geoLonRad` - :paramGeoLonRad ) ) AS `geoDist` FROM `example` `e` WHERE `e`.`geoLatInt` BETWEEN :paramMinGeoLatInt AND :paramMaxGeoLatInt AND `e`.`geoLonInt` BETWEEN :paramMinGeoLonInt AND :paramMaxGeoLonInt HAVING `geoDist` < 20 ORDER BY `geoDist`配方设计师的准确性很高(低于一米,具体取决于您所在的位置以及点之间的距离)
The formular has a quite good accuracy (below a metre, depending where you are and what distance is between the point)
我已经在数据库表example
CREATE TABLE `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `geoLat` double NOT NULL DEFAULT '0', `geoLon` double NOT NULL DEFAULT '0', # below is precalculated with a trigger `geoLatInt` int(11) NOT NULL DEFAULT '0', `geoLonInt` int(11) NOT NULL DEFAULT '0', `geoLatSinRad` double NOT NULL DEFAULT '0', `geoLatCosRad` double NOT NULL DEFAULT '0', `geoLonRad` double NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `example_cIdx_geo` (`geoLatInt`,`geoLonInt`,`geoLatSinRad`,`geoLatCosRad`,`geoLonRad`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC示例触发器
DELIMITER $ CREATE TRIGGER 'example_before_insert' BEFORE INSERT ON `example` FOR EACH ROW BEGIN SET NEW.`geoLatInt` := CAST( ROUND( NEW.`geoLat` * 1e6, 0 ) AS SIGNED INTEGER ); SET NEW.`geoLonInt` := CAST( ROUND( NEW.`geoLon` * 1e6, 0 ) AS SIGNED INTEGER ); SET NEW.`geoLatSinRad` := SIN( RADIANS( NEW.`geoLat` ) ); SET NEW.`geoLatCosRad` := COS( RADIANS( NEW.`geoLat` ) ); SET NEW.`geoLonRad` := RADIANS( NEW.`geoLon` ); END$ CREATE TRIGGER 'example_before_update' BEFORE UPDATE ON `example` FOR EACH ROW BEGIN IF NEW.geoLat <> OLD.geoLat OR NEW.geoLon <> OLD.geoLon THEN SET NEW.`geoLatInt` := CAST( ROUND( NEW.`geoLat` * 1e6, 0 ) AS SIGNED INTEGER ); SET NEW.`geoLonInt` := CAST( ROUND( NEW.`geoLon` * 1e6, 0 ) AS SIGNED INTEGER ); SET NEW.`geoLatSinRad` := SIN( RADIANS( NEW.`geoLat` ) ); SET NEW.`geoLatCosRad` := COS( RADIANS( NEW.`geoLat` ) ); SET NEW.`geoLonRad` := RADIANS( NEW.`geoLon` ); END IF; END$ DELIMITER ;有问题吗?否则玩得开心:)
Questions? Otherwise have fun :)
更多推荐
地理距离MySQL
发布评论