Compass Bearings in WHERE clause

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

General SQL Server Forums

New to SQL Server Programming

Compass Bearings in WHERE clause

Author

Topic

mkov02
Starting Member

1 Post

Posted - 2011-08-18 : 07:54:59

Hi All

I've been trying to figure out the following problem without my success.
I want to return all records that have a bearing value (in degrees) within a defined range.

e.g.


@myBearing = 255
@upperLimit = (@myBearing + 30)%360
@lowerLimit = (@myBearing - 30)%360

the following WHERE clause works for this bearing and returns the row with location D and bearing 233 from the table below.

WHERE [BEARING] > @lowerLimit AND [BEARING] < @upperLimit

LOCATION   BEARING
 A           80
 B           125
 C           349
 D           233
 E           05

However if the @myBearing is on the boundary of 0/360 i.e. bearings from 330 to 030, then the rows I don't want are returned.
Does anyone know how I can go about writing a query that can overcome this issue?

I haven't been able to find any information about SQL handling bearing calculations. I think I might need to use a CASE statement, but have now idea how, or even if it will solve my condundrum.

Any help is appreciated.

Cheers
mkov

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-18 : 09:23:31

You have to enforce two rules:
1. All the angles are on the same plane (in the same 360 degree window).
2. The range that you are comparing does not cross the so-called branch cut.

One way to do it would be like this:


set @myBearing = 255
set @upperLimit = @myBearing + 30
set @lowerLimit = @myBearing - 30;
IF @myBearing < 30
BEGIN
	set @upperLimit = @upperLimit + 360;
	set @lowerLimit = @lowerLimit + 360;
END

This would ensure that the values are always greater than 0. Then, when you compare, compare it like this, which would move the bearing values to the same plane as the lower and upper limits.

WHERE CASE WHEN bearing < 30 THEN bearing + 360 ELSE bearing END
BETWEEN @lowerLimit AND @upperLimit

If that does not work for you can you post an example that shows the problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 15:52:19

If you want to use a present index over bearing column, try this

DECLARE	@Sample TABLE
	(
		Location CHAR(1) NOT NULL,
		Bearing	SMALLINT NOT NULL
	)

INSERT	@Sample
	(
		Location,
		Bearing
	)
VALUES	('A',  80),
	('B', 125),
	('C', 349),
	('D', 233),
	('E',   5)

-- Solution by SwePeso
DECLARE	@Bearing SMALLINT = 340,
	@Deviation SMALLINT = 30

IF @Bearing - @Deviation < 0 OR @Bearing + @Deviation >= 360
	SELECT	Location,
		Bearing
	FROM	@Sample
	WHERE	Bearing < (@Bearing + @Deviation) % 360
		OR Bearing > @Bearing - @Deviation
ELSE
	SELECT	Location,
		Bearing
	FROM	@Sample
	WHERE	Bearing > @Bearing - @Deviation
		AND Bearing < @Bearing + @Deviation

But what you really need, is to calculate a bounding box and for those location within the bounding box, apply a distance calculation and filter out those locations within the box but outside the circle.
This is the fastest approach.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources