>>
>>So, if I am at latHere and lonHere (consider lat and lon to be stored in degrees
>>
>>
>>
>>select .... where
>> lat between latHere - 1/111 * 20 and latHere + 1/111 * 20
>> and lon between lonHere - 1/111 * 20 and lonHere + 1/111 * 20
>> and (calculatedDistance < 20 )
>>
>>
>>You may add a bit to the range to be sure, like 2 km or so
>>
>>RangeAdd = 2/111
>>
>><pre>
>>select .... where
>> lat between latHere - 1/111 * 20 - RangeAdd and latHere + 1/111 * 20 +RangeAdd
>> and lon between lonHere - 1/111 * 20 - RangeAdd and lonHere + 1/111 * 20 + RangeAdd
>> and (calculatedDistance < 20 )
>>
>>
>>
>
>Thanks, when I try this:
>
>
>declare @CenterLat float=47.7795
>declare @CenterLon float=-65.7191
>declare @SearchDistance float=10000
>
>select *
>from test where
>latitude between @CenterLat - (1/111 * @SearchDistance) and @CenterLat + (1/111 * @SearchDistance) and
>longitude between @CenterLon - (1/111 * @SearchDistance) and @CenterLon + (1/111 * @SearchDistance)
>
>
>It only returns one record however those two records in my table are only 12 to 16 km away. I have attached the table image.
I get both of them
create cursor Coordinates ;
( co_pk I, ;
co_city c(40), ;
co_latitude b(16), ;
co_longitude b(16), ;
co_x b(16), ;
co_y b(16), ;
co_z b(16) ;
)
insert into Coordinates ;
( co_pk, co_city, co_latitude, co_longitude) ;
values ;
(1, 'Here', 47.7795, -65.7191 )
insert into Coordinates ;
( co_pk, co_city, co_latitude, co_longitude) ;
values ;
(2, 'There', 47.5993, -65.6506 )
update Coordinates ;
set co_x = cos(co_latitude*PI()/180) * cos(co_longitude*PI()/180), ;
co_y = cos(co_latitude*PI()/180) * sin(co_longitude*PI()/180), ;
co_z = sin(co_latitude*PI()/180)
CenterLat =47.7795
CenterLon = -65.7191
SearchDistance = 1000
select * ;
from Coordinates ;
where ( co_latitude between CenterLat - (1/111 * SearchDistance) and CenterLat + (1/111 * SearchDistance) ) ;
and ( co_longitude between CenterLon - (1/111 * SearchDistance) and CenterLon + (1/111 * SearchDistance) )
Gregory