How to query SQL for coords nearest a point?

问题:

This is what i am trying to do:

From a form I GET the coordinates:

&usp-custom-20=45.492384716155605&usp-custom-19=9.206341950000024

The I query SQl to find all posts near those coordinates, since each posts have custom fields with lat and lng

Post 1:

usp-custom-19 = 45.492384716155605
usp-custom-20 = 9.206341950000024

Post 2:

usp-custom-19 = 45.512383456255605
usp-custom-20 = 9.326341933210024

Then I run this:

$posts = $wpdb->get_col( "SELECT m.post_id FROM $wpdb->postmeta m, $wpdb->postmeta n WHERE m.post_id=n.post_id AND m.meta_key='get_usp-custom-19' AND n.meta_key='get_usp-custom-20' AND (POW( CAST(m.meta_value AS DECIMAL(7,4)) - $lat, 2 ) + POW( COS(0.0175 * $lat) * ( CAST(n.meta_value AS DECIMAL(7,4)) - $ln ), 2 ) ) ) < $rad * $rad");
foreach($posts as $post) { 
    the_title();
}

But I get nothing.

This uses the Pythagorean theorem which is only valid on flat surfaces. The two points need to be close enough so that the triangle is essentially on a flat surface. (Otherwise, you need a "Great Circle" formula.) Also, there will be a problem if the two points straddle the opposite of the Prime Meridian - the meridian of 180 degrees - International Date Line.

回答1:

This is what I ended up with

$post_ids = $wpdb->get_col("SELECT j.post_id FROM $wpdb->postmeta j, $wpdb->postmeta k where j.post_id = k.post_id and j.meta_key='usp-custom-19' and k.meta_key='usp-custom-20' and ( 6371 * acos( cos( radians($lat) ) * cos( radians( j.meta_value ) ) * cos( radians( k.meta_value ) - radians($ln) ) + sin( radians($lat) ) * sin(radians(j.meta_value)) ) ) < 50");