Help w/ Distance Calculation Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help w/ Distance Calculation Query

Author

Topic

DotNetInt
Starting Member

9 Posts

Posted - 2007-03-28 : 21:58:26

I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.

SELECT UserID, ACOS(SIN($StartLatitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS($StartLatitude * PI() / 180) * COS(Latitude * PI() / 180) * COS(($StartLongitude - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515 AS Distance
FROM HPN_Painters
HAVING (Distance <= 150)

It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-28 : 22:23:33

You need to replace the Distance with the entire formula. You cannot reference an alias in the HAVING condition like that.

************************
Life is short. Enjoy it.
************************

DotNetInt
Starting Member

9 Posts

Posted - 2007-03-28 : 22:37:49

SELECT UserID, ACOS(SIN(28.601403 * PI() / 180) * SIN(Latitude * PI() / 180) + COS(28.601403 * PI() / 180) * COS(Latitude * PI() / 180) * COS((- 81.342773 - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515 AS Distance
FROM HPN_Painters
WHERE (ACOS(SIN(28.601403 * PI() / 180) * SIN(Latitude * PI() / 180) + COS(28.601403 * PI() / 180) * COS(Latitude * PI() / 180) * COS((- 81.342773 - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515) < 150
ORDER BY Distance ASC

I see. This works, but will it cause the calculation to be performed twice, slowing down the performance? I need to keep the Distance alias to be able to sort the records as well.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-28 : 22:57:01

You may find it more convenient to use a function for this calculation. The function on the link below computes the great circle distance using the Haversine distance algorithim.

Great Circle Distance Function - Haversine Formula:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

You can use the column alias in your WHERE clause if you do the calculation in a derived table.


select
	*
from
	(
	select
		UserID,
		Distance = 
		F_GREAT_CIRCLE_DISTANCE($StartLatitude,$StartLongitude,Latitude,Longitude)
	from
		HPN_Painters
	) a
where
	a.Distance < 150

CODO ERGO SUM

DotNetInt
Starting Member

9 Posts

Posted - 2007-03-29 : 14:19:09

This works very well. Thanks!!! It never occured to me to simply use a subquery. I plugged my own calcuation into it and it works well also. Though I don't believe it is as accurace as the function above, it might be a little less performance hungry.

SELECT * FROM (
SELECT UserID, (((acos(sin((" + Latitude + "*pi()/180)) * sin ((latitude*pi()/180)) + cos((" + Latitude + "*pi()/180)) * cos((latitude*pi()/180)) * cos(((" + Longitude + " - longitude)*pi()/180))))*180/pi())*60*1.1515) as Distance
FROM HPN_Painters ) AS a
WHERE a.Distance <= 150
ORDER BY Distance ASC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-29 : 17:19:27

quote:
Originally posted by DotNetInt

This works very well. Thanks!!! It never occured to me to simply use a subquery. I plugged my own calcuation into it and it works well also. Though I don't believe it is as accurace as the function above, it might be a little less performance hungry.

SELECT * FROM (
SELECT UserID, (((acos(sin((" + Latitude + "*pi()/180)) * sin ((latitude*pi()/180)) + cos((" + Latitude + "*pi()/180)) * cos((latitude*pi()/180)) * cos(((" + Longitude + " - longitude)*pi()/180))))*180/pi())*60*1.1515) as Distance
FROM HPN_Painters ) AS a
WHERE a.Distance <= 150
ORDER BY Distance ASC

Performance may depend more on how many rows you are doing this conversion for. If you are doing this for a few hundred rows, I doubt very much that there will be any difference.

There are methods that you can use to limit the number of rows you have to convert that would probably give a much greater performance improvement, instead of running your whole table through the calculation. Basically, you calculate the maximum and minimum longitude and latitude that describes a square around the circle that you want to select, and then use the function to calculate the distance to this subset of the data. You might want to look at this thread for a (possibly) helpful discussion of this technique:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71903

You could also simplify your query a bit by using the RADIANS function to do the conversion from degrees to radians:
radians(latitude) instead of latitude*pi()/180

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources