Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Points within a radius using pythagoras only

Author  Topic

schott19
Starting Member

5 Posts

 Posted - 2011-12-29 : 00:47:20 Hi,I know it is a common question about working out how many points (lon, lat) fall within a radius of another point but most answers and worked examples are using great circle.I dont need that kind of accuracy so i want to use pythagoras to do it. However, i just cant find any worked examples - i just see people say use "Pythagoras". I understand pythagoras of course but im struggling with converting the radius in to miles / km and returning the correct results. Please could somebody be kind enough to do a worked example as i dont believe what i have is correct..The table contains various points with lon and lat values. I want to provide my current long and lat, a radius and the unit of measure (M or K) as params and get a list of all the points within that radius.this is what i have but it doesnt work:@Distance = 1@Unit = 'K'--i.e. 1KM radius IF @Unit = 'M' set @Offset = (@Distance/0.00062)/100000 ELSE -- must be km set @Offset = @Distance/100000 SELECT * FROM dbo.points WHERE(Latitude between (@Lat-@Offset) and (@Lat+@Offset)) AND (Longitude between (@Long-@Offset) and (@Long+@Offset))Really hope someone can help as i keep re-reading the same posts all the time and going round in circles :) Thanks

boogiezy
Starting Member

12 Posts

 Posted - 2011-12-29 : 18:57:49 I looked up http://en.wikipedia.org/wiki/Longitude > Degree length > "Surface distance per 1° change in latitude" and "Surface distance per 1° change in longitude"Using 30° (around east coast) as your latitude origin, yields Offsets 37.35 seconds (or 0.6225) for Longitude and 32.48 seconds(or 0.5413) for Latitude, PER 1 Km.Change your Offset variables and try again. Hope this helps.Thanks,ZY

schott19
Starting Member

5 Posts

 Posted - 2011-12-29 : 19:39:06 Hi ZY,thanks for your reply - i think i have modified the sql as it should be but it still isnt working correctly - is the below what you expect to see?Many thanksmy lat and lon values are:point of origin: -33.88289498859485 , 151.22980040608218point that should sit approx 1km from origin (this is stored in the table points) -33.883000, 151.217000if i pass in @distance as 0.5 it still returns a result declare @OffsetLat DECIMAL(9,6) declare @OffsetLon DECIMAL(9,6) --km only SET @OffsetLat = 0.5413 * @Distance SET @OffsetLon = 0.6225 * @Distance SELECT * FROM dbo.points WHERE (Latitude between (@Lat-@OffsetLat) and (@Lat+@OffsetLat)) AND (Longitude between (@Long-@OffsetLon) and (@Long+@OffsetLon))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

 Posted - 2011-12-29 : 22:02:36 The function on the link below should provide good results.Function F_FIND_SEARCH_LIMITS will return values for a bounded search:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112369CODO ERGO SUM

schott19
Starting Member

5 Posts

 Posted - 2011-12-29 : 23:51:19 Hi Michael,Thanks for the code for the function but i want to avoid the great circle method as i dont need that kind of accuracy... I have indexes on my lat and lng columns so i can very quickly return results using Pythagoras but i can't seem to find a worked example for this.I have found lots for great circle :(

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

 Posted - 2011-12-30 : 00:40:22 quote:Originally posted by schott19Hi Michael,Thanks for the code for the function but i want to avoid the great circle method as i dont need that kind of accuracy... I have indexes on my lat and lng columns so i can very quickly return results using Pythagoras but i can't seem to find a worked example for this.I have found lots for great circle :(Since you haven't been able to get your code to work, I thought I would post some code that does work, just in case that's important to you.At the very least, you can use it to test your code against it.CODO ERGO SUM

Kristen
Test

22859 Posts

 Posted - 2011-12-30 : 05:35:27 I think we are talking the same thing, but I do:Define (Lat & Long) co-ordinates for the top-left and bottom-right of a box whose sides are the "radius" of your search. e.g. 1 km.Find all points within that box - similar to your code:`WHERE (Latitude between (@Lat-@OffsetLat) and (@Lat+@OffsetLat)) AND (Longitude between (@Long-@OffsetLon) and (@Long+@OffsetLon))`and then apply Great Circle formula to those to trim them to only those within a circle, for accuracy.However, I think this is your problem:`SET @OffsetLat = 0.5413 * @DistanceSET @OffsetLon = 0.6225 * @Distance`unless you are dealing with a very small zone, within the world, and it is not close to the poles, then I think this is too Rough & Ready.Calculate the Top-Left (i.e. North West) point of the square, and the Bottom-Righ (i.e. South East) accurately, then:`WHERE (Latitude between @BottomRightLat and @TopLeftLat) AND (Longitude between @TopLeftLong and @BottomRightLong)`some caution may be required when the "square" spans 0 degrees (or wherever the wrap-around-point is.See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85363#314900 (which also includes a comment from MVJ that care is needed when calculating the co-ordinates of the square to be sure it fully contains the circle)

boogiezy
Starting Member

12 Posts

 Posted - 2011-12-30 : 11:24:34 I checked google map and realized the offset I gave you was incorrect...Using Wikipedia's numbers at 30 degree: 110.852 km and 96.486 kmPoint of origin: -33.88289498859485 , 151.22980040608218distance 1 KM (4 Square KM box)Lat between -33.87387395 and -33.89191603Long between 151.2401646 and 151.2194362so try these new offsets:SET @OffsetLat = @Distance / 110.852SET @OffsetLon = @Distance / 96.486 SELECT * FROM dbo.points WHERE(Latitude between (@Lat-@Offset) and (@Lat+@Offset))AND (Longitude between (@Long-@Offset) and (@Long+@Offset))Thanks,ZY

schott19
Starting Member

5 Posts

 Posted - 2012-01-03 : 01:41:41 Hi All,Thanks for all your great posts - i've found one that seems the most correct so I will put it here in case anyone else needs it.Special thanks to boogiezy - the below is very similar to your solution but accounts for the curve when getting the longitude range. And also to MVJ - on the post where i found this solution you have confirmed the caclulations are correctoriginal post i got this code from: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111406DECLARE @StartLatitude floatDECLARE @StartLongitude floatDECLARE @Miles floatDECLARE @HighLatitude floatDECLARE @LowLatitude floatDECLARE @HighLongitude floatDECLARE @LowLongitude floatDECLARE @LatitudeRange floatDECLARE @LongitudeRange floatSET @StartLatitude = -33.88289498859485SET @StartLongitude = 151.22980040608218SET @Miles = (@km * 0.621)Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)Set @LatitudeRange = @Miles / 69.045454545454545454545454545455Set @LowLatitude = @StartLatitude - @LatitudeRangeSet @HighLatitude = @StartLatitude + @LatitudeRangeSet @LowLongitude = @StartLongitude - @LongitudeRangeSet @HighLongitude = @StartLongitude + @LongitudeRangeSELECT Latitude, Longitude FROM WHERE (Latitude <= @HighLatitude AND Latitude >= @LowLatitude) AND (Longitude <= @HighLongitude AND Longitude >= @LowLongitude))

schott19
Starting Member

5 Posts

 Posted - 2012-01-03 : 01:42:50 just realised that this isnt really pythagoras anymore but that was my starting point when investigating how to do this..