Answers.Body: <p>If I recall correctly (I battled this about a year ago)...</p>
  
  <p>What you are calling "the query" is actually a <strong>sub</strong>-query. Craft is compiling a much larger query to get all the data it needs in a single shot. The problem is, as far as the parent query is concerned, <code>distance</code> is not a real column. Your <code>distance</code> specifications are confined to the child query, and never make it out to the larger query where it needs to be.</p>
  
  <p>Anyway, my workaround was to convert the haversine formula into PHP, and process it on that side. It wasn't the prettiest solution, but it's been working well for about a year now.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-18T14:23:40.080Z
Answers.Text: @LindseyD Actually ended up being somewhat related to it not being a sub-query - since it’s part of the main query, anything that was added by `addSelect()` was getting discarded because he was calling `ids()` instead of `find()`, hence the MySQL error.
Answers.UserDisplayName: Brandon Kelly
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T11:08:55.140Z
Answers.Text: It’s actually no longer a sub query - that changed in Craft 2.0.
Answers.UserDisplayName: Brandon Kelly
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T14:33:03.760Z
Answers.Text: @BrandonKelly if its no longer a sub query, do you have any idea whats causing the 'unknown column distance' issue?
Answers.UserDisplayName: Sam Hibberd
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T17:04:25.577Z
Answers.Text: @BrandonKelly Well that changes everything! Any clue what may be causing the problem then?
Answers.UserDisplayName: Lindsey D
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T08:30:59.330Z
Answers.LastActivityDate: 2015-02-06T08:30:59.330Z
Answers.UserDisplayName: Lindsey D
Answers.Body: <p>Here is a plugin that I'm using to geocode entries, and search based on closest within a range of (lat, lang) coordinates. In this case. it's storing the lat/lang fields on the entry itself, but you should be able to adapt it for your needs.</p>
  
  <p>In the services file:</p>
  
  <pre><code>static private $url = "http://maps.google.com/maps/api/geocode/json?sensor=false&amp;address=";
  
  public function findClosest($address, $range, $section, $limit = 12) {
  
      $loc        = self::getLocation($address);
      $lat        = $loc['lat'];
      $lng        = $loc['lng'];
      $section    = $section;
      $range      = $range;
      $limit      = $limit;
  
      // define query
      $criteria = craft()-&gt;elements-&gt;getCriteria(ElementType::Entry);
  
      $query = craft()-&gt;elements-&gt;buildElementsQuery($criteria);
      $query-&gt;select(array('elements.id AS id', '( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( field_lat ) ) * cos( radians( field_lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat . ') ) * sin(radians( field_lat )) ) ) AS distance'));
      $query-&gt;andWhere('sections.handle = \''. $section.'\'');
      $query-&gt;having('distance &lt; ' . $range);
      $query-&gt;order('distance');
      $query-&gt;limit($limit);
  
      $results = $query-&gt;queryAll();
  
      $locations = '';
      foreach ($results as $result)
      {
          $locations[] = array(
              'id'        =&gt; $result['id'],
              'distance'  =&gt; $result['distance'],
              'entry'     =&gt; craft()-&gt;entries-&gt;getEntryById($result['id'])
          );
      }
      return $locations;
  }
  
  public function geocodeEntry($entry)
  {   
      $address = $entry-&gt;address.','.$entry-&gt;city.','.$entry-&gt;state.','.$entry-&gt;zip.','.$entry-&gt;country;
  
      $loc = self::getLocation($address);
      if ($loc)
      {
          $entry-&gt;getContent()-&gt;lat   = $loc['lat'];
          $entry-&gt;getContent()-&gt;lng   = $loc['lng'];
      }
  }
  
  static public function getLocation($address)
  {
      $url = self::$url.urlencode($address);
      $resp_json = self::curl_file_get_contents($url);
      $resp = json_decode($resp_json, true);
  
      if ($resp['status'] == 'OK')
      {
          return $resp['results'][0]['geometry']['location'];
      }
      else
      {
          return false;
      }
  }
  
  static private function curl_file_get_contents($url)
  {
      $c = curl_init();
      curl_setopt($c, CURLOPT_RETURNTRANSFER, 1);
      curl_setopt($c, CURLOPT_URL, $url);
      $contents = curl_exec($c);
      curl_close($c);
  
      if ($contents) 
      {
          return $contents;
      }
      else
      { 
          return false;
      }
  }
  </code></pre>
  
  <p>In the variables file:</p>
  
  <pre><code>public function closest($address)
  {
      return craft()-&gt;logic-&gt;findClosest($address, 50, 'SectionName', 9);
  }
  </code></pre>
  
  <p>The init function within the plugin's main file:</p>
  
  <pre><code>public function init()
  {
      parent::init();
  
      craft()-&gt;on('entries.beforeSaveEntry', function(Event $event) 
      {
          $entry = $event-&gt;params['entry'];
          if ($entry-&gt;section-&gt;handle == 'suppliers' || $entry-&gt;section-&gt;handle == 'retailers') 
          {
              craft()-&gt;myPlugin-&gt;geocodeEntry($entry);
          }
      });
  }
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T07:26:36.090Z
Answers.Text: Thanks Douglas, but this needs to work as a part of craft query.
Answers.UserDisplayName: Sam Hibberd
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T10:33:56.927Z
Answers.Text: I'm confused. What do you consider to be a 'craft' query? The haversine sql is just there for a reference.
Answers.UserDisplayName: Douglas McDonald
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T10:38:50.110Z
Answers.Text: Sorry probably not the best terminology to use. Currently my solution needs to integrate with an existing entries query, there are a whole host of param's as a part of an advanced search from the front end. If its makes sense to build my own entries query I would be happy to do so, although would need to ensure that craft still gets all the stuff it needs and that it is future proof etc etc???
Answers.UserDisplayName: Sam Hibberd
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-06T11:30:32.153Z
Answers.Text: Ah, I see what you are doing now – looks like you are trying to modify an existing DBCommand query that is initiated elsewhere in a way that's portable. I wasn't looking at the method parameters.
Answers.UserDisplayName: Douglas McDonald
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-04T18:10:48.380Z
Answers.LastActivityDate: 2015-02-06T10:46:34.827Z
Answers.LastEditDate: 2015-02-06T10:46:34.827Z
Answers.UserDisplayName: Douglas McDonald
Answers.Body: <p>So it turned out that the distance column error was sending us down the wrong path.</p>
  
  <p>The issue was caused by 2 things:</p>
  
  <p>1) As the original Craft entries query was using ids() any select string added via addSelect was being disregarded, the answer here was to move this logic into the having clause.</p>
  
  <p>2) The second issue that was preventing the having clause from working was a missing groupBy clause.</p>
  
  <p>The modifyRadiusQuery code in full:</p>
  
  <pre><code>private function modifyRadiusQuery(DbCommand $query, $value)
  {
      $settings = $this-&gt;getSettings();
      $measurementUnit = $settings-&gt;unit == 'mi' ? 3959 : 6371;
  
      $geo = craft()-&gt;logic-&gt;stringToGeo($value['location']);
  
      if($geo)
      {
          $haversine = $this-&gt;haversine($geo['lat'], $geo['lng']);
  
          // ids()
          $query-&gt;join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
          $query-&gt;having($haversine.' &lt; '.$value['radius']);
          $query-&gt;group('geo.lat, geo.lng');
  
          // find()
          // $haversine = $this-&gt;haversine($geo['lat'], $geo['lng']);
          // $query-&gt;addSelect($haversine.' AS distance');    
          // $query-&gt;join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
          // $query-&gt;having('distance &lt; '.$value['radius']);
          // $query-&gt;group('geo.lat, geo.lng');
      }
  }
  </code></pre>
  
  <p>The DBCommand query that was fixed:</p>
  
  <pre><code>        $query-&gt;join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
          $query-&gt;having($haversine.' &lt; '.$value['radius']);
          $query-&gt;group('geo.lat, geo.lng');
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-02-18T12:13:37.003Z
Answers.LastActivityDate: 2015-02-18T12:13:37.003Z
Answers.UserDisplayName: Sam Hibberd
Body: <p>I am looking to build a radius search from my field type’s modifyElementsQuery() method.</p>
  
  <p>I am storing lat/lng values in a separate table, and am trying to integrate the location search based on the following:</p>
  
  <p><a href="https://developers.google.com/maps/articles/phpsqlsearch_v3?csw=1#findnearsql" rel="nofollow">https://developers.google.com/maps/articles/phpsqlsearch_v3?csw=1#findnearsql</a></p>
  
  <p>Here’s my current code. It throws an error saying "unknown column 'distance' in having clause".</p>
  
  <pre><code>public function modifyElementsQuery(DbCommand $query, $value)
  {
  
      if($value !== null)
      {
          $settings = $this-&gt;getSettings();
          $this-&gt;plugin-&gt;pluginLog($value);
  
          $unitAdjustment = $settings-&gt;unit == 'mi' ? 3959 : 6371;
          $lat = 37;
          $lng = -122;
          $radius = $value;
  
          $query-&gt;addSelect('( '.$unitAdjustment.' * acos( cos( radians( '.$lat.' ) ) * cos( radians( fruit_geo.lat ) ) * cos( radians( fruit_geo.lng ) - radians( '.$lng.' ) ) + sin( radians( '.$lat.' ) ) * sin( radians( fruit_geo.lat ) ) ) ) as distance');
          $query-&gt;join('fruit_geo fruit_geo', 'fruit_geo.elementId = content.elementId');
          $query-&gt;having('distance &lt; '.$radius);
  
  
          // Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. 
          // It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, 
          // and then asks for only rows where the distance value is less than 25, orders the whole query by distance, 
          // and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371
  
          //SELECT id, ( $unitAdjustment * acos( cos( radians( $lat ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( lat ) ) ) ) AS distance FROM table HAVING distance &lt; $radius
      }
  
  }
  </code></pre>
  
  <p>Template errors:</p>
  
  <pre><code>&lt;p&gt;CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause'. The SQL statement executed was: SELECT `elements`.`id`
  FROM `craft_elements` `elements`
  JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
  JOIN `craft_content` `content` ON content.elementId = elements.id
  JOIN `craft_fruit_geo` `fruit_geo` ON fruit_geo.elementId = content.elementId
  JOIN `craft_entries` `entries` ON entries.id = elements.id
  JOIN `craft_sections` `sections` ON sections.id = entries.sectionId
  LEFT JOIN `craft_structures` `structures` ON structures.id = sections.structureId
  LEFT JOIN `craft_structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
  WHERE (((((((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate &lt;= '2015-02-06 11:57:46') AND ((entries.expiryDate is null) OR (entries.expiryDate &gt; '2015-02-06 11:57:46')))) AND (elements_i18n.enabled = 1)) AND (content.field_date&gt;=:contentfield_date1)) AND (sections.handle=:sectionshandle1)) AND (`elements`.`id` IN ('5747', '5823', '8529', '8865', '8913', '8972', '8973', '8978', '8979', '10228', '10370', '10912', '11780', '13366', '14673', '14675', '14759', '14764', '14770', '20516', '20682', '20683', '21042', '21043', '21143', '22247', '22570', '22814', '22817', '22827', '22830', '22834', '22835', '22836', '22844', '22855', '22859', '22860', '22867', '22899', '22909', '22916', '22945', '22970'))
  GROUP BY `elements`.`id`
  HAVING distance &lt; 50
  ORDER BY `field_date` ASC. Bound with :locale='en_gb', :contentfield_date1='2015-02-06 00:00:00', :sectionshandle1='events' (/var/www/vhosts/findarace.com/beta.findarace.com/craft/app/framework/db/CDbCommand.php:543)&lt;/p&gt;&lt;pre&gt;#0 /var/www/vhosts/findarace.com/beta.findarace.com/craft/app/framework/db/CDbCommand.php(396): CDbCommand-&gt;queryInternal('fetchAll', Array, Array)
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-04T20:19:09.500Z
Comments.Text: Not sure if this helps, but the [Smart Map](https://craftpl.us/plugins/smart-map) plugin was originally designed specifically to [perform a radius search](https://craftpl.us/plugins/smart-map/docs/sorting-entries-by-closest-locations).
Comments.UserDisplayName: Lindsey D
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T07:24:00.687Z
Comments.Text: Thanks Lindsey, but this needs to work as a part of craft query and we are using squareMaps with the mapbox api. Things appear to be getting stuck at the addSelect and the reference to distance in the HAVING clause??
Comments.UserDisplayName: Sam Hibberd
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T07:40:13.900Z
Comments.Text: What do you mean by "getting stuck"? That addSelect looks pretty solid... Is it returning the correct results, but not ordering them properly?
Comments.UserDisplayName: Lindsey D
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T07:43:49.207Z
Comments.Text: It throws an error saying 'unknown column distance is having clause.
Comments.UserDisplayName: Sam Hibberd
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T07:46:17.480Z
Comments.Text: My first instinct is to say triple-check the number of parenthesis you've got... but I just checked a minute ago and they looked fine.
Comments.UserDisplayName: Lindsey D
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T07:49:25.443Z
Comments.Text: Yeah I have :). Thought that too. In my head this should work??!!
Comments.UserDisplayName: Sam Hibberd
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T07:55:20.160Z
Comments.Text: I feel like I also ran into this problem... but it was like a year ago, so I'm having trouble remembering. Email me (lindsey@doublesecretagency.com) and I'll send you a copy of the plugin to dissect.
Comments.UserDisplayName: Lindsey D
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T11:47:36.830Z
Comments.Text: Lindsey, as it's no longer a sub-query does that mean in theory we should be able to get it working??
Comments.UserDisplayName: Sam Hibberd
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-06T18:03:46.967Z
Comments.Text: In the error, I'm not seeing the haversine factored into the query at all. I don't know if it will help, but you might have a look [at this post](http://craftcms.stackexchange.com/questions/4600/order-two-entry-types-by-different-date-fields-for-use-in-one-combined-stream). Looks like he/she is defining multiple queries and then unioning them together (in a way that I don't completely understand). Sounds kind of like what you are trying to do. Might help.
Comments.UserDisplayName: Douglas McDonald
Tags: <plugin-development>
Title: Using modifyElementsQuery() to build a Radius Search?
ThreadText: # Using modifyElementsQuery() to build a Radius Search?
  
  (Asked by: username_0 on 2015-02-04)
  
  I am looking to build a radius search from my field type’s modifyElementsQuery() method.
  
  I am storing lat/lng values in a separate table, and am trying to integrate the location search based on the following:
  
  <https://developers.google.com/maps/articles/phpsqlsearch_v3?csw=1#findnearsql>
  
  Here’s my current code. It throws an error saying "unknown column 'distance' in having clause".
  
  ```
  public function modifyElementsQuery(DbCommand $query, $value)
  {
  
      if($value !== null)
      {
          $settings = $this->getSettings();
          $this->plugin->pluginLog($value);
  
          $unitAdjustment = $settings->unit == 'mi' ? 3959 : 6371;
          $lat = 37;
          $lng = -122;
          $radius = $value;
  
          $query->addSelect('( '.$unitAdjustment.' * acos( cos( radians( '.$lat.' ) ) * cos( radians( fruit_geo.lat ) ) * cos( radians( fruit_geo.lng ) - radians( '.$lng.' ) ) + sin( radians( '.$lat.' ) ) * sin( radians( fruit_geo.lat ) ) ) ) as distance');
          $query->join('fruit_geo fruit_geo', 'fruit_geo.elementId = content.elementId');
          $query->having('distance < '.$radius);
  
          // Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. 
          // It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, 
          // and then asks for only rows where the distance value is less than 25, orders the whole query by distance, 
          // and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371
  
          //SELECT id, ( $unitAdjustment * acos( cos( radians( $lat ) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians( $lng ) ) + sin( radians( $lat ) ) * sin( radians( lat ) ) ) ) AS distance FROM table HAVING distance < $radius
      }
  
  }
  
  ```
  
  Template errors:
  
  ```
  <p>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause'. The SQL statement executed was: SELECT `elements`.`id`
  FROM `craft_elements` `elements`
  JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
  JOIN `craft_content` `content` ON content.elementId = elements.id
  JOIN `craft_fruit_geo` `fruit_geo` ON fruit_geo.elementId = content.elementId
  JOIN `craft_entries` `entries` ON entries.id = elements.id
  JOIN `craft_sections` `sections` ON sections.id = entries.sectionId
  LEFT JOIN `craft_structures` `structures` ON structures.id = sections.structureId
  LEFT JOIN `craft_structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
  WHERE (((((((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate <= '2015-02-06 11:57:46') AND ((entries.expiryDate is null) OR (entries.expiryDate > '2015-02-06 11:57:46')))) AND (elements_i18n.enabled = 1)) AND (content.field_date>=:contentfield_date1)) AND (sections.handle=:sectionshandle1)) AND (`elements`.`id` IN ('5747', '5823', '8529', '8865', '8913', '8972', '8973', '8978', '8979', '10228', '10370', '10912', '11780', '13366', '14673', '14675', '14759', '14764', '14770', '20516', '20682', '20683', '21042', '21043', '21143', '22247', '22570', '22814', '22817', '22827', '22830', '22834', '22835', '22836', '22844', '22855', '22859', '22860', '22867', '22899', '22909', '22916', '22945', '22970'))
  GROUP BY `elements`.`id`
  HAVING distance < 50
  ORDER BY `field_date` ASC. Bound with :locale='en_gb', :contentfield_date1='2015-02-06 00:00:00', :sectionshandle1='events' (/var/www/vhosts/findarace.com/beta.findarace.com/craft/app/framework/db/CDbCommand.php:543)</p><pre>#0 /var/www/vhosts/findarace.com/beta.findarace.com/craft/app/framework/db/CDbCommand.php(396): CDbCommand->queryInternal('fetchAll', Array, Array)
  
  ```
  
  **Tags:** plugin-development
  
  > **Comments:**
  > **username_1**: Not sure if this helps, but the [Smart Map](https://craftpl.us/plugins/smart-map) plugin was originally designed specifically to [perform a radius search](https://craftpl.us/plugins/smart-map/docs/sorting-entries-by-closest-locations).  
  > **username_0**: Thanks username_1, but this needs to work as a part of craft query and we are using squareMaps with the mapbox api. Things appear to be getting stuck at the addSelect and the reference to distance in the HAVING clause??  
  > **username_1**: What do you mean by "getting stuck"? That addSelect looks pretty solid... Is it returning the correct results, but not ordering them properly?  
  > **username_0**: It throws an error saying 'unknown column distance is having clause.  
  > **username_1**: My first instinct is to say triple-check the number of parenthesis you've got... but I just checked a minute ago and they looked fine.  
  > **username_0**: Yeah I have :). Thought that too. In my head this should work??!!  
  > **username_1**: I feel like I also ran into this problem... but it was like a year ago, so I'm having trouble remembering. Email me (username_1@doublesecretagency.com) and I'll send you a copy of the plugin to dissect.  
  > **username_0**: username_1, as it's no longer a sub-query does that mean in theory we should be able to get it working??  
  > **username_2**: In the error, I'm not seeing the haversine factored into the query at all. I don't know if it will help, but you might have a look [at this post](http://craftcms.stackexchange.com/questions/4600/order-two-entry-types-by-different-date-fields-for-use-in-one-combined-stream). Looks like he/she is defining multiple queries and then unioning them together (in a way that I don't completely understand). Sounds kind of like what you are trying to do. Might help.  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_0 on 2015-02-18. Score: 5 | Accepted answer
  
  So it turned out that the distance column error was sending us down the wrong path.
  
  The issue was caused by 2 things:
  
  1) As the original Craft entries query was using ids() any select string added via addSelect was being disregarded, the answer here was to move this logic into the having clause.
  
  2) The second issue that was preventing the having clause from working was a missing groupBy clause.
  
  The modifyRadiusQuery code in full:
  
  ```
  private function modifyRadiusQuery(DbCommand $query, $value)
  {
      $settings = $this->getSettings();
      $measurementUnit = $settings->unit == 'mi' ? 3959 : 6371;
  
      $geo = craft()->logic->stringToGeo($value['location']);
  
      if($geo)
      {
          $haversine = $this->haversine($geo['lat'], $geo['lng']);
  
          // ids()
          $query->join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
          $query->having($haversine.' < '.$value['radius']);
          $query->group('geo.lat, geo.lng');
  
          // find()
          // $haversine = $this->haversine($geo['lat'], $geo['lng']);
          // $query->addSelect($haversine.' AS distance');    
          // $query->join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
          // $query->having('distance < '.$value['radius']);
          // $query->group('geo.lat, geo.lng');
      }
  }
  
  ```
  
  The DBCommand query that was fixed:
  
  ```
          $query->join(Logic_GeoRecord::TABLE_NAME.' geo', 'elements.id = geo.elementId');
          $query->having($haversine.' < '.$value['radius']);
          $query->group('geo.lat, geo.lng');
  
  ```
  
  
  ---
  
  ### Answer by username_1 on 2015-02-06. Score: 2
  
  If I recall correctly (I battled this about a year ago)...
  
  What you are calling "the query" is actually a **sub**-query. Craft is compiling a much larger query to get all the data it needs in a single shot. The problem is, as far as the parent query is concerned, `distance` is not a real column. Your `distance` specifications are confined to the child query, and never make it out to the larger query where it needs to be.
  
  Anyway, my workaround was to convert the haversine formula into PHP, and process it on that side. It wasn't the prettiest solution, but it's been working well for about a year now.
  
  > **Comments:**
  > **username_3**: It’s actually no longer a sub query - that changed in Craft 2.0.  
  > **username_0**: @username_3 if its no longer a sub query, do you have any idea whats causing the 'unknown column distance' issue?  
  > **username_1**: @username_3 Well that changes everything! Any clue what may be causing the problem then?  
  > **username_3**: @username_1 Actually ended up being somewhat related to it not being a sub-query - since it’s part of the main query, anything that was added by `addSelect()` was getting discarded because he was calling `ids()` instead of `find()`, hence the MySQL error.  
  
  ---
  
  ### Answer by username_2 on 2015-02-04. Score: 2
  
  Here is a plugin that I'm using to geocode entries, and search based on closest within a range of (lat, lang) coordinates. In this case. it's storing the lat/lang fields on the entry itself, but you should be able to adapt it for your needs.
  
  In the services file:
  
  ```
  static private $url = "http://maps.google.com/maps/api/geocode/json?sensor=false&address=";
  
  public function findClosest($address, $range, $section, $limit = 12) {
  
      $loc        = self::getLocation($address);
      $lat        = $loc['lat'];
      $lng        = $loc['lng'];
      $section    = $section;
      $range      = $range;
      $limit      = $limit;
  
      // define query
      $criteria = craft()->elements->getCriteria(ElementType::Entry);
  
      $query = craft()->elements->buildElementsQuery($criteria);
      $query->select(array('elements.id AS id', '( 3959 * acos( cos( radians(' . $lat . ') ) * cos( radians( field_lat ) ) * cos( radians( field_lng ) - radians(' . $lng . ') ) + sin( radians(' . $lat . ') ) * sin(radians( field_lat )) ) ) AS distance'));
      $query->andWhere('sections.handle = \''. $section.'\'');
      $query->having('distance < ' . $range);
      $query->order('distance');
      $query->limit($limit);
  
      $results = $query->queryAll();
  
      $locations = '';
      foreach ($results as $result)
      {
          $locations[] = array(
              'id'        => $result['id'],
              'distance'  => $result['distance'],
              'entry'     => craft()->entries->getEntryById($result['id'])
          );
      }
      return $locations;
  }
  
  public function geocodeEntry($entry)
  {   
      $address = $entry->address.','.$entry->city.','.$entry->state.','.$entry->zip.','.$entry->country;
  
      $loc = self::getLocation($address);
      if ($loc)
      {
          $entry->getContent()->lat   = $loc['lat'];
          $entry->getContent()->lng   = $loc['lng'];
      }
  }
  
  static public function getLocation($address)
  {
      $url = self::$url.urlencode($address);
      $resp_json = self::curl_file_get_contents($url);
      $resp = json_decode($resp_json, true);
  
      if ($resp['status'] == 'OK')
      {
          return $resp['results'][0]['geometry']['location'];
      }
      else
      {
          return false;
      }
  }
  
  static private function curl_file_get_contents($url)
  {
      $c = curl_init();
      curl_setopt($c, CURLOPT_RETURNTRANSFER, 1);
      curl_setopt($c, CURLOPT_URL, $url);
      $contents = curl_exec($c);
      curl_close($c);
  
      if ($contents) 
      {
          return $contents;
      }
      else
      { 
          return false;
      }
  }
  
  ```
  
  In the variables file:
  
  ```
  public function closest($address)
  {
      return craft()->logic->findClosest($address, 50, 'SectionName', 9);
  }
  
  ```
  
  The init function within the plugin's main file:
  
  ```
  public function init()
  {
      parent::init();
  
      craft()->on('entries.beforeSaveEntry', function(Event $event) 
      {
          $entry = $event->params['entry'];
          if ($entry->section->handle == 'suppliers' || $entry->section->handle == 'retailers') 
          {
              craft()->myPlugin->geocodeEntry($entry);
          }
      });
  }
  
  ```
  
  > **Comments:**
  > **username_0**: Thanks username_2, but this needs to work as a part of craft query.  
  > **username_2**: I'm confused. What do you consider to be a 'craft' query? The haversine sql is just there for a reference.  
  > **username_0**: Sorry probably not the best terminology to use. Currently my solution needs to integrate with an existing entries query, there are a whole host of param's as a part of an advanced search from the front end. If its makes sense to build my own entries query I would be happy to do so, although would need to ensure that craft still gets all the stuff it needs and that it is future proof etc etc???  
  > **username_2**: Ah, I see what you are doing now – looks like you are trying to modify an existing DBCommand query that is initiated elsewhere in a way that's portable. I wasn't looking at the method parameters.  
  
  ---
  
  