WordPress and Haversine Formula

2 Views

I am trying to use the Haversine Formula inside WordPress, using a custom post type “stores” with custom fields holding the latitude, longitude and the street_address for display only. I have been using this tutorial as a guide.

Here is the query I am trying to worth with…

Read More
SELECT wp_posts.ID, 
       wp_posts.post_title, 
       pm1.meta_value as address, 
       pm2.meta_value as latitude, 
       pm3.meta_value as longitude, ( 6371 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin( radians( latitude ) ) ) ) AS distance   
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='street_address')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='latitude') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='longitude') 
WHERE wp_posts.post_type = 'stores' 
AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20

But this returns… 

Unknown column 'latitude' in 'field list'

I have also tried using a variation of the query here, but I get the same error regarding “Unknown column”.

Any advice greatly appreciated!

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. Try using original column names instead of new aliases in the formula like latitude =>pm2.meta_value and latitude=>pm3.meta_value

    SELECT wp_posts.ID, 
       wp_posts.post_title, 
       pm1.meta_value AS address, 
       pm2.meta_value AS latitude, 
       pm3.meta_value AS longitude, 
       ( 6371 * ACOS( COS( RADIANS(37) ) * COS( RADIANS( pm2.meta_value ) ) * COS( RADIANS( pm3.meta_value ) - RADIANS(-122) ) + SIN( RADIANS(37) ) * SIN( RADIANS( pm2.meta_value ) ) ) ) AS distance   
FROM wp_posts 
LEFT JOIN wp_postmeta AS pm1 ON (wp_posts.ID = pm1.post_id AND pm1.meta_key='street_address')
LEFT JOIN wp_postmeta AS pm2 ON (wp_posts.ID = pm2.post_id AND pm2.meta_key='latitude') 
LEFT JOIN wp_postmeta AS pm3 ON (wp_posts.ID = pm3.post_id AND pm3.meta_key='longitude') 
WHERE wp_posts.post_type = 'stores' 
AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20

    EDIT

    SELECT 
  wp_posts.ID,
  wp_posts.post_title,
  pm1.meta_value AS address,
  pm2.meta_value AS latitude,
  pm3.meta_value AS longitude,
  (
    6371 * ACOS(
      COS(RADIANS(37)) * COS(
        RADIANS(
          CASE
            WHEN pm2.meta_value = '' 
            THEN 0 
            WHEN pm2.meta_value IS NULL 
            THEN 0 
            ELSE pm2.meta_value 
          END
        )
      ) * COS(
        RADIANS(
          CASE
            WHEN pm3.meta_value = '' 
            THEN 0 
            WHEN pm3.meta_value IS NULL 
            THEN 0 
            ELSE pm3.meta_value 
          END
        ) - RADIANS(- 122)
      ) + SIN(RADIANS(37)) * SIN(
        RADIANS(
          CASE
            WHEN pm2.meta_value = '' 
            THEN 0 
            WHEN pm2.meta_value IS NULL 
            THEN 0 
            ELSE pm2.meta_value 
          END
        )
      )
    )
  ) AS distance 
FROM
  wp_posts 
  LEFT JOIN wp_postmeta AS pm1 
    ON (
      wp_posts.ID = pm1.post_id 
      AND pm1.meta_key = 'street_address'
    ) 
  LEFT JOIN wp_postmeta AS pm2 
    ON (
      wp_posts.ID = pm2.post_id 
      AND pm2.meta_key = 'latitude'
    ) 
  LEFT JOIN wp_postmeta AS pm3 
    ON (
      wp_posts.ID = pm3.post_id 
      AND pm3.meta_key = 'longitude'
    ) 
WHERE wp_posts.post_type = 'stores' 
  AND wp_posts.post_status = 'publish' 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0, 20