Points within a radius using pythagoras only

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 thanks

my lat and lon values are:

point of origin: -33.88289498859485 , 151.22980040608218
point that should sit approx 1km from origin (this is stored in the table points) -33.883000, 151.217000

if 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=112369

CODO 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 schott19

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 :(

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 * @Distance
SET @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 km

Point of origin: -33.88289498859485 , 151.22980040608218
distance 1 KM (4 Square KM box)
Lat between -33.87387395 and -33.89191603
Long between 151.2401646 and 151.2194362

so try these new offsets:
SET @OffsetLat = @Distance / 110.852
SET @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 correct

original post i got this code from: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111406

DECLARE @StartLatitude float
DECLARE @StartLongitude float
DECLARE @Miles float
DECLARE @HighLatitude float
DECLARE @LowLatitude float
DECLARE @HighLongitude float
DECLARE @LowLongitude float
DECLARE @LatitudeRange float
DECLARE @LongitudeRange float

SET @StartLatitude = -33.88289498859485
SET @StartLongitude = 151.22980040608218
SET @Miles = (@km * 0.621)

Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)
Set @LatitudeRange = @Miles / 69.045454545454545454545454545455
Set @LowLatitude = @StartLatitude - @LatitudeRange
Set @HighLatitude = @StartLatitude + @LatitudeRange
Set @LowLongitude = @StartLongitude - @LongitudeRange
Set @HighLongitude = @StartLongitude + @LongitudeRange

SELECT Latitude, Longitude FROM <Yourtable> 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..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources