2

I am trying to work out if it is possible to write the following using Drupal's database abstraction layer. The tricky bit is the second field which is a function to calculate distance. Is it possible to include this calculation field (3959 * acos(cos(radians(:lat)) * cos(radians(field_bluescope_geolocation_lat)) * cos(radians(field_bluescope_geolocation_lng) - radians(:lng)) + sin(radians(:lat)) * sin(radians(field_bluescope_geolocation_lat)))) AS distance using the abstraction layer?

  $result = db_query_range('SELECT '
  . 'geo.entity_id, '
  . '(3959 * acos(cos(radians(:lat)) * cos(radians(field_bluescope_geolocation_lat)) * cos(radians(field_bluescope_geolocation_lng) - radians(:lng)) + sin(radians(:lat)) * sin(radians(field_bluescope_geolocation_lat)))) AS distance '
  . 'FROM '
  . 'node__field_bluescope_geolocation AS geo '
  . 'INNER JOIN '
  . 'node__field_bluescope_consumer_supp AS consumer_supp '
  . 'ON '
  . 'geo.entity_id = consumer_supp.entity_id '
  . 'WHERE '
  . 'geo.bundle = \'bluescope_business\' '
  . 'AND '
  . 'consumer_supp.field_bluescope_consumer_supp_target_id = 28 '
  . 'ORDER BY distance', 0, 200, [':lat' => $lat, ':lng' => $lng]);
4

Yep Haversine should work without a problem. This is slightly modified from a production site which is up and working right now, for example:

$lat = 51.5014;
$lng = 0.1419;

$query = db_select('field_geo_latitude', 'f')
  ->fields('f', array('entity_id'))
  ->orderBy('distance');

$earth_radius = 3959;
$expr = "(
  $earth_radius * acos( 
    cos(radians(:lat))
  * cos(radians(field_geo_latitude))
  * cos(radians(field_geo_longitude) - radians(:lng)) 
  + sin(radians(:lat)) 
  * sin(radians(field_geo_latitude)) 
  )
)";
$query->addExpression($expr, 'distance', array(':lat' => $lat, ':lng' => $long));

$query->having('distance <= :radius', array(':radius' => 10));

Resulting query:

SELECT f.entity_id AS entity_id, (
  3959 * acos( 
    cos(radians(:lat))
  * cos(radians(field_geo_latitude))
  * cos(radians(field_geo_longitude) - radians(:lng)) 
  + sin(radians(:lat)) 
  * sin(radians(field_geo_latitude)) 
  )
) AS distance
FROM {field_geo_latitude} f
HAVING  (distance <= :radius) 
ORDER BY distance ASC

When executed all the parameters are replaced as expected.

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.