Is it possible to create a calculation field using database abstraction layer?

Question

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]);

Answer 1

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.