Using Alias in WHERE using geography functionality

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

SQL Server 2008 Forums

Transact-SQL (2008)

Using Alias in WHERE using geography functionality

Author

Topic

BlackIce662
Starting Member

11 Posts

Posted - 2013-09-27 : 04:03:54

Good Day

I would like to restrict my records to distances that are under a certain amount (I handle this later with coding) but the problem is setting the limit in my SQL.

This is my original code:

Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance 
From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10
AND Distance < 20000 AND MAct='Full' Order By Distance ASC

I also tried:

Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance 
From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10
AND Cast((SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) as int) < 20000 
AND MAct='Full' Order By Distance ASC

And I tried:

Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
(SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) As Distance 
From Member WITH(NOLOCK) INNER JOIN FoodType WITH(NOLOCK) ON (Member.FoodTypeID = FoodType.FoodTypeID) Where Zoom >10
AND (SELECT geography::Point(Lat,Lng, 4326).STDistance(geography::Point(-1.33821478, 36.71208143, 4326))) < 20000 
AND MAct='Full' Order By Distance ASC

Thank you

Greatness... Live It!!

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-27 : 07:18:28

How about using a cte to determine the distance before the query?

djj