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?

I don't understand what you mean by "radius search" exactly. What is your input and what is your desired output?|||

Thanks for your reply. I am trying to realize a distance serach based on an entered zip code.
MyTable: has fields: ID, Zip,Lat,Long

Assuming user entered UserZip=26511 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.

|||

Depends on how accurate you want the result to be. I'll assume that a margin of error of 10% is acceptable in your case. So if you say 10 miles, it will include all zips between 0 and 9 miles away, some zips that are between 9 and 11, and none that are 11 and over. Accurate enough for find all (somethings) within x miles of me type queries without killing the database server with complex math formulas.

SELECT t1.ID

FROM MyTable t1

JOIN MyTable t2 ON (sqrt(square(69.1*(t2.lat-t1.lat))+square(53.0*(t2.long-t1.long)))<@.Distance)

WHEREt2.Zip=@.StartZip

For highly accurate results, use this:

SELECT t1.ID

FROM MyTable t1

JOIN MyTable t2 ON (3963.0*acos(sin(t1.lat/(180/PI())) * sin(t2.lat/(180/PI())) + cos(t1.lat/(180/PI())) * cos(t2.lat/(180/PI())) * cos(t2.long/(180/PI())-t1.long/(180/PI())))<@.Distance)

WHEREt2.Zip=@.StartZip

The second formula makes the assumption that the earth is a perfect sphere (it's really closer to an oblate spheroid) that has a radius of 3963.0 miles. If you need something more accurate than that (I don't know why you would), let me know and I'll post it up for you, but be warned, it'll probably kill your SQL Server trying to calculate it in real time against 100,000 zip codes.

|||Thanks.sql