Geek Answers Handbook

Geometric distance MySQL

To search for nearby locations in selected locations, order by distance

  • Should I use float or Point?
  • Do I need to pre-calculate the value of cos / sin / sqrt http://www.movable-type.co.uk/scripts/latlong-db.html
  • My requests - these are different locations within the same city.
  • Many OLD-posts report that mysql does not have a proper geo-information, if this is true, and the latest version of MySQL?
+4
source share
1 answer

We use double for storing latitude and longitude . In addition, we pre-set (by triggers) all values ​​that are pre-computer when viewing only one point. At the moment I do not have access to the formula that we use, we will add it later. It is optimized for an optimal balance of speed / accuracy.

Search for specific areas (give me all points within x km), we additionally save the values lat / lng, multiplied by 1e6 (1,000,000), so we can limit the square, comparing the ranges of integers that are lightning fast, for example

 lat BETWEEN 1290000 AND 2344000 AND lng BETWEEN 4900000 AND 4910000 AND distformularesult < 20

EDIT:

Here is the formula and preliminary costing of the current location in PHP.

WindowSize - is the value to which you want to play, the degree coefficient 1e6, used to narrow the possible outcomes in a square around the center, the faster search results - do not forget that it should be at least the size of your search radius.

 $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; paramGeoLat)); $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; ; $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; $ paramGeoLat * 1e6), $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; $ paramGeoLat * 1e6), $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;

Search all rows in 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` * `e`.`geoLatSinRad` +: paramGeoLatCosRad *` m`.`geoLatCosRad` * COS ( `e`.`geoLonRad` -: paramGeoLonRad)) AS` geoDist` 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 form has pretty good accuracy (below a meter, depending on where you are and what distance is between the point)

I have previously calculated 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 geoLonInt`,` geoLatSinRad`, `geoLatCosRad`,` geoLonRad`) 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

Trigger example

 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 ; `example` FOR EACH ROW 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 ; NEW.`geoLon` * 1e6, 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 ; NEW.`geoLat`)); 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 ; ; 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 ; `example` FOR EACH ROW 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 ; > OLD.geoLon 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 ; NEW.`geoLat` * 1e6, 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 ; NEW.`geoLat`)); 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 ; NEW.`geoLat`)); 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 :)

+6
source

More articles:


All Articles