SQL语法错误-Haversine公式 [英] SQL syntax error - Haversine formula

问题描述

我正在尝试获取最近的地方使用 Haversine公式

我下面的表格结构

帖子

+--------------+
| Field        |
+--------------+
| ID           |
| post_author  |
| post_title   | 
| post_type    | 
+--------------+

后修饰

+--------------+
| Field        |
+--------------+
| meta_id      |
| post_id      |
| meta_key     |
| meta_value   |
+--------------+

并具有meta_key值latitude和longitude

请参阅答案 a>对于我用来获取经度和纬度的SQL.

See the answer to my previous question for the SQL I've used to get the latitude and longitude.

SELECT p.ID, 
  p.post_title, 
  p.post_author,
  max(case when pm.meta_key='latitude' then pm.meta_value end) latitude,
  max(case when pm.meta_key='longitude' then pm.meta_value end) longitude
FROM `wp_posts` p
LEFT JOIN `wp_postmeta` pm
  on p.ID=pm.post_id 
WHERE p.post_type='place' 
  AND (pm.meta_key='latitude' OR pm.meta_key='longitude') 
GROUP BY p.ID, p.post_title, p.post_author
ORDER BY p.ID ASC

现在，我想将上述查询合并到 answer对于这个问题

Now I want to incorporate above query into answer for this question

SELECT item1, item2, 
    ( 3959 * acos( cos( radians(37) ) 
                   * cos( radians( lat ) ) 
                   * cos( radians( lng ) 
                       - radians(-122) ) 
                   + sin( radians(37) ) 
                   * sin( radians( lat ) ) 
                 )
   ) AS distance 
FROM geocodeTable 
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

以下是通过组合查询

SELECT ID, 
  post_title, 
  post_author,
  max(case when meta_key='latitude' then meta_value end) latitude,
  max(case when meta_key='longitude' then meta_value end) longitude,
  ( 3959 * acos( cos( radians(18.204540500000) ) 
                   * cos( radians( latitude ) ) 
                   * cos( radians( longitude ) 
                       - radians(-66.450958500000) ) 
                   + sin( radians(18.204540500000 ) 
                   * sin( radians( latitude ) ) 
                 )
   ) AS distance 
FROM `wp_posts` 
LEFT JOIN `wp_postmeta` 
  on ID=post_id 
WHERE post_type='place' 
  AND (meta_key='latitude' OR meta_key='longitude') 
GROUP BY ID, post_title, post_author
ORDER BY ID ASC

但这会产生语法错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS distance FROM `wp_posts` LEFT JOIN `wp_postmeta` on ID=post_id WHERE po' at line 13

推荐答案

您缺少第一个sin()

( 3959 * acos( cos( radians(18.204540500000) ) 
                   * cos( radians( latitude ) ) 
                   * cos( radians( longitude ) 
                       - radians(-66.450958500000) ) 
                   + sin( radians(18.204540500000 ) ) /* <--- here */
                   * sin( radians( latitude ) ) 
              )
 ) AS distance 

尽管很难从视觉上发现它，但我通过将您的代码复制到支持大括号匹配的文本编辑器中发现了这一点.强烈建议使用一种，如果不是用于查询开发和测试，则至少要用于调试.

Though it is difficult to spot visually, I found this by copying your code into a text editor that supports brace matching. It is highly recommended to use one, if not for query development and testing, then at least for debugging.

这篇关于SQL语法错误-Haversine公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！