$

The Perfect Query

·

Your “nearby” query is sampling the alphabet

“Show places within 10 km of this one” is a feature every travel or real-estate site gets asked for. On one project it shipped, passed review, looked fine on staging — and on production, with about three thousand geolocated places, it only ever suggested restaurants whose names started with A or B.

Nothing was wrong with the distance maths. The bug was in where the filtering happened. It’s a pattern I now look for in every WordPress code review, so here it is in detail.

The buggy version

function places_near( float $lat, float $lng, float $radius_km ): array {
	$posts = get_posts( array(
		'post_type'   => 'place',
		'numberposts' => 200,           // "should be plenty"
		'orderby'     => 'title',
		'order'       => 'ASC',
	) );

	$near = array();
	foreach ( $posts as $post ) {
		$d = haversine_km( $lat, $lng, (float) $post->lat, (float) $post->lng );
		if ( $d <= $radius_km ) {
			$near[] = array( 'id' => $post->ID, 'distance' => $d );
		}
	}
	return $near;
}

Read it as SQL and the problem is obvious: SELECT … ORDER BY post_title LIMIT 200, then filter by distance in PHP. The database returns the first 200 places alphabetically, from anywhere in the country, and PHP throws away the ones that are too far. With 150 places in the seed data, 200 was “everything”. With 3,000, it’s the A–B slice of the alphabet.

The general rule: a LIMIT must come after every filter that changes which rows qualify. If a filter runs in PHP, the limit has to run in PHP too — or, better, the filter has to move into the query.

Moving the filter into SQL: a bounding box

You can compute haversine in MySQL, but doing it on a postmeta value means a full scan with trigonometry per row, on a string column. The cheap trick is to ask the database a simpler question first: which points fall inside the square around my circle? That’s just two BETWEEN comparisons.

  • One degree of latitude is about 111 km everywhere, so Δlat = r / 111.
  • One degree of longitude shrinks with the cosine of latitude, so Δlng = r / (111 · cos(lat)). Clamp the cosine away from zero so the poles don’t divide by zero.
function places_near( float $lat, float $lng, float $radius_km, int $limit = 20 ): array {
	$d_lat = $radius_km / 111.0;
	$d_lng = $radius_km / ( 111.0 * max( 0.01, cos( deg2rad( $lat ) ) ) );

	$ids = get_posts( array(
		'post_type'      => 'place',
		'post_status'    => 'publish',
		'fields'         => 'ids',
		'posts_per_page' => 1000, // a safety cap, not a sampling limit
		'no_found_rows'  => true,
		'meta_query'     => array(
			array(
				'key'     => 'lat',
				'value'   => array( $lat - $d_lat, $lat + $d_lat ),
				'compare' => 'BETWEEN',
				'type'    => 'DECIMAL(10,6)',
			),
			array(
				'key'     => 'lng',
				'value'   => array( $lng - $d_lng, $lng + $d_lng ),
				'compare' => 'BETWEEN',
				'type'    => 'DECIMAL(10,6)',
			),
		),
	) );

	update_meta_cache( 'post', $ids ); // one query for all coordinates

	$near = array();
	foreach ( $ids as $id ) {
		$d = haversine_km( $lat, $lng, (float) get_post_meta( $id, 'lat', true ), (float) get_post_meta( $id, 'lng', true ) );
		if ( $d <= $radius_km ) { // the box's corners are outside the circle
			$near[] = array( 'id' => $id, 'distance' => $d );
		}
	}

	usort( $near, fn( $a, $b ) => $a['distance'] <=> $b['distance'] );
	return array_slice( $near, 0, $limit ); // the limit, finally, after every filter
}

Some notes on the details:

  • 'type' => 'DECIMAL(10,6)' is not optional. Without it, meta_value is compared as a string, and '9.5' > '10.1'. Six decimal places is about 10 cm of precision — more than enough.
  • The exact check still runs in PHP, because a square is not a circle: its corners are about 41% further away than its edges. The box only has to be a superset.
  • Prime the meta cache yourself. With 'fields' => 'ids', WordPress skips its usual meta priming, so each get_post_meta() in the loop would be its own query. One update_meta_cache() call loads all of them at once.
  • The 1000 is a guard, not a sample. If a box ever returns 1000 rows, the radius is too big for this approach — log it rather than silently truncating again.

What about indexes?

Two postmeta joins with a CAST can’t use an index on meta_value, so this is still a scan of the place rows — but a scan with cheap comparisons instead of trigonometry, returning tens of rows instead of thousands to PHP. For a few thousand posts that runs in single-digit milliseconds and the result can be cached per place in a transient, invalidated when any place is saved.

If you’re at hundreds of thousands of points, stop using postmeta for coordinates: put them in a custom table with DECIMAL columns and a composite index on (lat, lng), or a POINT column with a spatial index and ST_Distance_Sphere(). The bounding-box idea stays the same; only the storage changes.

The test that would have caught it

The original bug survived because test data was small and alphabetically convenient. The useful integration test is boring: create 300 places named “A…” far away, and one place named “Z…” 500 metres from the origin. Assert the Z place is returned. It fails against the first version and passes against the second — and it encodes the rule, not the implementation.

public function test_finds_near_place_regardless_of_title(): void {
	for ( $i = 0; $i < 300; $i++ ) {
		$this->make_place( 'Aaa ' . $i, 50.0, 20.0 ); // ~400 km away
	}
	$near = $this->make_place( 'Zzz', 46.004, 15.0 );

	$ids = wp_list_pluck( places_near( 46.0, 15.0, 10 ), 'id' );

	$this->assertContains( $near, $ids );
}