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 – Vikram fz16 Apr 10 at 7:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.