INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Selecting Rows That Span 360 Degrees

Selecting Rows That Span 360 Degrees

(OP)
Hi,

Here's a brain puzzler for anyone who likes a challenge or has already come across a similar problem and has a solution for it.

I have a table that holds wind directions and another that holds records that link to that table:

CODE

DROP TABLE IF EXISTS `wind_direction`;
CREATE TABLE IF NOT EXISTS `wind_direction` (
  `wind_dir_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `wind_dir_name` varchar(20) NOT NULL DEFAULT '',
  `wind_dir_degrees` decimal(4,1) NOT NULL DEFAULT '0',
  `wind_dir_degrees_alt` decimal(4,1) NOT NULL DEFAULT '0',
  `created_ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`wind_dir_id`),
  UNIQUE KEY `ux_wind_dir_name` (`wind_dir_name`),
  KEY `ix_wind_dir_degrees` (`wind_dir_degrees`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=0 COMMENT='Wind Directions' AUTO_INCREMENT=1 ;

INSERT INTO `wind_direction` (`wind_dir_id`, `wind_dir_name`, `wind_dir_degrees`, `wind_dir_degrees_alt`, `created_ts`, `updated_ts`) VALUES
(1,  'N',       0,   360, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(2,  'NNE',  22.5, 382.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(3,  'NE',     45,   405, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(4,  'ENE',  67.5, 427.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(5,  'E',      90,   450, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(6,  'ESE', 112.5, 472.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(7,  'SE',    135,   495, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(8,  'SSE', 157.5, 517.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(9,  'S',     180,   540, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(10, 'SSW', 202.5, 562.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(11, 'SW',    225,   585, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(12, 'WSW', 247.5, 607.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(13, 'W',     270,   630, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(14, 'WNW', 292.5, 652.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(15, 'NW',    315,   675, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
(16, 'NNW', 337.5, 697.5, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP); 

The columns in table 2 that link to the wind_direction table are defined like this:

CODE

`wind_dir_1_from` int(11) unsigned DEFAULT NULL,
`wind_dir_1_to`   int(11) unsigned DEFAULT NULL,
`wind_dir_2_from` int(11) unsigned DEFAULT NULL,
`wind_dir_2_to`   int(11) unsigned DEFAULT NULL, 

An example row in table 2 is:

CODE

wind_dir_1_from  wind_dir_1_to  wind_dir_2_from  wind_dir_3_to
15 (NW)          3 (NE)         9 (S)            11 (SW) 

(The letters in brackets are only there for ease of reference)

I'm trying to build a query that accepts a given wind direction e.g. N and retrieves the rows from table 2 where the wind direction falls within the FROM and TO values.

The puzzler bit comes when the FROM and TO spans the 360 point on the compass. I've been trying to wrap my brains around this for several days now, but still can't figure out how to match the TO column, in a query, to the correct wind direction row when it has spanned the 360 point.

As you can see from the table definition, I thought that if I added another column that extended the degrees beyond 360, I could just do an OR to check the alt column as well, but it gave another problem as explained below:

The current incarnation of my query is:

CODE

SELECT site.site_id
	 , site.site_name
	 , wndr1.wind_dir_degrees     AS wind_dir_1_from
	 , wndr2.wind_dir_degrees     AS wind_dir_1_to
	 , wndr2.wind_dir_degrees_alt AS wind_dir_1_to_alt
  FROM site           site
	 , wind_direction wndr1
	 , wind_direction wndr2
 WHERE (270 >= wndr1.wind_dir_degrees
   AND  (270 <= wndr2.wind_dir_degrees
	OR   270 <= wndr2.wind_dir_degrees_alt)
	   )
   AND site.wind_dir_1_from = wndr1.wind_dir_id
   AND site.wind_dir_1_to   = wndr2.wind_dir_id 

When I use 315, 0 or 180 as the parameter, it correctly returns the example row, but when I use 90 or 270, which are out of the scope of both ranges, it shouldn't return a row, but it still does, because the wind_dir_degrees_alt for the TO columns is greater than the parameter. Grrr.

I'll be eternally grateful if anyone can enlighten me as to how I can return the correct row(s) when the FROM and TO columns span the 360 point.

The definitions of the wind direction table and table 2 are eminently tweakable to include any axtra information necessary to achieve this elusive goal or even dumpable, if a completely different solution can be proposed.

Debbie

[color=#F98537]
Debbie Figg runs Quick-Computer-Solutions.com - your One Stop Solution Shop to your computer problems covering every problem area you would come across.
[/color]

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close