INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Pulling Up Single Record of Distance

Pulling Up Single Record of Distance

(OP)
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.

CODE --> MySQL-Query

// 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>"; 

RE: Pulling Up Single Record of Distance

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.

RE: Pulling Up Single Record of Distance

(OP)
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!

RE: Pulling Up Single Record of Distance

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

CODE

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

CODE

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

CODE

//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; 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close