Geographical proximity query using post_meta

Question

Geographical proximity query using post_meta

shadylane

2015年10月26日 22:30

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!

Topic mysql wpdb geo-data Wordpress

Category Web

shadylane · Accepted Answer · 2015年10月26日 07:01

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;

Geographical proximity query using post_meta

About