>declare @CenterLat float=47.7795 >declare @CenterLon float=-65.7191 >declare @SearchDistance float=250 > >select * > from test >where (latitude>=@CenterLat - @SearchDistance/111.0 and latitude<=@CenterLat + @SearchDistance/111.0) AND > (longitude>=@CenterLon - @SearchDistance/111.0 and longitude<=@CenterLon + @SearchDistance/111.0) AND > 3958.75586574 * ACOS(CASE WHEN SIN(@Latitude1/57.2957795130823) * SIN(@Latitude2/57.2957795130823) + > COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823) < -1 > THEN -1 > WHEN SIN(@Latitude1/57.2957795130823) * SIN(@Latitude2/57.2957795130823) + > COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823) > 1 > THEN 1 > ELSE > SIN(@Latitude1/57.2957795130823) * SIN(@Latitude2/57.2957795130823) + > COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823) < -1 END) <=@SearchDistance >>Just change @Latitude1, @Latitude2, @Longitude1, @Longitude2 to their real names (I'm too lazy right now :o))))
DECLARE @CenterLat Float=47.7795 DECLARE @CenterLon Float=-65.7191 DECLARE @SearchDistance Float=250 SELECT * FROM Test WHERE (Latitude>=@CenterLat-@SearchDistance/111.0 AND Latitude<=@CenterLat+@SearchDistance/111.0) AND (Longitude>=@CenterLon-@SearchDistance/111.0 AND Longitude<=@CenterLon+@SearchDistance/111.0) AND 3958.75586574*ACOS( CASE WHEN SIN(@CenterLat/57.2957795130823)*SIN(Latitude/57.2957795130823)+ COS(@CenterLat/57.2957795130823)*COS(Latitude/57.2957795130823)* COS(Longitude/57.2957795130823-@CenterLon/57.2957795130823)<-1 THEN -1 WHEN SIN(@CenterLat/57.2957795130823)*SIN(Latitude/57.2957795130823)+ COS(@CenterLat/57.2957795130823)*COS(Latitude/57.2957795130823)* COS(Longitude/57.2957795130823-@CenterLon/57.2957795130823)>1 THEN 1 END)<=@SearchDistanceHowever, it returns no record. So, there must be something with the syntax.