Geolocation SQL query not finding exact location?

In your first query, I believe you've inverted the longitudes in the subtraction. The Spherical Law of Cosines is.

In your first query, I believe you've inverted the longitudes in the subtraction. The Spherical Law of Cosines is: d = acos(sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(long2? Long1))*R If lat1 is substituted with tblcity.

Latitude, long1 must be substituted with tblcity.longitude. I think you've accidentally substituted long2 in your query. Does this one work better?

SELECT tblcity. City, tblcity. Latitude, tblcity.

Longitude, truncate((degrees(acos( sin(radians(tblcity. Latitude)) * sin(radians(45.266708)) + cos(radians(tblcity. Latitude)) * cos(radians(45.266708)) * cos(radians(-73.616257 - tblcity.

Longitude) ) ) ) * 69.09*1.6),1) as distance FROM tblcity HAVING distance.

Thanks Mike, I tried the query you suggested and I come up with the same result, the town of Saint-Rémi is still omitted. I will not claim that I knew the math behind the query, I had to Google it in order to create it, but your equation above does help me understand the math behind the query a little better. Thanks!

– Iridium52 Apr 23 '10 at 0:54 I was lazy before, and didn't actually test it. I was surprised to hear that it failed, though, so I tried it now. Admittedly, you made it easy by providing the SQL input to create the table.

Anyway, it worked! Not sure what to say now... ;o) – Mike Pelley Apr 23 '10 at 1:13 Hmmmm. Your original query also worked for me, even though the longitudes were reversed, which makes sense since the subtraction will come out to zero.

Now I'm at a loss as to why it's not working for you. Could you replace the "HAVING distance – Mike Pelley Apr 23 '10 at 1:19 Thanks Mike, I really appreciate the help. Your suggestion to change HAVING to "city = 'Saint-Rémi'" is a great idea and yielded an interesting result, the distance returned for the city of Saint-Rémi was NULL.

I received a NULL distance using both my queries and your corrected query. I even did the math using an online scientific calculator and came up with the answer of 0. I'm really stumped on this one, could this possibly be a problem with my version or installation of mysql?

– Iridium52 Apr 23 '10 at 13:30 I'm not sure, but that would be the logical conclusion since it works on mine (ver. 5.1.37). I'd suggest changing the distance equation to be a single term, then iteratively add terms and run the query.

At some point, distance will become null and you'll know what's causing the problem. – Mike Pelley Apr 23 '10 at 13:51.

You are using the "spherical law of cosines" formula, which is susceptable to rounding error at small distances (like zero! ) -- see this discussion. The long expression that is fed into acos() is evaluating to slightly more than 1.0, which is out of bounds.

Here's the problem illustrated using Python to do the calculations: >>> from math import sin, cos, acos, radians >>> lat = radians(45.266708) >>> long_expression = sin(lat) * sin(lat) + cos(lat) * cos(lat) * cos(0.0) >>> repr(long_expression) '1.0000000000000002' >>> acos(long_expression) Traceback (most recent call last): File "", line 1, in ValueError: math domain error >>> It seems that MySQL is substituting NULL instead of raising an exception. I know little about MySQL, but you should be able to overcome that by doing something like ifnull(acos(long_expression), 0.0) or coalesce(acos(long_expression), 0.0). Alternatively you could use the haversine formula, which shifts the rounding problem from your door-step to the opposite side of the earth.

Update: I've tested using that formula in Python to calculate the should-be-zero distance between a point and the same point, for each of the 37582 unique (lat, lon) 2-tuples in a file of US zip codes. Of these: 31591 (84.1%) produced a zero distance 4244 (11.3%) produced a distance of 9.5 cm. 831 (2.2%) produced a distance of 13.4 cm.916 (2.4%) produced a "cos" value of 1.0000000000000002 which would cause an exception in acos() if not detected and avoided.

It appears that explicit testing for lat1 = lat2 and lon1 = lon2 and avoiding the formula in that case (just use zero) might be a good idea -- it would give a consistent answer and avoid puzzlement.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.