Menu
So I have a table with a bunch of different addresses in it. I need a proc that will select the addresses in that table that are within a specified distance in miles from the passed in lat/long values.
So example of my table:
- messageId
- lat (float)
- long (float)
Proc is passing in another lat/long pair (both floats as well) as well as an int (miles)
I found this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360 to calculate the actual formula but I can't figure out to modify it in proc form to be able to go through an entire list of addresses and only give me the Id's of the addresses that are <= the miles (that I pass in), from the lat/long I pass in.
Can I get any help here?
Thanks!
893
The great circle formula is given by: d = rcos-1[cos a cos b cos(x-y) + sin a sin b]. Given: r = 4.7 km or 4700 m, a, b= 45°, 32° and x, y = 24°,17°.
Euclidean Distance = SquareRoot(((x2-x1)^2)+((y2-y1)^2)) SquareRoot can be written as (something)^(0.5) I implemented like that. CAST(ROUND(LONG_W ,4) as numeric(36,4)) is for taking value upto 4 decimal point.
All of these can be expressed simply in terms of the more familiar trigonometric functions. For example, haversine(θ) = sin²(θ/2). The haversine formula is a very accurate way of computing distances between two points on the surface of a sphere using the latitude and longitude of the two points.
Heres is MySQL query and function which use to get distance between two latitude and longitude and distance will return in KM. SELECT getDistance($lat1,$lng1,$lat2,$lng2) as distance FROM your_table. Almost a decade later, this function gives THE SAME results as Google Maps distance measurement. Thank you!
SQL Server 2008
Using the spacial function STDistance return distance in meters
geography::Point(@lat1, @lon1, 4326).STDistance(geography::Point(@lat2, @lon2, 4326))
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
