MySql spatial data query for finding near locations using haversine formula

Question

Newbie here have optimum knowledge in mysql, but stuck on spatial query.

I peeked into many spatial data related questions for finding near locations around given lat/lon , but ended up with no proper result because of my different spatial table format (please provide link if there is a question already. I dunno much about spatial data query).

I have a table where we stored lat/lon as Point data type of Geometry type.!(Sorry if I'm wrong here)

desc of my table is as follows:

+-----------+----------------+
| Field     |    Type        |
+-----------+----------------+
|  id       |        Int(10) |
| property  |       geometry |
+-----------+----------------+

when i queried for viewing property data using select astext(property) from mytable query, it will return list of

POINT(10.1234 50.12356)

as a result of latitude and longitude.

Now I have haversine formulae for calculating nearest locations using mysql.

In the given link writer Mr.Ollie queried directly on columns latitude and longitude but in my problem latitude and longitude are stored as POINT data-type.

For achieving nearest locations

do i need to first extract points from table and apply query on that

(i tried select X(property),Y(property) from mytable; to get lat/long values.)

or

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

i am using MySql server version 5.5

Please suggest me how to do it. Thanks in advance for all.

Find separate thread here

..in the **desc** of your table, you have `property => geometry` .. but then in your query, you have `select X(cordinates) .. from property` .. already, that much doesn't make sense. In the least, it seems you should have `select X(property) .. from THE_UNSTATED_TABLE_NAME` ..no? — elrobis, Jun 16 '14 at 16:38
@elrobis yes you are right. that was my mistake. i apologies editing my question. — Aparichith, Jun 17 '14 at 05:08
Previously you mentioned an error message--are you still getting an error? — elrobis, Jun 17 '14 at 13:13
Also, just to be clear, the X/Y values correspond to LONG/LAT, and *not* LAT/LONG which is a common mistake. It might be helpful to think of your query as `SELECT X(property) as long, Y(property) as lat FROM mytable;` ..in other words, is it possible you're getting these values reversed? — elrobis, Jun 17 '14 at 13:17
@elrobis yeah, now i managed to get lat and lon separately using x(property) and y(property). — Aparichith, Jun 18 '14 at 02:26

score 2 · Accepted Answer · answered Jun 18 '14 at 12:10

After doing some studies ended with some descent Haversine query

     select id, ( 3959 * acos( cos( radians(12.91841) ) * cos( radians( y(property) ) ) * 
    cos( radians( x(property)) - radians(77.58631) ) + sin( radians(12.91841) ) * 
sin( radians(y(property) ) ) ) ) AS distance from mytable having distance < 10 order by distance limit 10

;

And please take a look at stored LAT an LON the way lat/lon stored in mytable is reverse.

score 1 · Answer 2 · answered Jun 15 '16 at 03:46

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

uh... lol. whatcha need THAT for?! Looks like some NSA cellphone triangulation for Pegasus2.0 XKeyscore PRISM RDS... lol... you need the users device, altitude, location... AND time? lol. sounds invasive. — Christian Žagarskas, Mar 29 '23 at 02:26

MySql spatial data query for finding near locations using haversine formula

2 Answers2