0

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

0

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

  • query not working for me – user52318 Apr 10 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy