SQL syntax error - Haversine formula

Question

I'm trying to get nearest places from a WordPress database using Haversine formula

My table structure below

posts

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

postmeta

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

and have records with meta_key values latitude and longitude

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

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;

Below is by combined query

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

But this yields syntax error

#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

Michael Berkowski · Accepted Answer · 2012-11-06T12:14:35.837

4

You are missing a closing ) for the first 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.

edited Nov 06 '12 at 12:14

answered Nov 06 '12 at 12:09

Michael Berkowski

267,341
46
444
390

I was using MySQL Workbench, but it does not have brace matching :-( – Mithun Sreedharan Nov 06 '12 at 12:28
@mithun does this query provide you result. – Jan 06 '15 at 15:17

score 0 · Answer 2 · answered Nov 06 '12 at 12:53

Try this:

SELECT *, ( 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 
(SELECT ID, 
  post_title, 
  post_author,
  MAX(CASE WHEN meta_key='geo_latitude' THEN meta_value END) latitude,
  MAX(CASE WHEN meta_key='geo_longitude' THEN meta_value END) longitude
FROM `wp_posts` 
LEFT JOIN `wp_postmeta` 
  ON ID=post_id 
WHERE post_type='place' 
  AND (meta_key='geo_latitude' OR meta_key='geo_longitude') 
GROUP BY ID, post_title, post_author
ORDER BY ID ASC) AS A

SQL syntax error - Haversine formula

2 Answers2

Linked