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...
>
>
|||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 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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>