<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>

CC BY-SA 3.0
2015-02-18T14:23:40.080Z
@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.
Brandon Kelly
CC BY-SA 3.0
2015-02-06T11:08:55.140Z
It’s actually no longer a sub query - that changed in Craft 2.0.
Brandon Kelly
CC BY-SA 3.0
2015-02-06T14:33:03.760Z
@BrandonKelly if its no longer a sub query, do you have any idea whats causing the 'unknown column distance' issue?
Sam Hibberd
CC BY-SA 3.0
2015-02-06T17:04:25.577Z
@BrandonKelly Well that changes everything! Any clue what may be causing the problem then?
Lindsey D
CC BY-SA 3.0
2015-02-06T08:30:59.330Z
2015-02-06T08:30:59.330Z
Lindsey D
<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>

CC BY-SA 3.0
2015-02-06T07:26:36.090Z
Thanks Douglas, but this needs to work as a part of craft query.
Sam Hibberd
CC BY-SA 3.0
2015-02-06T10:33:56.927Z
I'm confused. What do you consider to be a 'craft' query? The haversine sql is just there for a reference.
Douglas McDonald
CC BY-SA 3.0
2015-02-06T10:38:50.110Z
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???
Sam Hibberd
CC BY-SA 3.0
2015-02-06T11:30:32.153Z
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.
Douglas McDonald
CC BY-SA 3.0
2015-02-04T18:10:48.380Z
2015-02-06T10:46:34.827Z
2015-02-06T10:46:34.827Z
Douglas McDonald
<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>

CC BY-SA 3.0
2015-02-18T12:13:37.003Z
2015-02-18T12:13:37.003Z
Sam Hibberd
<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>

CC BY-SA 3.0
2015-02-04T20:19:09.500Z
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).
Lindsey D
CC BY-SA 3.0
2015-02-06T07:24:00.687Z
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??
Sam Hibberd
CC BY-SA 3.0
2015-02-06T07:40:13.900Z
What do you mean by "getting stuck"? That addSelect looks pretty solid... Is it returning the correct results, but not ordering them properly?
Lindsey D
CC BY-SA 3.0
2015-02-06T07:43:49.207Z
It throws an error saying 'unknown column distance is having clause.
Sam Hibberd
CC BY-SA 3.0
2015-02-06T07:46:17.480Z
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.
Lindsey D
CC BY-SA 3.0
2015-02-06T07:49:25.443Z
Yeah I have :). Thought that too. In my head this should work??!!
Sam Hibberd
CC BY-SA 3.0
2015-02-06T07:55:20.160Z
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.
Lindsey D
CC BY-SA 3.0
2015-02-06T11:47:36.830Z
Lindsey, as it's no longer a sub-query does that mean in theory we should be able to get it working??
Sam Hibberd
CC BY-SA 3.0
2015-02-06T18:03:46.967Z
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.
Douglas McDonald
<plugin-development>
Using modifyElementsQuery() to build a Radius Search?
# 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:
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)#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 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??  
> **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 (lindsey@doublesecretagency.com) and I'll send you a copy of the plugin to dissect.  
> **username_0**: Lindsey, 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**: @BrandonKelly if its no longer a sub query, do you have any idea whats causing the 'unknown column distance' issue?  
> **username_1**: @BrandonKelly Well that changes everything! Any clue what may be causing the problem then?  
> **username_3**: @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.  

---

### 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 Douglas, 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.  

---

