Location Proximity Search using db_select in Custom Module

Question

I want to build a custom block which uses the nid of the node it's visible on to pull in nodes within a radius of 50 miles based on it's geolocation. I am using the location module and gmap where doing this query in views is easy but I have no idea where to even begin in building this query in db_select.

this is what views is outputting for the same query:

SELECT node.title AS node_title, node.nid AS nid, (COALESCE(ACOS(0.61832265427253*COS(RADIANS(location.latitude))*(0.99735883206175*COS(RADIANS(location.longitude)) + -0.072631674277943*SIN(RADIANS(location.longitude))) + 0.78592435718291*SIN(RADIANS(location.latitude))), 0.00000)*6364902.7458257) AS location_distance_sort
FROM 
{node} node
LEFT JOIN {location_instance} location_instance ON node.vid = location_instance.vid
LEFT JOIN {location} location ON location_instance.lid = location.lid
WHERE (( (node.nid != '3849' OR node.nid IS NULL) )AND(( (node.status = '1') AND (node.type IN  ('weather_reports')) AND (location.latitude > '51.081897505891' AND location.latitude < '52.530604494109' AND location.longitude > '-5.3366374087818' AND location.longitude < '-2.9936745912182') AND ((COALESCE(ACOS(0.61832265427253*COS(RADIANS(location.latitude))*(0.99735883206175*COS(RADIANS(location.longitude)) + -0.072631674277943*SIN(RADIANS(location.longitude))) + 0.78592435718291*SIN(RADIANS(location.latitude))), 0.00000)*6364902.7458257) < '80467.35') )))
ORDER BY location_distance_sort ASC
LIMIT 1 OFFSET 0

How do i run a db_select query to select the nodes within 50 miles of 51.6599, -3.9215

Answer 1

Thanks to @joemurray for pointing me in the right direction. This is my function to grab the closest nid to my target nodes lat, long.

/**
 *
 * Load the nid we are interested in for this report taken from the page we are on.
 * [http://funkjedi.com/technology/308-search-using-geolocation-data-in-mysql/][1]
 */
function _load_proximity_nids($lat, $lon) {
  $nids = array();

  // in Miles
  $radius = 25;

  // Constants related to the surface of the Earth
  $earths_radius = 3958.9394;
  $surface_distance_coeffient = 69.174;

  // Spherical Law of Cosines
  $distance_formula = "$earths_radius * ACOS( SIN(RADIANS(latitude)) * SIN(RADIANS($lat)) + COS(RADIANS(longitude - $lon)) * COS(RADIANS(latitude)) * COS(RADIANS($lat)) )";

  // Create a bounding box to reduce the scope of our search
  $lng_b1 = $lon - $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient);
  $lng_b2 = $lon + $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient);
  $lat_b1 = $lat - $radius / $surface_distance_coeffient;
  $lat_b2 = $lat + $radius / $surface_distance_coeffient;

  // Construct our sql statement
  $results = db_query('SELECT node.title AS node_title, node.nid AS nid, node.status AS status, node.type AS type, (' . $distance_formula . ') AS distance
FROM
{node} node
LEFT JOIN {location_instance} location_instance ON node.vid = location_instance.vid
LEFT JOIN {location} location ON location_instance.lid = location.lid
WHERE (location.latitude BETWEEN ' . $lat_b1 . ' AND ' . $lat_b2 . ') AND (location.longitude BETWEEN ' . $lng_b1 . ' AND ' . $lng_b2 . ') HAVING distance < :radius
AND (node.status = 1)
AND node.type = :type
ORDER BY distance ASC',
  array(
    ':type' => 'weather_reports',
    ':radius' => $radius)
  );

  foreach ($results as $result) {
    $nids[] = $result->nid;
  }

  return $nids;
}

Answer 2

Given your comment, I see the question is related not to the query itself but how to use db_select. I don't think you should. Use db_query() instead - https://api.drupal.org/api/drupal/includes%21database%21database.inc/function/db_query/7

If you are comfortable hardcoding the lat/long and 50 miles, then you don't need to change the query in order to accept arguments for these values.

Assuming the query was produced for the latlong and 50 mile radius you provide, then it looks to me like you just need to remove the LIMIT 1 OFFSET 0 to get all of the relevant nodes, like this:

    SELECT node.title AS node_title, node.nid AS nid, (COALESCE(ACOS(0.61832265427253*COS(RADIANS(location.latitude))*(0.99735883206175*COS(RADIANS(location.longitude)) + -0.072631674277943*SIN(RADIANS(location.longitude))) + 0.78592435718291*SIN(RADIANS(location.latitude))), 0.00000)*6364902.7458257) AS location_distance_sort
FROM 
{node} node
LEFT JOIN {location_instance} location_instance ON node.vid = location_instance.vid
LEFT JOIN {location} location ON location_instance.lid = location.lid
WHERE (( (node.nid != '3849' OR node.nid IS NULL) )AND(( (node.status = '1') AND (node.type IN  ('weather_reports')) AND (location.latitude > '51.081897505891' AND location.latitude < '52.530604494109' AND location.longitude > '-5.3366374087818' AND location.longitude < '-2.9936745912182') AND ((COALESCE(ACOS(0.61832265427253*COS(RADIANS(location.latitude))*(0.99735883206175*COS(RADIANS(location.longitude)) + -0.072631674277943*SIN(RADIANS(location.longitude))) + 0.78592435718291*SIN(RADIANS(location.latitude))), 0.00000)*6364902.7458257) < '80467.35') )))
ORDER BY location_distance_sort ASC;

My sense is that the code has calculated a bounding rectangle 50 miles in every direction for the where clause as a performance optimization. The trigonometry calulations define the distance between the nodes/latlongs, and is used to limit to a 50 mile radius in the where condition and just to provide the distance in the SELECT clause.

If you want to be able to produce queries like this for other latitudes and longitudes or radiuses, I would recommend looking at how the code converts miles to degrees, and calculates the values in the rest of the trig parts of the query.