Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 13th, 2013, 09:44 AM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
xsl import - selecting rows by rowset id kataclysm XSLT 0 May 31st, 2013 11:05 AM
Selecting Rows programmatically in a D.G.V. liamfitz BOOK: Beginning Microsoft Visual Basic 2008 ISBN: 978-0-470-19134-7 0 November 13th, 2009 06:38 PM
Selecting rows for Report stealthdevil Access VBA 10 June 2nd, 2006 04:09 PM
Selecting multiple rows in a flexgrid dfbosse VB How-To 2 May 23rd, 2006 12:18 AM
selecting rows returned by executing a SP dsekar_nat SQL Server 2000 4 May 11th, 2006 11:12 AM



All times are GMT -4. The time now is 08:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
2013 John Wiley & Sons, Inc.