0

I realise this might be an inefficient query, but I'm trying to grab latitude and longitude values from the post_meta table that are connected to posts to figure out the proximity of the posts to a specific latitude and longitude.

The pinpoint location is 36.555555, 139.731111

I've put together the following query, (based on the post here) but I'm having trouble debugging it.

SELECT ID, (
          6371 * acos (
              cos ( radians(36.555555 )
              * cos( radians( CAST(latitude.meta_value AS DECIMAL) ) )
              * cos( radians( CAST(longitude.meta_value AS DECIMAL)) - radians(139.731111) )
              + sin ( radians(36.555555 )                       
              * sin( radians( CAST(latitude.meta_value AS DECIMAL) ) )
             )
          ) AS distance
         FROM $wpdb->posts
         INNER JOIN $wpdb->postmeta latitude
             ON (ID = latitude.post_id AND latitude.meta_key = 'place_latitude')
         INNER JOIN $wpdb->postmeta longitude
             ON (ID = longitude.post_id AND longitude.meta_key = 'place_longitude')
         HAVING distance < 20
         ORDER BY distance
         LIMIT 0,6

The error I'm getting is the standard

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 'FROM wp_posts INNER JOIN wp_postmeta latitude ON (ID = latitude.post_id' at line 10

I'm not sure if the problem is in my join logic or in the distance calculation formula. The reason I'm using CAST is because I thought MySQL might have an issue with running maths functions on a string type. With or without CAST, the error is the same.

Any guidance appreciated!

0

Oh dear, that was extremely obvious - there was a missing closing bracket!
Here is the working query.

SELECT ID, (
     6371 * acos (
     cos ( radians( 35.665833 ) )
     * cos( radians( latitude.meta_value ) )
     * cos( radians( longitude.meta_value ) - radians( 139.731111 ) )
     + sin ( radians(35.665833 ) )                       
     * sin( radians( latitude.meta_value ) )
    )
    ) AS distance
FROM $wpdb->posts
INNER JOIN $wpdb->postmeta latitude
    ON (ID = latitude.post_id AND latitude.meta_key = 'place_latitude')
INNER JOIN $wpdb->postmeta longitude
    ON (ID = longitude.post_id AND longitude.meta_key = 'place_longitude')
HAVING distance < 30
ORDER BY distance
LIMIT 0,6;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.