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.

We've moved the forum!

Please use for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

[SOLVED] DataObject Search by Distance

Go to End

3 Posts   2042 Views


Community Member, 28 Posts

9 May 2011 at 11:44pm

Edited: 10/05/2011 2:14am

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


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

					// create new
					$filter = new $data['filter']($key);
				if (!$filter->isEmpty()) {
		$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
			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;


Community Member, 5 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!