MySQL WordPress Query Returning a Distance of Zero

Question

I wrote a query against a WordPress Database that selects the 10 closest restaurants, ordered by distance. For some reason, the first three have a distance of zero and I can't for the life of me figure out why.

Any ideas?

Here is the SQL:

    SELECT pm1.post_id, (6371 * acos( cos( radians(51.507351) ) 
                   * cos( radians( pm1.meta_value) ) 
                   * cos( radians(pm2.meta_value) - radians(-0.127758)) + sin(radians(51.507351)) 
                   * sin( radians(pm1.meta_value)))
                 ) AS distance,
           pm1.meta_value AS lat,
           pm2.meta_value as lon
           FROM wp_postmeta AS pm1, 
                wp_postmeta AS pm2,
                wp_posts    AS p 
           WHERE pm1.meta_key = 'latitude' AND pm2.meta_key = 'longitude'
           AND pm1.post_id = pm2.post_id
           AND pm1.post_id = p.id
           AND p.post_status = 'publish' 
           HAVING distance <= 50
           ORDER BY distance ASC
           LIMIT 10;

And here are the results:

    post_id  distance              lat                 lon 
    50098    0                     51.507351           -0.127758 
    528181   0                     51.5073509          -0.12775829999998223 
    528183   0                     51.5073509          -0.12775829999998223 
    2193     0.036628788957855186  51.507207           -0.127282 
    2161     0.0605752670106375    51.507312           -0.128631 
    11292    0.18852496145820602   51.5082275731907    -0.130089685072323 
    2157     0.2315552293897031    51.5093632362       -0.12689665526 
    2088     0.2942221944412102    51.5082070105091    -0.12373537807585 
    527740   0.3184070735907211    51.5101623          -0.1286324999999806 
    7075     0.3759866662527897    51.5103             -0.1251 

Answer 1

The first row is as you have discovered is correctly zero because the two points are identical.

The second row produces 0 distance but it should be about 2 cm. The error is caused by the formula that you are using; it can cause problems with small distances. Google "spherical law of cosines".

The third row's point is identical to that of the second row. Looks like you need to investigate (1) short term fix: eliminate zeroes and almost-zeroes e.g. HAVING distance <= 50 and distance > 0.001 (2) longer term fix: investigate why you've got data points that are duplicates or close enough to be duplicates. Validate your data ... e.g. before you add a new restaurant to the database, check using your distance formula to see if it's already there.