Filter by function

Hi. I have two tables: one called Hotel and another Places. In each of them i have a field called coordinates( something like - 55.751402982902576,37.5655871629715).

Also there is a function which counts the distance between two points on earth called distanceFind($x,$y).

Now the main part: i have a form where i choose the one point(via dropdownlist) from Place table and in textfield i am pointing out the radius within which must be a hotel.

How i can list hotels whose distanceFind function between hotel coordinates and place coordinates result less than radius?

What RDBMS are you using?

Mysql 5.1

985

Just show me the way, please. I need an IDEA

Hey guys, any help will be useful to me

Build an array from the results and run it through an array_filter.

Then sort the results.

See this:

http://php.net/manual/en/function.array-filter.php

I don’t think you need to convert the results of a findAll query; just put it through that array_filter.

The callback function could then figure out the distance and return false if it’s to far off.

this may be of use:

"Geo (proximity) Search with MySQL"

http://www.arubin.org/files/geo_search.pdf

Hi aramiz.

I’m not a yii wizard but If ypur functions is a php funtion I think that is the critical point:the mix of your distance function and the list of points that come from the database. The dabase functions are simple functions (averange, addition, max, etc). If you try to insert your distance function as a sql sentence is the hard way.

Why dont you just process all the points , finding the distance and build and array of the point that have a distance that you require then you can use that array.

$sql = ‘Select your points’;

$command= Yii::app()->db->createCommand($sql);

$points_array=$command->queryAll();

$points_selected=array();

foreach ($points_array as $point){

$distance=your_function($point[‘x’],$point[‘y’]);

// if the distance meets your condition add to points_selected arary.

}

Thanks a lot guys! Your answers were useful.