dson - 2 years ago 155
SQL Question

# MySql spatial data query for finding near locations using haversine formula

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download