Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Possible Duplicate:
How to query posts based on lat-lng coordinate as post meta?

I want to create a $wpdb query that select the closest places (custom post type) near the current coordinates ($lat and $long) but I'm not able to get two custom field values simoultaneously (wpfc-latitude and wpfc-longitude)...

how can i change my query to get also the longitude?

SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->terms.name, $wpdb->postmeta.meta_value AS latitude
FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->postmeta.meta_key = 'wpcf-latitude'
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'places'
AND $wpdb->term_taxonomy.taxonomy = 'countries'

UPDATE - THE FIXED QUERY

Thanks to s_ha_dum I succesfully have both values and I can now calculate the distance and order my result. My final query is:

SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->terms.name, wpcflat.meta_value AS latitude, wpcflong.meta_value AS longitude,
    6371 * 2 * ASIN ( SQRT (POWER(SIN(($lat - wpcflat.meta_value)*pi()/180 / 2),2) + COS($lat * pi()/180) * COS(wpcflat.meta_value *pi()/180) * POWER(SIN(($long - wpcflong.meta_value) *pi()/180 / 2), 2) ) ) as distance
FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta as wpcflong ON ($wpdb->posts.ID = wpcflong.post_id)
    LEFT JOIN $wpdb->postmeta as wpcflat ON ($wpdb->posts.ID = wpcflat.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->posts.ID NOT IN ($post->ID)
AND wpcflat.meta_key = 'wpcf-latitude'
AND wpcflong.meta_key = 'wpcf-longitude'
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'places'
AND $wpdb->term_taxonomy.taxonomy = 'countries'
ORDER BY distance
LIMIT 20

Is it a heavy query to load? Do you know if is there a way to optimize it?

share|improve this question

marked as duplicate by kaiser, toscho Oct 17 '12 at 9:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
I marked it as a duplicate. Please take a look at the answer from @woony. –  kaiser Oct 17 '12 at 2:47

1 Answer 1

up vote 2 down vote accepted

You are going to have to JOIN twice on the postmeta table. Something like:

SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->terms.name, wpcflat.meta_value AS latitude, wpcflong.meta_value AS longitude
FROM $wpdb->posts
/* First Join */
LEFT JOIN $wpdb->postmeta as wpcflong ON $wpdb->posts.ID = wpcflong.post_id
/* Second Join */
LEFT JOIN $wpdb->postmeta as wpcflat ON $wpdb->posts.ID = wpcflat.post_id
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE 1
/* Meta queries for lat and long */
AND wpcflat.meta_key = 'wpcf-latitude'
AND wpcflong.meta_key = 'wpcf-longitude'
/* I don't know what criteria you are using to calculate 'nearest' 
but you'd need more conditions here for that */
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'places'
AND $wpdb->term_taxonomy.taxonomy = 'countries'

As noted in my SQL comments, I don't know what you are using as criteria for 'closest' but that code will get you both meta values to play with. My database doesn't have lat and long data so I can't test that strictly speaking but it is correct in principle, barring a typo or two or other stupid mistake.

Subqueries are another possible solution but they do not perform well when you try to sort or filter on them.

share|improve this answer
1  
Great, thanks! Yes, not that I have both the latitude and longitude values I can calculate the distance and order my resultss. I'll edit my answer including my final query. Do you think that it's a heavy query to run? Do you know if there is a way to optimize it? Thanks again! –  Stefano Oct 17 '12 at 7:58
1  
You should be able to run much heavier queries than that :) It should be fine. When you get it working, use EXPLAIN to check its performance. –  s_ha_dum Oct 17 '12 at 13:21

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