dson - 7 months ago 65

SQL 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`

`POINT(10.1234 50.12356)`

as a result of latitude and longitude.

Now I have

In the given link writer

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

or

- is there any other ways to directly apply haversine formula on my

table.?

i am using MySql server version

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.

Source (Stackoverflow)

Comments