How to fetch nearest node based on geofield?

Question

How to fetch nearest node based on location geofield,i want to fetch nearest node based on longitude and latitude of my current location ,for this i have installed and configured geolocation field module also created view enabled all settings,but its not giving desired result.

I have followed this tutorial https://www.drupal.org/docs/8/modules/geolocation-field/how-to-create-a-nearest-node-view-sorting-by-proximity

Can we fetch the data directly from database by something like below example of php in drupal .

set @orig_lat=122.4058; set @orig_lon=37.7907;
set @dist=10;

SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2), 2) +  COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) *  POWER(SIN((@orig_lon – dest.lon) * pi()/180 / 2), 2) )) as  distance
FROM hotels dest 
having distance < @dist
ORDER BY distance limit 10

By using this query i will try to alter views query for getting desired result

Answer 1

Something like this should do the trick to implement haversine :

/**
 * Implements hook_views_pre_execute().
 */
function YOURMODULE_views_pre_execute(\Drupal\views\ViewExecutable $view) {
//...
    $view->build_info['query']->join('YOUR_FIELD_LATLNG', '...', '...');
    $view->build_info['query']->addExpression(sprintf('3956 * 2 * 
                                            ASIN(
                                                SQRT( 
                                                    POWER(
                                                        SIN((%s - TABLE.YOUR_FIELD_LATLNG_LAT)*pi()/180/2)
                                                    ,2)
                                                    +COS(
                                                        %s*pi()/180 
                                                    )
                                                    *COS(
                                                        TABLE.YOUR_FIELD_LATLNG_LAT*pi()/180
                                                    )
                                                    *POWER(
                                                        SIN((%s-TABLE.YOUR_FIELD_LATLNG_LNG)*pi()/180/2)
                                                    ,2)
                                                )
                                            ) * 1.60934', $lat, $lat, $lon), 'kilometers');
}

---

edit nearest :

$view->build_info['query']->orderBy('kilometers', 'ASC');
$view->build_info['query']->range(0, 1);

---

Note there is a contribution with views on geofield : https://www.drupal.org/project/geofield/issues/3012750