4

I'm trying to implement a geospacial search in a php application. Currently, I'm using the following query to find points within 10km of a given latitude and longitude:

SELECT * FROM (
      SELECT *,
             (6378.1 * ACOS(
                  COS(RADIANS(48.856614)) * COS(RADIANS(latitude))
                * COS(RADIANS(2.3522219) - RADIANS(longitude))
                + SIN(RADIANS(48.856614))
                * SIN(RADIANS(latitude)))
             ) AS distance
      FROM `destinations_info`
      WHERE latitude BETWEEN 48.856614  - (10 / 69)
               AND 48.856614  + (10 / 69)
        AND longitude BETWEEN 2.3522219 - (10 / (69 * COS(RADIANS(48.856614))))
               AND 2.3522219 + (10 / (69 * COS(RADIANS(48.856614))))
      ) d
WHERE distance <= 10
LIMIT 1

This works fine as long I don't search for the exact latitude and longitude that is stored into the POI's table.

So for example if I have in my poi table the following entry

id     name     latitude    longitude
1      Paris    48.856614   2.3522219000000177

And I call the query with lat = 48.856614 and long = 2.3522219000000177 I won't get any results. As far as I see this happens because the following operation returns NULL and not 0 in mysql:

SELECT (6378.1 * acos(cos(radians(48.856614)) * cos(radians(48.856614)) * cos( radians(2.3522219) - radians(2.3522219)) + sin(radians(48.856614)) * sin(radians(48.856614))))

Running the same in Chrome Calculator I get 0 rad.

Is there something wrong in the query or do I need to include the NULL results as well using "OR IS NULL"

lp_
  • 1,158
  • 1
  • 14
  • 21
Tudor Ravoiu
  • 2,130
  • 8
  • 35
  • 56

2 Answers2

4

IFNULL( ... ),0) AS Distance will also help you to get around the error.

Frédéric Peters
  • 56
  • 4
  • +1 - I was having this problem when the lat/lng specified in the query matches EXACTLY the lat/lng of the record in the database I want to retrieve. Good, elegant solution. – phirschybar Oct 02 '18 at 14:39
1

The problem is that acos is invoked with a value which is not in the range of -1 and 1, so it returns null.

SELECT cos(radians(48.856614)) * cos(radians(48.856614)) * cos( radians(2.3522219) - radians(2.3522219)) + sin(radians(48.856614)) * sin(radians(48.856614))

1.0000000000000002

It seems to be some sort of rounding issue.

Since, arc cos is defined only between -1 and 1, you probably should pre-check its parameter or be prepared that it can return null if something is wrong with the calculation.

lp_
  • 1,158
  • 1
  • 14
  • 21
  • i noticed now that my latitude, longitude columns are of type varchar 255 so and not float so that it allows to store more than 8 numbers after decimal. changed the type to float and now it works but I don't really know how safe/accurate it is. – Tudor Ravoiu Jan 31 '15 at 19:26
  • the value stored in varchar gets converted, so that's not the problem. until you don't need to compare float values, I think that is fine here. but be aware that it won't solve the problem that acos may return null. chances are very low, but you already have one in your example. so the same can happen again. – lp_ Jan 31 '15 at 22:55