Need correct MySQL query to list nearby posts

Unfortunately i can’t help you with that query. Maybe you can make things easier to create an extra custom table to store post locations in.

CREATE TABLE wp_post_locations (
	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	post_id BIGINT(20) UNSIGNED DEFAULT NULL,
	lat FLOAT( 10, 6 ) NOT NULL,
	lng FLOAT( 10, 6 ) NOT NULL,
	PRIMARY KEY  (id),
	UNIQUE KEY post_id (post_id)
)

You can then use the ‘posts_clauses’ filter to join this table.

/**
 * Posts clauses
 *
 * https://codex.www.ads-software.com/WordPress_Query_Vars
 * https://codex.www.ads-software.com/Custom_Queries
 *
 * @param array $pieces
 * @param WP_Query $query
 * @return string
 */
function custom_locations_posts_clauses( $pieces, $query ) {
	global $wpdb;

	// Fields
	$fields = ",
		location.id AS location_id,
		location.lat AS location_lat,
		location.lng AS location_lng
	";

	// Join
	$join = "
		LEFT JOIN
			$wpdb->post_locations AS location
				ON $wpdb->posts.ID = location.post_id
	";

	// Location
	$latitude  = $query->get( 'latitude' );
	$longitude = $query->get( 'longitude' );

	if ( ! empty( $latitude ) && ! empty( $longitude ) ) {
		$radius = 6371; // KM

		$latitude1 = $wpdb->prepare( "%f", $latitude );
		$latitude2 = 'location.lat';
		$longitude1 =  $wpdb->prepare( "%f", $longitude );
		$longitude2 = 'location.lng';

		$d = "$radius * ACOS( COS( RADIANS( $latitude1 ) ) * COS( RADIANS( $latitude2 ) ) * COS( RADIANS( $longitude1 ) - RADIANS( $longitude2 ) ) + SIN( RADIANS( $latitude1 ) ) * SIN( RADIANS( $latitude2 ) ) )";

		$fields .= ",
			$d AS distance
		";
	}

	$pieces['fields']  .= $fields;
	$pieces['join']    .= $join;

	return $pieces;
}

add_filter( 'posts_clauses', 'custom_locations_posts_clauses', 10, 2 );

You can now use a query like this:

$query = new WP_Query( array(
	'latitude'  => 53,
	'longitude' => 9,
) );

Good luck.

The query was generally correct but there was a mistake in it:

FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.id = pm1.post_id AND pm1.meta_key = '_pronamic_google_maps_latitude'
INNER JOIN wp_postmeta pm2 ON p.id = pm2.post_id AND pm1.meta_key = '_pronamic_google_maps_longitude'

In the second line I ask for pm1.meta_key again.
Correct is pm2.meta_key
In the meantime I have finalized the project. But thanks for helping !

Hi Remco,

Great plugin! But is there a ‘designated’ way to show geo-tagged posts in a wordpress-friendly way in-a-sort-order-by-distance from a given geo-coordinate?

I am trying to manage exactly that, – any help would be greatly appreciated. I currently use not a seperate table, i want to use the
‘standard’ Pronamic-Fields for longitude and latitude..

Currently I try to get things done with custom SQL-Queries like that:
(where 49.0, 8.4 are my coordinates, germany)


$querystr = "
SELECT
ACOS( SIN( RADIANS( 49 ) ) * SIN( RADIANS( pm1.meta_value ) ) + COS( RADIANS( 49 ) ) * COS( RADIANS( pm1.meta_value ) ) * COS( RADIANS( pm2.meta_value ) - RADIANS( 8.4 ) ) ) *3959 AS distance,
pm1.meta_value AS lat, pm2.meta_value AS lon,
p.*
FROM wp_posts p
INNER JOIN wp_term_relationships ON ( p.id = wp_term_relationships.object_id )

INNER JOIN wp_postmeta pm1 ON p.id = pm1.post_id AND pm1.meta_key = '_pronamic_google_maps_latitude'
INNER JOIN wp_postmeta pm2 ON p.id = pm2.post_id AND pm2.meta_key = '_pronamic_google_maps_longitude'

WHERE post_type = 'post'
AND post_status = 'publish'
AND ( wp_term_relationships.term_taxonomy_id IN ( $cat ) )
ORDER BY distance ASC
LIMIT 0 , 10
";

I would like to have some help – or I am the only one who wants that? Can’t believe that.. ??
It should give an example how to archive a “sort-by-distance-posts” category-blog.php (example) that follow the rules of wordpress standards, that is what I am missing here..

kind regards,
Ingo