Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: Selecting Rows That Span 360 Degrees

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

2. 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.

3. 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

4. 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/>

5. 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.

6. 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.

7. 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.

8. 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

9. 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.

10. 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
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...

11. 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 !!

12. 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

13. 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.

14. Yes, they come from two different selects in a union.

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

Debbie

15. 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.]

Page 1 of 2 12 Last

#### Posting Permissions

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