Pulling Up Single Record of Distance

[PCHomepage]

I am trying to pull up only the smallest value for distance between locations. The locations table has only a few entries so speed is not an issue and "as the bird flies" is close enough. I am feeding the query an external location and I want only the single one that is closest. This query below as it shown is giving an array containing the proper distance but the other fields are from first row entry.

When I remove the MIN() and add GROUP BY Distance it seems to work but I am not sure why or if that is the proper way to do it. Please advise.

// Customer's location
$center_lat = 36.713171;
$center_lng = -121.624309;

// My locations
$query = sprintf("SELECT ID, Address, MIN((3959 * acos(cos(radians('%s')) * cos(radians(lat)) * cos(radians(lng) -
 radians('%s')) + sin(radians('%s')) * sin( radians(lat))))) AS Distance 
FROM locations 
LIMIT 1",
  $center_lat,
  $center_lng,
  $center_lat);
$rowCat = DBConnect($query, "Select", "pchome_geoip");

echo "<pre>";
print_r($rowCat);
echo "</pre>";

 

[jpadie]

No need to use min(). Or group functions (not their purpose)

Just add an order by clause and a limit of 1.

The order by clause should be your distance calculation. It is fine to repeat this calc in both the select and order as mysql will optimise the query on the fly. But if you don't need the actual distance returned then just put the calc in the order by.

 

[PCHomepage]

I do need the distances returned. but thank you, that makes sense. It just seemed odd that it was working the way I wanted it to when there seemed to be nothing there to make it do so!

 

[jpadie]

the group by doesn't really work here. you're not looking to group things.

and although MIN is an aggregation function if you are not specifically adding grouping clauses then the interpreter assumes you want the row with the min value across the recordset. (i.e. grouping on the whole recordset, so to speak)

i took a look at the explain output of these two queries

select v1,v2,min(v3) from test;

select v1,v2,v3 from test order by v3 asc;

and found that the engine employed filesort on the latter, which I expect makes it nett slower. I'd need to load it with 100000 or so rows to test properly but at first glance you may get better performance from the MIN() query, however counterintuitive that might be.

Looking back through my records I see that I provided this function for another TT user. you run the second query just once (for ever) to declare the function and then use it in the form

$query = sprintf("select *, min(getDistance( %f, %f, lat, long)) as Distance from locations",$center_lat, $center_lng);

//function
DELIMITER $$

DROP FUNCTION IF EXISTS `getDistance`$$

CREATE FUNCTION `getDistance`(	lat_a float,long_a float, lat_b float, long_b float)
	RETURNS float

BEGIN
	DECLARE distance float;
	
	SELECT (
				(
					ACOS(
						SIN(lat_a * PI() / 180) * SIN(lat_b * PI() / 180) 
						+ 
						COS(lat_a * PI() / 180) * COS(lat_b * PI() / 180) 
						* 
						COS( (long_a - long_b) * PI() / 180)) 
						* 180 / PI()) * 60 * 1.1515) 
	INTO distance;
	RETURN distance;
END $$
DELIMITER ;
SQL;