q; radius search

I have latitude and longitude in my database, can anyone give me an sql
query how I can make radius search based on that?Not sure about radius but this function will return the distance between two
lat and lons. I am not really sure what you are speaking of in terms of
radius search...Perhaps this will lead you in the right direction...
Create FUNCTION [dbo].[fn_distance_fl](@.sLat int, @.sLon int, @.dLat int,
@.dLon int)
RETURNS float
AS
BEGIN
DECLARE @.fn_distance_fl float
SET @.fn_distance_fl = SQRT( ((0.006917 * (@.sLat - @.dLat) ) * (0.006917 *
(@.sLat - @.dLat)) + ( 0.005756 * (@.sLon - @.dLon) ) * ( 0.005756 * (@.sLon -
@.dLon))))
RETURN(@.fn_distance_fl)
END
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:DEB1B553-ECF2-4A26-B09D-BDF622779463@.microsoft.com...
> I have latitude and longitude in my database, can anyone give me an sql
> query how I can make radius search based on that?
>|||Thanks for the reply. Ok. Here is an example, :
http://www.trucktraderonline.com/dealersearch.html there is a zip and there
is a distance search feature in this site, how are they performing this
search, I need to do the same thing, ge a zip code and do distance search, it
does not seem it involves two lat, long values.
"Warren Brunk" wrote:
> Not sure about radius but this function will return the distance between two
> lat and lons. I am not really sure what you are speaking of in terms of
> radius search...Perhaps this will lead you in the right direction...
> Create FUNCTION [dbo].[fn_distance_fl](@.sLat int, @.sLon int, @.dLat int,
> @.dLon int)
> RETURNS float
> AS
> BEGIN
> DECLARE @.fn_distance_fl float
> SET @.fn_distance_fl = SQRT( ((0.006917 * (@.sLat - @.dLat) ) * (0.006917 *
> (@.sLat - @.dLat)) + ( 0.005756 * (@.sLon - @.dLon) ) * ( 0.005756 * (@.sLon -
> @.dLon))))
> RETURN(@.fn_distance_fl)
> END
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:DEB1B553-ECF2-4A26-B09D-BDF622779463@.microsoft.com...
> >
> > I have latitude and longitude in my database, can anyone give me an sql
> > query how I can make radius search based on that?
> >
>
>|||Thanks for your reply. Can anybody write me that stored procedure,
MyTable: has fields: ID, Zip,Lat,Long
Assuming user entered UserZip and UserRadius=5miles. If these are parameters
for my stored procedure, how should I write my stored procedure to return all
the IDs that meet this criteria.
"Kent Tegels" wrote:
> Hello JIM.H.,
> Actually, it *probably* does. They know table that has the lat/long for some
> number of zip codes, and a table that knows that dealers are in what zip
> codes. So they take your input zip code, get the lat/lon for it, run a query
> against their lat/lon table ordering by the result the distance ascending,
> then join that back to dealer zip codes.
> Its unlike that match found this way is *exactly* 25 miles away from the
> customer, but "as the bird flies," the dealership should be within a 25 mile
> radius "give or take."
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>|||Jim,
Do you have a database of Zip Codes that map a Code to a LAT LON. You will
need to start there. Then you could use my function inside of a stored proc
doing something like.
Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
where [Value] < 5
Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be the
zip code of the dealership.
Hope that helps...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> Thanks for your reply. Can anybody write me that stored procedure,
> MyTable: has fields: ID, Zip,Lat,Long
> Assuming user entered UserZip and UserRadius=5miles. If these are
> parameters
> for my stored procedure, how should I write my stored procedure to return
> all
> the IDs that meet this criteria.
>
> "Kent Tegels" wrote:
>> Hello JIM.H.,
>> Actually, it *probably* does. They know table that has the lat/long for
>> some
>> number of zip codes, and a table that knows that dealers are in what zip
>> codes. So they take your input zip code, get the lat/lon for it, run a
>> query
>> against their lat/lon table ordering by the result the distance
>> ascending,
>> then join that back to dealer zip codes.
>> Its unlike that match found this way is *exactly* 25 miles away from the
>> customer, but "as the bird flies," the dealership should be within a 25
>> mile
>> radius "give or take."
>> Thanks,
>> Kent Tegels
>> http://staff.develop.com/ktegels/
>>|||Hi,
Thanks for your help, I have two problems,
1.Your lat, long value are int, mine are float
2. Your function returns only one value I need to find IDs that mach the
criteria
"Warren Brunk" wrote:
> Jim,
> Do you have a database of Zip Codes that map a Code to a LAT LON. You will
> need to start there. Then you could use my function inside of a stored proc
> doing something like.
> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
> where [Value] < 5
> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be the
> zip code of the dealership.
> Hope that helps...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> > Thanks for your reply. Can anybody write me that stored procedure,
> > MyTable: has fields: ID, Zip,Lat,Long
> >
> > Assuming user entered UserZip and UserRadius=5miles. If these are
> > parameters
> > for my stored procedure, how should I write my stored procedure to return
> > all
> > the IDs that meet this criteria.
> >
> >
> > "Kent Tegels" wrote:
> >
> >> Hello JIM.H.,
> >>
> >> Actually, it *probably* does. They know table that has the lat/long for
> >> some
> >> number of zip codes, and a table that knows that dealers are in what zip
> >> codes. So they take your input zip code, get the lat/lon for it, run a
> >> query
> >> against their lat/lon table ordering by the result the distance
> >> ascending,
> >> then join that back to dealer zip codes.
> >>
> >> Its unlike that match found this way is *exactly* 25 miles away from the
> >> customer, but "as the bird flies," the dealership should be within a 25
> >> mile
> >> radius "give or take."
> >>
> >> Thanks,
> >> Kent Tegels
> >> http://staff.develop.com/ktegels/
> >>
> >>
> >>
>
>|||1. You may change the values to a FLOAT with no ramifications.
2. When you are selecting from a table you can use the function to return
distance for a number of rows...When you use the scalar function on a select
statement it will give you the distance for each row in the select.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17B835D-E718-409E-BC2F-754A93F77732@.microsoft.com...
> Hi,
> Thanks for your help, I have two problems,
> 1.Your lat, long value are int, mine are float
> 2. Your function returns only one value I need to find IDs that mach the
> criteria
>
> "Warren Brunk" wrote:
>> Jim,
>> Do you have a database of Zip Codes that map a Code to a LAT LON. You
>> will
>> need to start there. Then you could use my function inside of a stored
>> proc
>> doing something like.
>> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
>> where [Value] < 5
>> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be
>> the
>> zip code of the dealership.
>> Hope that helps...
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
>> > Thanks for your reply. Can anybody write me that stored procedure,
>> > MyTable: has fields: ID, Zip,Lat,Long
>> >
>> > Assuming user entered UserZip and UserRadius=5miles. If these are
>> > parameters
>> > for my stored procedure, how should I write my stored procedure to
>> > return
>> > all
>> > the IDs that meet this criteria.
>> >
>> >
>> > "Kent Tegels" wrote:
>> >
>> >> Hello JIM.H.,
>> >>
>> >> Actually, it *probably* does. They know table that has the lat/long
>> >> for
>> >> some
>> >> number of zip codes, and a table that knows that dealers are in what
>> >> zip
>> >> codes. So they take your input zip code, get the lat/lon for it, run a
>> >> query
>> >> against their lat/lon table ordering by the result the distance
>> >> ascending,
>> >> then join that back to dealer zip codes.
>> >>
>> >> Its unlike that match found this way is *exactly* 25 miles away from
>> >> the
>> >> customer, but "as the bird flies," the dealership should be within a
>> >> 25
>> >> mile
>> >> radius "give or take."
>> >>
>> >> Thanks,
>> >> Kent Tegels
>> >> http://staff.develop.com/ktegels/
>> >>
>> >>
>> >>
>>|||Thanks Warren,
I tested your function, my test show this always returns numbers close to 0,
what is the unit in here? I need to is the miles.
Thanks for your help.
"Warren Brunk" wrote:
> 1. You may change the values to a FLOAT with no ramifications.
> 2. When you are selecting from a table you can use the function to return
> distance for a number of rows...When you use the scalar function on a select
> statement it will give you the distance for each row in the select.
> thanks,
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:A17B835D-E718-409E-BC2F-754A93F77732@.microsoft.com...
> > Hi,
> > Thanks for your help, I have two problems,
> > 1.Your lat, long value are int, mine are float
> > 2. Your function returns only one value I need to find IDs that mach the
> > criteria
> >
> >
> > "Warren Brunk" wrote:
> >
> >> Jim,
> >> Do you have a database of Zip Codes that map a Code to a LAT LON. You
> >> will
> >> need to start there. Then you could use my function inside of a stored
> >> proc
> >> doing something like.
> >>
> >> Select * from fn_Distance(Lat1,Lon1,Lat2,Lon2)
> >> where [Value] < 5
> >>
> >> Lat 1 and Lon1 should be the starting zip code and lat lon 2 should be
> >> the
> >> zip code of the dealership.
> >>
> >> Hope that helps...
> >>
> >> --
> >> /*
> >> Warren Brunk - MCITP - SQL 2005, MCDBA
> >> www.techintsolutions.com
> >> */
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:276E2AA0-B200-4F48-B8AE-D6F2009F5DAD@.microsoft.com...
> >> > Thanks for your reply. Can anybody write me that stored procedure,
> >> > MyTable: has fields: ID, Zip,Lat,Long
> >> >
> >> > Assuming user entered UserZip and UserRadius=5miles. If these are
> >> > parameters
> >> > for my stored procedure, how should I write my stored procedure to
> >> > return
> >> > all
> >> > the IDs that meet this criteria.
> >> >
> >> >
> >> > "Kent Tegels" wrote:
> >> >
> >> >> Hello JIM.H.,
> >> >>
> >> >> Actually, it *probably* does. They know table that has the lat/long
> >> >> for
> >> >> some
> >> >> number of zip codes, and a table that knows that dealers are in what
> >> >> zip
> >> >> codes. So they take your input zip code, get the lat/lon for it, run a
> >> >> query
> >> >> against their lat/lon table ordering by the result the distance
> >> >> ascending,
> >> >> then join that back to dealer zip codes.
> >> >>
> >> >> Its unlike that match found this way is *exactly* 25 miles away from
> >> >> the
> >> >> customer, but "as the bird flies," the dealership should be within a
> >> >> 25
> >> >> mile
> >> >> radius "give or take."
> >> >>
> >> >> Thanks,
> >> >> Kent Tegels
> >> >> http://staff.develop.com/ktegels/
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>