Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Jul 2007
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Willing to change your SITE table slightly?

    I think I have an answer:
    Code:
    wind_dir_1_from  wind_dir_1_to  wind_dir_2_from  wind_dir_2_to
    15 (NW)          19 (NE)         9 (S)            11 (SW)
    That is, if the "to" is smaller than the "from", then add 16 to the "to".

    And the added 16 means that you use the ALT, else you ignore ALT.

    Still thinking about it, though.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Jul 2007
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Old Pedant,

    Thanks for the suggestion.

    However, I'm not sure that will work, because the values in the site table are the keys of the records on the wind direction table, rather than the compass degrees.

    What I'm trying to do is to join to the wind direction table to use the degrees on there to do the comparison with the parameter value.

    Debbie
    QuicknEasySalesPro.com - your quick and easy, yet powerful solution for managing your membership site sales, downloads and affiliates.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Yes, I know they are keys, but you *can* do a join via wind_dir_1_to - 16

    But I don't think it's needed. I think I'm getting another idea. It just has to percolate to the surface.<grin/>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Okay...got it.
    Code:
    SELECT S.sitename, W1.wind_dir_name, w1.wind_dir_degrees, W2.wind_dir_name, w2.wind_dir_degrees
    FROM site AS S, wind_direction AS W1, wind_direction AS W2
    WHERE S.wind_dir_1_from  = W1.wind_dir_id
      AND S.wind_dir_1_to    = W2.wind_dir_id
      AND (
            ( S.wind_dir_1_to > S.wind_dir_1_from
              AND 270 BETWEEN W1.wind_dir_degrees AND W2.wind_dir_degrees
            )
            OR
            ( S.wind_dir_1_to < S.wind_dir_1_from
              AND (    270 BETWEEN W1.wind_dir_degrees AND 360
                    OR 270 BETWEEN 0 AND W2.wind_dir_degrees
                  )
            )
          )
    And of course replace 270 with, say, 10 and it works.

    Or replace 270 with 347.771 and it works.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Convert that into a stored procedure:
    Code:
    delimiter //
    
    CREATE PROCEDURE windinfo( angle DECIMAL(10,3) )
    BEGIN
    
    SELECT S.sitename, W1.wind_dir_name, w1.wind_dir_degrees, W2.wind_dir_name, w2.wind_dir_degrees
    FROM site AS S, wind_direction AS W1, wind_direction AS W2
    WHERE S.wind_dir_1_from  = W1.wind_dir_id
      AND S.wind_dir_1_to    = W2.wind_dir_id
      AND (
            ( S.wind_dir_1_to > S.wind_dir_1_from
              AND angle BETWEEN W1.wind_dir_degrees AND W2.wind_dir_degrees
            )
            OR
            ( S.wind_dir_1_to < S.wind_dir_1_from
              AND (    angle BETWEEN W1.wind_dir_degrees AND 360
                    OR angle BETWEEN 0 AND W2.wind_dir_degrees
                  )
            )
          )
    ;
    
    END 
    //
    
    delimiter ;
    And then you can do
    Code:
    CALL windinfo(270);
    -- empty set
    CALL windinfo(37.33);
    +----------+---------------+------------------+---------------+------------------+
    | sitename | wind_dir_name | wind_dir_degrees | wind_dir_name | wind_dir_degrees |
    +----------+---------------+------------------+---------------+------------------+
    | test1    | NW            |            315.0 | NE            |             45.0 |
    +----------+---------------+------------------+---------------+------------------+
    And so on.

    And yes, I forgot to alias the names/degrees. You can do that.
    Last edited by Old Pedant; 06-13-2013 at 10:05 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    If it's not obvious, this means you *MUST* adhere to putting the from-->>to in the SITE table in clockwise order. Which is what you showed, so I assume that's what you would do, anyway.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Jul 2007
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Old Pedant,

    Wow, that took a while to get my head around, but yes, of course the secret is to split the comparison for the 360 spanning part. Thanks for the help.

    I'm now trying to add in the columns for the second range to say "return the rows where the parameter is in either the first or the second range", but just duplicating the comparison clause and putting an OR in between just returns every combination on the table. An AND returns nothing.

    Maybe it's because it too late here and my brain's already gone to sleep, but I can't spot why. The query I'm using is:

    Code:
    SELECT S.sitename, W1.wind_dir_name, w1.wind_dir_degrees, W2.wind_dir_name, w2.wind_dir_degrees
    FROM site AS S, wind_direction AS W1, wind_direction AS W2, wind_direction AS W3, wind_direction AS W4
    WHERE S.wind_dir_1_from  = W1.wind_dir_id
      AND S.wind_dir_1_to    = W2.wind_dir_id
      AND S.wind_dir_2_from  = W3.wind_dir_id
      AND S.wind_dir_2_to    = W4.wind_dir_id
      AND (
            ( S.wind_dir_1_to > S.wind_dir_1_from
              AND 270 BETWEEN W1.wind_dir_degrees AND W2.wind_dir_degrees
            )
            OR
            ( S.wind_dir_1_to < S.wind_dir_1_from
              AND (    270 BETWEEN W1.wind_dir_degrees AND 360
                    OR 270 BETWEEN 0 AND W2.wind_dir_degrees
                  )
            )
          )
       OR (
            ( S.wind_dir_2_to > S.wind_dir_2_from
              AND 270 BETWEEN W3.wind_dir_degrees AND W4.wind_dir_degrees
            )
            OR
            ( S.wind_dir_2_to < S.wind_dir_2_from
              AND (    270 BETWEEN W3.wind_dir_degrees AND 360
                    OR 270 BETWEEN 0 AND W4.wind_dir_degrees
                  )
            )
          )
    Have I missed something obvious?

    Also, I haven't used stored procedures before, so can you give a hint of how to use them? For instance, can they be used like a sub-query e.g. could I call it from a WHERE clause like: WHERE column IN CALL windinfo(270), if only one column was returned from the stored procedure?

    Debbie
    QuicknEasySalesPro.com - your quick and easy, yet powerful solution for managing your membership site sales, downloads and affiliates.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You can't use a stored procedure as if it's a recordset. But you could possibly use a stored function, instead.

    But let's get your query working, first.
    Code:
    delimiter //
    
    CREATE PROCEDURE windinfo( angle DECIMAL(10,4) )
    BEGIN
    
    SELECT S.sitename, W1.wind_dir_name AS name1, w1.wind_dir_degrees AS degrees1, 
                       W2.wind_dir_name AS name2, w2.wind_dir_degrees AS degrees2
    FROM site AS S, wind_direction AS W1, wind_direction AS W2
    WHERE 
        (     S.wind_dir_1_from  = W1.wind_dir_id
          AND S.wind_dir_1_to    = W2.wind_dir_id
          AND 
            (
                ( S.wind_dir_1_to > S.wind_dir_1_from
                  AND angle BETWEEN W1.wind_dir_degrees AND W2.wind_dir_degrees
                )
                OR
                ( S.wind_dir_1_to < S.wind_dir_1_from
                  AND (    angle BETWEEN W1.wind_dir_degrees AND 360
                        OR angle BETWEEN 0 AND W2.wind_dir_degrees
                      )
                )
              )
        )
        OR
        (     S.wind_dir_2_from  = W1.wind_dir_id
          AND S.wind_dir_2_to    = W2.wind_dir_id
          AND 
            (
                ( S.wind_dir_2_to > S.wind_dir_2_from
                  AND angle BETWEEN W1.wind_dir_degrees AND W2.wind_dir_degrees
                )
                OR
                ( S.wind_dir_2_to < S.wind_dir_2_from
                  AND (    angle BETWEEN W1.wind_dir_degrees AND 360
                        OR angle BETWEEN 0 AND W2.wind_dir_degrees
                      )
                )
              )
        )      
    ;
    
    END 
    //
    
    delimiter ;
    And then I tested it:
    Code:
    mysql> call windinfo(7);
    +----------+-------+----------+-------+----------+
    | sitename | name1 | degrees1 | name2 | degrees2 |
    +----------+-------+----------+-------+----------+
    | test1    | NW    |    315.0 | NE    |     45.0 |
    +----------+-------+----------+-------+----------+
    
    mysql> call windinfo(197);
    +----------+-------+----------+-------+----------+
    | sitename | name1 | degrees1 | name2 | degrees2 |
    +----------+-------+----------+-------+----------+
    | test1    | S     |    180.0 | SW    |    225.0 |
    +----------+-------+----------+-------+----------+
    
    mysql> call windinfo( 271.33 );
    Empty set (0.00 sec)
    
    mysql> call windinfo( 53.1 );
    Empty set (0.00 sec)
    Okay? Just a matter of getting the parentheses in the right places.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    And I just realized that I was mixing up MySQL functions and SQL Server functions. SQL Server functions *can* return a table (or pseudo-table) and so can be JOINed to. But MySQL isn't that sophisticated.

    The best you could do in MySQL would be to use a Stored Procedure to create at TEMPORARY TABLE. And then you could join to that temporary table *after* making the CALL to the procedure.

    And that's really okay. See here:
    http://dev.mysql.com/doc/refman/5.5/...ate-table.html
    And find and read:
    Temporary Tables
    You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name.
    Not something I would do as a matter of course, but if it's the only viable alternative...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Just for fun, I added another row to my site table, which now looks like this:
    Code:
    mysql> select * from site;
    +----------+-----------------+---------------+-----------------+---------------+
    | sitename | wind_dir_1_from | wind_dir_1_to | wind_dir_2_from | wind_dir_2_to |
    +----------+-----------------+---------------+-----------------+---------------+
    | test1    |              15 |             3 |               9 |            11 |
    | test2    |              10 |            13 |               5 |             8 |
    +----------+-----------------+---------------+-----------------+---------------+
    and then I did this:
    Code:
    mysql> call windinfo(220);
    +----------+-------+----------+-------+----------+
    | sitename | name1 | degrees1 | name2 | degrees2 |
    +----------+-------+----------+-------+----------+
    | test1    | S     |    180.0 | SW    |    225.0 |
    | test2    | SSW   |    202.5 | W     |    270.0 |
    +----------+-------+----------+-------+----------+
    Using wind_dir_1 from test1 and wind_dir_2 from test2 !!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New Coder
    Join Date
    Jul 2007
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Old Pedant,

    I've been playing around with various ideas based on yours and have finally got close to something that works with the extra columns and data I that I need to use on the site table that I didn't originally include in this post for ease of explanation.

    However, I'm stuck on the last bit. The rows returned have the different wind ranges on different rows e.g.
    Code:
    +----------+-----------------+---------------+-----------------+---------------+
    | id       | wind_dir_1_from | wind_dir_1_to | wind_dir_2_from | wind_dir_2_to |
    +----------+-----------------+---------------+-----------------+---------------+
    | 1        |              15 |             3 |            NULL |          NULL |
    | 1        |            NULL |          NULL |              12 |            13 |
    +----------+-----------------+---------------+-----------------+---------------+
    Is there a SQL command or a technique to return just one row like this:
    Code:
    +----------+-----------------+---------------+-----------------+---------------+
    | id       | wind_dir_1_from | wind_dir_1_to | wind_dir_2_from | wind_dir_2_to |
    +----------+-----------------+---------------+-----------------+---------------+
    | 1        |              15 |             3 |              12 |            13 |
    +----------+-----------------+---------------+-----------------+---------------+
    Debbie
    QuicknEasySalesPro.com - your quick and easy, yet powerful solution for managing your membership site sales, downloads and affiliates.

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Ugh. No easy way.

    If those two lines come from two separate SELECTs, then yes, we could probably do it.

    This might be easier to solve in your (assumed) PHP code than in the SQL query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #14
    New Coder
    Join Date
    Jul 2007
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, they come from two different selects in a union.

    I'm intrigued. What could be done in that case?

    Debbie
    QuicknEasySalesPro.com - your quick and easy, yet powerful solution for managing your membership site sales, downloads and affiliates.

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Ahhh...then it's not hard.

    Code:
    SELECT U.id, 
           MAX(U.wind_dir_1_from) AS from1,
           MAX(U.wind_dir_1_to  ) AS to1,
           MAX(U.wind_dir_2_from) AS from2,
           MAX(U.wind_dir_2_to  ) AS to2
    FROM (
        SELECT ... 
        UNION
        SELECT ...
        ) AS U
    GROUP BY U.id
    ORDER BY U.id
    A non-null value will always be larger than a NULL value for purposes of MAX().
    [Actually, a non-null value will always be smaller than a NULL for purposes of MIN(), so you could use MIN() in place of MAX() and it would work as well.]
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •