Skip to content

Instantly share code, notes, and snippets.

@ChrisFlannagan
Created October 13, 2017 16:52
Show Gist options
  • Save ChrisFlannagan/57f201293d8e833dafe0bfeb3792ae91 to your computer and use it in GitHub Desktop.
Save ChrisFlannagan/57f201293d8e833dafe0bfeb3792ae91 to your computer and use it in GitHub Desktop.
Calculate distance between to coordinates of latitude and longitude using the WP REST API and return posts ordered by distance from user's coordinates
<?php
/**
* Heavily borrowed from: http://xplus3.net/2010/08/08/filtering-on-a-non-standard-database-field-with-wordpress/
**/
class CoordinatesTable extends DB {
protected $db_option = "coordinates_db";
protected $db_version = 2;
protected $id_field = 'pid';
protected $columns = [
'pid' => '%d',
'latitude' => '%f',
'longitude' => '%f',
];
/**
* CoordinatesTable constructor.
*
* Create table if needed
*/
private function __construct() {
global $wpdb;
$this->table = $wpdb->prefix . 'coordinates';
if ( $this->update_required() ) {
$this->run_updates();
}
}
/**
* Create table with indexes for lat/lon
*/
protected function create_table() {
global $wpdb;
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
foreach ( array( 'providers' ) as $table ) {
$sql = "CREATE TABLE {$this->table} (
pid bigint(20) unsigned NOT NULL,
latitude double,
longitude double,
UNIQUE KEY pid (pid),
INDEX latitude (latitude),
INDEX longitude (longitude)
) /*!40100 DEFAULT CHARACTER SET utf8 */;";
dbDelta( $sql );
}
}
/**
* @param $fields
*
* @return string
*
* To be hooked into 'post_fields' filter
*/
public function query_posts_fields( $fields ) {
global $wpdb;
global $wp_query;
list( $lat, $lng ) = explode(',', $wp_query->query_vars['latlong'] );
$fields .= sprintf(",
( 6371000
* acos( cos( radians( %f ) )
* cos( radians( {$this->table}.latitude ) )
* cos( radians( {$this->table}.longitude ) - radians( %f ) )
+ sin( radians( %f ) )
* sin( radians({$this->table}.latitude ) ) ) ) AS distance", $lat, $lng, $lat );
return $fields;
}
/**
* @param $join
*
* @return string
*
* To be hooked into 'posts_join' filter
*/
public function query_posts_join( $join ) {
global $wpdb;
$join = " LEFT JOIN {$this->table} ON {$wpdb->posts}.ID = {$this->table}.pid" . $join;
return $join;
}
/**
* @param $orderby
*
* @return string
*/
public function query_posts_orderby( $orderby ) {
$myorder = "distance ASC";
if ( $orderby ) {
$myorder .= ',' . $orderby;
}
return $myorder; // return the default if we haven't changed it
}
/**
* @param $groupby
*
* @return string
*
* To be hooked into 'posts_groupby' filter. This groups by the distance and orders closest in meters. The limit
* is necessary for max distance to get properly ordered results.
*/
public function query_posts_groupby( $groupby ) {
global $wpdb;
if ( null === $max_distance = get_field( Options::GEO_MAX_DISTANCE, 'option' ) ) {
$max_distance = Options::GEO_MAX_DISTANCE_DEFAULT;
}
$groupby = $wpdb->prepare( " {$wpdb->posts}.ID HAVING distance < %d", $max_distance );
return $groupby;
}
/**
* Inject our query's sql string
*/
public function query_filters() {
add_filter( 'posts_clauses', function( $clauses ) {
$clauses['fields'] = $this->query_posts_fields( $clauses['fields'] );
$clauses['join'] = $this->query_posts_join( $clauses['join'] );
$clauses['orderby'] = $this->query_posts_orderby( $clauses['orderby'] );
$clauses['groupby'] = $this->query_posts_groupby( $clauses['groupby'] );
return $clauses;
} );
}
public function save_coordinates( $post_id, $lat, $long ) {
$this->add( [
'pid' => $post_id,
'latitude' => $lat,
'longitude' => $long,
] );
}
public function delete_coordinates( $post_id ) {
$this->remove( [
'pid' => $post_id,
] );
}
public function get_latitude( $post_id ) {
return $this->get( 'latitude', [ 'pid' => $post_id, ], 1 );
}
public function get_longitude( $post_id ) {
return $this->get( 'longitude', [ 'pid' => $post_id, ], 1 );
}
}
@spezias
Copy link

spezias commented Nov 20, 2020

Where should the file be saved?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment