Nearest neighbor radius serach

[tagger]

Hi Folks,

I need a feature .

What Id like to have is a functionality to run a radius search on items of one list with items of another list.

For example I have a list with locations of households and a list with supermarket locations. Now I want to populate an element in households list with the nearest supermarket.

Any ideas/pointers on a quick solution for that?

Question to that on radius search: Are the results ordered ascending by distance?

Thanks so much!

 

[tagger]

badabump

 

[rob]

So you only need the nearest one super market to the household? If so then I think you might be able to do this with a calculation element in the household form

using this PHP code...

// --------- Edit these parameters to match your site  ---------------//
 
// House hold form's google map element
$userLocationField = 'household___location_raw';
 
// Lookup address table
$lookUpTable = 'supermarkets';
 
//Lookup table, google map element field name
$LookUpField = 'googlemap';
 
// Distance km or miles?
$metric = 'km';
 
// The look address list's form id
$formid = 1;
 
//--------- Do not edit below here  ---------------//
 
// Get the house hold address latitude and longitude.
$loc = $data[$userLocationField];
$loc = str_replace(array('(', ')'), '', $loc);
$loc = explode(':', $loc);
$loc = explode(',', $loc[0]);
$latitude = $loc[0];
$longitude = $loc[1];
 
$scalar = $metric === 'km' ? 1.609344 : 1;
 
// Query the supermarkets table to find the closest one to the house location
$db = JFactory::getDbo();
 
$latfield = "SUBSTRING_INDEX(TRIM(LEADING '(' FROM $LookUpField), ',', 1)";
$lonfield = "SUBSTRING_INDEX(SUBSTRING_INDEX($LookUpField, ',', -1), ')', 1)";
 
$dis = "(((acos(sin((" . $latitude . " * pi()/180)) * sin(($latfield * pi()/180))+cos((" . $latitude
. " * pi()/180)) * cos(($latfield * pi()/180)) * cos(((" . $longitude . "- $lonfield) * pi()/180)))) * 180/pi()) * 60 * 1.1515 * " . $scalar . ")";
 
$query = "SELECT id, $dis AS distance FROM $lookUpTable ORDER BY $dis ASC LIMIT 1";
$db->setQuery($query);
$closest = $db->loadObject();
 
// Return the URL to the super market's details page
return '<a href="index.php?option=com_fabrik&view=details&formid=' . $formid . '&rowid=' . $closest->id . '">Closest supermarket</a>';

 

[tagger]

Oh yer!! Thanks a million!! Code works, nice job, you're the man !

for other noobs like me, if you need the actual name and the (rounded) distance of the "supermarket" shown (if the DB field name is called "name"):

$query = "SELECT id, name, ROUND($dis,3) AS distance FROM $lookUpTable ORDER BY $dis ASC LIMIT 1";
$db->setQuery($query);
$closest = $db->loadObject();
return '<a href="index.php?option=com_fabrik&view=form&formid=' . $formid . '&rowid=' . $closest->id . '">' . $closest->name . ' /'  . $closest->distance . ' km</a>';

again thanks a lot!

 

[rob]

It was fun to figure out how to do it

 

[tagger]

Hi Rob. Glad you had fun...I am havin a lot of enjoying moments with fabrik, too

As you're around right now....

I tryed to to fill a new empty field with the calculated value as a cron...but I am stuck referencing the $rowid to the actual db primarykey

// $query = UPDATE households SET distance_to_supermarket ='' . $dis . '' WHERE ???

could you give me a little headsup...that'll be very cool.

Thanks so much!