-
June 13th, 2013, 08:43 AM
#1
Selecting Rows That Span 360 Degrees
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
QuicknEasySalesPro.com
- your quick and easy, yet powerful solution for managing your
membership site sales, downloads and affiliates.
-
June 13th, 2013, 03:22 PM
#2
Re: Selecting Rows That Span 360 Degrees
Originally Posted by Debbie-Leigh
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 first problem is that you did not define which angle you are considering: the inner or outer angle between the 'from' and 'to' directions? Is it the smallest of the two? Or always clockwise?
Depending on your answer, you could convert the angles to be relative, e.g. to the direction you are searching for, and then compare them.
Cheers, D Drmmr
Please put [code][/code] tags around your code to preserve indentation and make it more readable.
As long as man ascribes to himself what is merely a posibility, he will not work for the attainment of it. - P. D. Ouspensky
-
June 13th, 2013, 08:38 PM
#3
Re: Selecting Rows That Span 360 Degrees
I'm trying to select the rows where the parameter (270 in the example) falls within the range between the from and to values.
The from will always be before the to on the compass, which is where the problem arises when the to spans the 360 point, when it will be numerically less than the from.
This is the conundrum that I'm trying to solve.
QuicknEasySalesPro.com
- your quick and easy, yet powerful solution for managing your
membership site sales, downloads and affiliates.
-
June 14th, 2013, 01:42 AM
#4
Re: Selecting Rows That Span 360 Degrees
Originally Posted by Debbie-Leigh
I'm trying to select the rows where the parameter (270 in the example) falls within the range between the from and to values.
The from will always be before the to on the compass, which is where the problem arises when the to spans the 360 point, when it will be numerically less than the from.
This is the conundrum that I'm trying to solve.
The 'conundrum' is that you have not formulated your problem properly. There is no such thing as 'before' on a compass. Given any two 'from' and 'to' directions, you need to formulate which range of directions you are interested in. There are only a limited number of possibilities:
- ranging from 'from' clockwise to 'to'
- ranging from 'from' counterclockwise to 'to'
- the smallest/largest of the first two options (but you need to disambiguate the case where the angle between 'from' and 'to' is 180 degrees; and you need to consider the case where 'from' is equal to 'to')
Cheers, D Drmmr
Please put [code][/code] tags around your code to preserve indentation and make it more readable.
As long as man ascribes to himself what is merely a posibility, he will not work for the attainment of it. - P. D. Ouspensky
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|