dson - 1 year ago 144

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

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

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**