mysql 地理,地理距离MySQL

To search nearest locations to given locations, order by distance

Should I use float or Point?

My searches are various locations within one city.

Many OLD posts are telling mysql is not having proper geo support, Is it true with latest MySQL version as well?

解决方案

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.

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

EDIT:

Here's the formular and precalculation of values of the current place in PHP.

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)

I've precalculated the following values in my database table 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

Example trigger

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 :)