MySQL WordPress查询返回某些记录的零距离 [英] MySQL WordPress Query Returning a Distance of Zero for Some Records

问题描述

我写了一个针对WordPress数据库的查询，该查询选择了10个最接近的餐厅(按距离排序). 出于某种原因，前三个的距离为零，我一生都无法找出原因.

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.

有什么想法吗?

这是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;

结果如下:

    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 

推荐答案

您发现的第一行正确为零，因为两点相同.

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

第二行产生0距离，但应为2厘米左右.该错误是由您使用的公式引起的.小距离可能会引起问题. Google的余弦球形定律".

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".

第三行的点与第二行的点相同.看来您需要研究(1)短期修复:消除零和几乎为零的现象，例如HAVING distance <= 50 and distance > 0.001(2)长期修复:研究为什么您拥有重复的数据点或足够接近的重复数据点.验证您的数据...例如在将新餐厅添加到数据库之前，请使用距离公式检查它是否已经存在.

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.

这篇关于MySQL WordPress查询返回某些记录的零距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！