StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
3275357
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2010-07-18T11:42:05.770
FavoriteCount
0
LastActivityDate
2010-07-19T11:59:04.363
LastEditDate
2010-07-19T11:59:04.363
LastEditorUserId
84270
OwnerUserId
84270
ParentId
2695365
PostTypeId
2
Score
2
ViewCount
0
LastEditorDisplayName
text
Body
You are using the "spherical law of cosines" formula, which is susceptable to rounding error at small distances (like zero!) -- see <a href="http://en.wikipedia.org/wiki/Haversine_formula" rel="nofollow noreferrer">this discussion</a>. 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: <pre><code>>>> 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 "<stdin>", line 1, in <module> ValueError: math domain error >>> </code></pre> It seems that MySQL is substituting <code>NULL</code> instead of raising an exception. I know little about MySQL, but you should be able to overcome that by doing something like <code>ifnull(acos(long_expression), 0.0)</code> or <code>coalesce(acos(long_expression), 0.0)</code>. 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 <a href="http://www.boutell.com/zipcodes/" rel="nofollow noreferrer">file of US zip codes</a>. Of these: <ul> <li>31591 (84.1%) produced a zero distance</li> <li>4244 (11.3%) produced a distance of 9.5 cm.</li> <li>831 (2.2%) produced a distance of 13.4 cm.</li> <li>916 (2.4%) produced a "cos" value of 1.0000000000000002 which would cause an exception in acos() if not detected and avoided.</li> </ul> It appears that explicit testing for <code>lat1 = lat2 and lon1 = lon2</code> and avoiding the formula in that case (just use zero) might be a good idea -- it would give a consistent answer and avoid puzzlement.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POGeolocation SQL query not finding exact location
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USJohn Machin
UserOwnerUserId
1. USJohn Machin
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.