Skip to content

Instantly share code, notes, and snippets.

@ChrisFlannagan

ChrisFlannagan/geodistance.php

Created October 13, 2017 16:52
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ChrisFlannagan/57f201293d8e833dafe0bfeb3792ae91 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ChrisFlannagan/57f201293d8e833dafe0bfeb3792ae91 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ChrisFlannagan created this gist Oct 13, 2017.
    166 changes: 166 additions & 0 deletions geodistance.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,166 @@
    <?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 );
    }

    }