Selecting Rows That Span 360 Degrees
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Selecting Rows That Span 360 Degrees

  1. #1
    Join Date
    Feb 2004
    Location
    Bristol, England
    Posts
    66

    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.

  2. #2
    Join Date
    Jul 2005
    Location
    Netherlands
    Posts
    2,004

    Re: Selecting Rows That Span 360 Degrees

    Quote Originally Posted by Debbie-Leigh View Post
    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

  3. #3
    Join Date
    Feb 2004
    Location
    Bristol, England
    Posts
    66

    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.

  4. #4
    Join Date
    Jul 2005
    Location
    Netherlands
    Posts
    2,004

    Re: Selecting Rows That Span 360 Degrees

    Quote Originally Posted by Debbie-Leigh View Post
    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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center