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