Answer by Chris Petersn for MySql spatial data query for finding near locations using haversine formula

To answer:

is there any other ways to directly apply haversine formula on my table.?

I have a direct answer in a solution I solved recently. I had an issue with CPU performance having to apply the formula to a great deal of data. I solved this by realizing the data never changed and redundantly applying the calculation at write time. GLength, LineStringFromWKB, LineString, and GeomFromText do what you desire:

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(255) DEFAULT NULL,
  `device` varchar(255) DEFAULT NULL,
  `location` point DEFAULT NULL,
  `altitude` float DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `previd` INT UNSIGNED NULL,
  `prevdist` DOUBLE NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  INDEX `idx_user` (`user`),
  INDEX `idx_device` (`device`),
  INDEX `location` (`location`),
  INDEX `idx_time` (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `locations` SET 
`time` = NOW(), `location`= GeomFromText('POINT(@LAT @LONG)'), `user`= @USER,
`device`= @DEVICE, `altitude`= @ALT,
previd=(SELECT id FROM `locations` AS lc WHERE
  lc.`user`= @USER AND
  lc.`device`= @DEVICE AND
  lc.time < NOW()
  ORDER BY lc.time DESC LIMIT 1),
prevdist=IFNULL(ROUND(GLength(LineStringFromWKB(LineString(GeomFromText('POINT(@LAT @LONG)'), (SELECT location FROM `locations` AS lc WHERE
  lc.`user`= @USER AND
  lc.`device`= @DEVICE AND
  lc.time < NOW() ORDER BY lc.time DESC LIMIT 1)
)))*110400), 0);

Wednesday 15th June 2016 3:46 am

Back to User Chris Petersn - Stack Overflow blog