Need expert's help to solve minor change in sp

Question

i have the following query to access the nearest locations around the given lat-lon. I followed Mr.Ollie's blog Nearest-location finder for MySQL to find nearest locations around given lat-long using haversine formula.

But due to lack of much knowledge in spatial data query i failed to execute it properly, so looking for an expert's advice to solve this.

Here is my query

  SELECT z.id,
       p.distance_unit
                * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                * COS(RADIANS(z.(x(property))))
                * COS(RADIANS(p.longpoint) - RADIANS(z.(y(property))))
                + SIN(RADIANS(p.latpoint))
                * SIN(RADIANS(z.(x(property)))))) AS distance_in_km
 FROM mytable AS z
 JOIN (   /* these are the query parameters */
       SELECT  12.00  AS latpoint,  77.00 AS longpoint,
               20.0 AS radius,      111.045 AS distance_unit
   ) AS p
 WHERE z.(x(property))
    BETWEEN p.latpoint  - (p.radius / p.distance_unit)
        AND p.latpoint  + (p.radius / p.distance_unit)
   AND z.(y(property)
    BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
        AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
 ORDER BY distance_in_km
 LIMIT 15;

when i run this query i'm getting error as

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(x(property))))                 * COS(RADIANS(p.longpoint) - RADIANS(z.(y(geo' at line 1

i also tried z.(GeomFromText(x.property)))

this is my table desc

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

select x(property) from mytable; //gives me lat 
select y(property) from mytable; //gives me lan

Where am i going wrong? is this the way to achieve this.?

Please suggest.

Answer 1

Finally i followed this link and ended with this. query is not optimized yet but working great.

here is my 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;

Answer 2

It seems to me that you are assuming that once you have selected z.id in the query, that this gives you you direct access to the x(property) and y(property)

(Aside - do those names really have parentheses in them?)

So to me it looks like you should replace things like

* COS(RADIANS(z.(x(property))))

with something like

* COS(RADIANS( select x(property) from mytable where id = z.id ))

However on further thinking about it, I think that your mytable doesn't have the required structure. From looking at the link, I believe that your mytable should have a structure more like:

+-----------+----------------+
| Field     |    Type        |
+-----------+----------------+
|  id       |        Int(10) |
| latitude  |        Float   |
| longitude |        Float   |
+-----------+----------------+

So that you can do something like

* COS(RADIANS(z.latitude))

NOTE

The above was based on me not understanding that MySQL supports spatial data types (for which I have no idea how to use)

Update

I just did some googling to understand the spatial types and found this:

How do you use MySQL spatial queries to find all records in X radius? [closed]

which suggests that you can't do what you want to do with spatial data types in mysql. Which thus brings you back to using a non-optimal way of storing data in mutable

However in re-reading that link, the comments to the answer suggest that you may now be able to use spatial data types. (I told you I didn't have a clue here) This would mean replacing the query code with things like ST_Distance(g1,g2), which effectively means totally rewriting the example.

To put it another way

The example you gave presumes that spatial data types and testing of geometries do not exist in MySQL. But now that they do exist, they make this set of example code irrelevant, and that you are in for a world of hurt if you try and combine the two forms of analysis.

update 2

There are three paths you can follow:

1. Deny that spatial data types exists in MySQL and use a table that has explicit columns for lat and long, and use the sample code as originally written on that blog.

2. Embrace the MySQL spatial data types (warts and all) and take a look at things like this answer https://stackoverflow.com/a/21231960/31326 that seem to do what you want directly with spatial data types, but as noted in that answer there are some caveats.

3. Use a spatial type to hold your data, and use a pre-query to extract lat and long before passing it into the original sample code.