dson dson - 7 months ago 65
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

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.

Comments