Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

[SOLVED] DataObject Search by Distance


Reply


3 Posts   936 Views

Avatar
joern

Community Member, 28 Posts

9 May 2011 at 11:44pm

Edited: 10/05/2011 2:14am

Hi,
i'm working on a Google-Maps based Catalog and every Entry has a Location (Lat, Lng) via has_one-Relation.
i found this example http://code.google.com/intl/en-US/apis/maps/articles/phpsqlsearch_v3.html#findnearsql but i don't know how to implement the query into silverstripes sqlQuery-Object.
currently i use a subclass of the AdvancedSearchForm. It would be good to maintain that, because I need to search in other fields and this is still working.

I hope you can help me.
Jörn

Avatar
joern

Community Member, 28 Posts

10 May 2011 at 8:05pm

I figure out how it works! here is my code:

class FilterForm extends AdvancedSearchForm {

   function getQuery($searchParams, $sort = false, $limit = false, $existingQuery = null) {
      …
      
      // check if location fields are set and modify query
      if (isset($searchParams['Location__Lat']) || isset($searchParams['Location__Lng'])) {
         $query = $this->extendLocationSQL($searchParams, $query, $context);
      }
      
      foreach ($searchParamArray as $key => $data) {
         $key = str_replace('__', '.', $key);
         
         if ($filter = $context->getFilter($key)) {
         
            if (isset($data['filter'])) {
               // remove old
               $context->removeFilterByName($key);

               // create new
               $filter = new $data['filter']($key);
               $context->addFilter($filter);
            }
            
            $filter->setModel($this->stat('classToSearch'));
            $filter->setValue($data['value']);
            
            if (!$filter->isEmpty()) {
               $filter->apply($query);
            }
         }
      }
      
      $query->connective = $context->connective;
      $query->distinct = true;

      $model->extend('augmentSQL', $query);
      
      return $query;
   }

   /**
    * modify the current search-query to find entries by distance
    *
    */
   private function extendLocationSQL($searchParams, $query, $context) {
      
      if (isset($searchParams['Location__Distance'])) {
         
         $lat = $searchParams['Location__Lat'];
         $lat = isset($lat['value']) ? (float) $lat['value'] : false;
         
         $lng = $searchParams['Location__Lng'];
         $lng = isset($lng['value']) ? (float) $lng['value'] : false;
         
         $distance = $searchParams['Location__Distance'];
         $distance = isset($distance['value']) ? (float) $distance['value'] : false;
         
         // remove default Location Filters
         $context->removeFilterByName('Location.Lat');
         $context->removeFilterByName('Location.Lng');
      
         if (!$distance || !$lat || !$lng)
            return $query;

         if (!$query->isJoinedTo('Location')) {
            $query->leftJoin('Location', '"Location"."ID" = "Record"."LocationID"');
         }
         
         $query->select['LocationDistance'] = '(3959 * acos(cos(radians(' . $lat . ')) * cos(radians("Location"."Lat")) * cos( radians("Location"."Lng") - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians("Location"."Lat")))) AS LocationDistance';
         $query->having[] = 'LocationDistance < ' . $distance;
      }
   
      return $query;
   }
}

Avatar
Stevie

Community Member, 4 Posts

14 June 2011 at 2:36pm

Hi Joern,

I'm currently looking to do a similar thing - I was wondering if you could show me how you store the LatLng values for the search?

I'm currently storing them as individual values ("Lat" => Text, "Lng" => Text) and can't get anything to work. Help would be appreciated!

Thanks,
Steven