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