Dr. Roberto Rossi

Search this site

Navigation

"To know the road ahead, ask those coming back",
Chinese proverb

"Not everything that counts can be counted, and not everything that can be counted counts",
A. Einstein

"Like everything metaphysical the harmony between thought and reality is to be found in the grammar of the language",
L. Wittgenstein

"The greatest challenge to any thinker is stating the problem in a way that will allow a solution",
B. Russell 

"...no general method for the solution of questions in the theory of probabilities can be established which does not explicitly recognise... those universal laws of thought which are the basis of all reasoning", 
G. Boole

"No great discovery was ever made without a bold guess",
Sir. I. Newton

"All truths are easy to understand once they are discovered; the point is to discover them",
G. Galilei

"In the universe many things, in spite of their differences among themselves, can be united on account of their common limits",
G. Gemistos

"Alea iacta est",
G. Cesare, Rimini, January the 10th, 49 BC

"Wisdom is the principal thing, therefore get wisdom, and with all thy getting, get understanding. Exalt her and she shall bring thee to honour", 
Proverbs 4:7

"A good decision is based on knowledge and not on numbers",
Plato

"Rest satisfied with doing well, and leave others to talk of you as they please",
Pythagoras

"If you search for the laws of harmony, you will find knowledge",
inscription on a wall of Luxor temple

A Weather Forecasting System in MySQL

In this page we discuss the design and implementation of a database for managing  information from a weather monitoring and forecasting system.

The exercise was part of the final exam for the course Data Management INF-21306.

The design and implementation presented below were developed in MySQL.

A pdf presentation is available here.

The Assignment

We now present the text of the assignment.

A Weather Forecasting and Monitoring System


The Entity/Relationship Diagram

In the following picture, the E/R diagram for the assignment is presented. The MySQL workbench
project file can be downloaded from this link.


Data Definition and Insertion Statements

The following code was automatically generated by MySQL workbench out of the above E/R diagrams by
using the command "Forward Engineering". This code can be executed on MySQL community server.

Data Definition and Insertion Statements

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`property`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`property` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`property` (
  `ptyname` VARCHAR(45) NOT NULL ,
  `unit` VARCHAR(45) NULL ,
  PRIMARY KEY (`ptyname`) )
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `mydb`.`station`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`station` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`station` (
  `location` POINT NOT NULL ,
  `elevation` INT NULL ,
  PRIMARY KEY (`location`) )
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `mydb`.`installedSensor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`installedSensor` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`installedSensor` (
  `location` POINT NOT NULL ,
  `property` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`location`, `property`) ,
  INDEX `station` (`location` ASC) ,
  INDEX `sensor` (`property` ASC) ,
  CONSTRAINT `station`
    FOREIGN KEY (`location` )
    REFERENCES `mydb`.`station` (`location` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `sensor`
    FOREIGN KEY (`property` )
    REFERENCES `mydb`.`property` (`ptyname` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `mydb`.`measurement`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`measurement` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`measurement` (
  `location` POINT NOT NULL ,
  `property` VARCHAR(45) NOT NULL ,
  `dateTime` DATETIME NOT NULL ,
  `value` DECIMAL(2) NULL ,
  PRIMARY KEY (`location`, `property`, `dateTime`) ,
  INDEX `sensor` (`location` ASC, `property` ASC) ,
  CONSTRAINT `sensor`
    FOREIGN KEY (`location` , `property` )
    REFERENCES `mydb`.`installedSensor` (`location` , `property` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `mydb`.`forecast`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`forecast` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`forecast` (
  `location` POINT NOT NULL ,
  `property` VARCHAR(45) NOT NULL ,
  `dateTime` DATETIME NOT NULL ,
  `value` DECIMAL(2) NULL ,
  PRIMARY KEY (`location`, `property`, `dateTime`) ,
  INDEX `sensors` (`location` ASC, `property` ASC) ,
  CONSTRAINT `sensors`
    FOREIGN KEY (`location` , `property` )
    REFERENCES `mydb`.`installedSensor` (`location` , `property` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`property`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`property` (`ptyname`, `unit`) VALUES ('temperature', 'C');
INSERT INTO `mydb`.`property` (`ptyname`, `unit`) VALUES ('pressure', 'mBars');
INSERT INTO `mydb`.`property` (`ptyname`, `unit`) VALUES ('humidity', 'perc');
INSERT INTO `mydb`.`property` (`ptyname`, `unit`) VALUES ('rain', 'mm/h');
INSERT INTO `mydb`.`property` (`ptyname`, `unit`) VALUES ('windSpeed', 'm/s');
INSERT INTO `mydb`.`property` (`ptyname`, `unit`) VALUES ('windDirection', 'n/a');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`station`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`station` (`location`, `elevation`) VALUES (GeomFromText('POINT(0 0)'), '5');
INSERT INTO `mydb`.`station` (`location`, `elevation`) VALUES (GeomFromText('POINT(1 1)'), '10');
INSERT INTO `mydb`.`station` (`location`, `elevation`) VALUES (GeomFromText('POINT(2 3)'), '15');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`installedSensor`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`installedSensor` (`location`, `property`) VALUES (GeomFromText('POINT(2 3)'), 'temperature');
INSERT INTO `mydb`.`installedSensor` (`location`, `property`) VALUES (GeomFromText('POINT(0 0)'), 'pressure');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`measurement`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`measurement` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(0 0)'), 'pressure', '2010-01-21 12:00:00', '12');
INSERT INTO `mydb`.`measurement` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(2 3)'), 'temperature', '2010-01-21 12:00:00', '23');
INSERT INTO `mydb`.`measurement` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(2 3)'), 'temperature', '2010-01-21 00:00:00', '12');
INSERT INTO `mydb`.`measurement` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(0 0)'), 'rain', '2010-01-21 12:00:00', '0');
INSERT INTO `mydb`.`measurement` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(0 0)'), 'pressure', '2010-01-22 12:00:00', '12');
INSERT INTO `mydb`.`measurement` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(0 0)'), 'rain', '2010-01-22 12:00:00', '5');

COMMIT;

-- -----------------------------------------------------
-- Data for table `mydb`.`forecast`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`forecast` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(0 0)'), 'pressure', '2010-02-21', '12');
INSERT INTO `mydb`.`forecast` (`location`, `property`, `dateTime`, `value`) VALUES (GeomFromText('POINT(2 3)'), 'temperature', '2010-02-21', '32');

COMMIT;


SQL Queries

Below we present the four SQL queries that perform the tasks required in the assignment. Furthermore,
the bonus query that implements a weather forecasting functionality is also presented.

Query 1

SELECT asWKT(mydb.measurement.location), mydb.measurement.dateTime, mydb.measurement.value 
FROM mydb.measurement
WHERE 
mydb.measurement.location = GeomFromText('POINT(0 0)') AND
mydb.measurement.property = 'pressure';

Query 2

SELECT asWKT(mydb.measurement.location), MIN(mydb.measurement.value) as MIN, MAX(mydb.measurement.value) as MAX, AVG(mydb.measurement.value) as AVG
FROM mydb.measurement
WHERE 
mydb.measurement.location = GeomFromText('POINT(2 3)') AND
mydb.measurement.property = 'temperature';
GROUP BY DATE(mydb.measurement.datetime)
HAVING DATE(mydb.measurement.datetime) = '2010-01-21';

Query 3

SELECT DISTINCT COUNT(mydb.measurement.location)
FROM mydb.measurement
WHERE 
DATE(mydb.measurement.datetime) = '2010-01-21' AND
mydb.measurement.property = 'temperature' AND
mydb.measurement.value >=
(SELECT AVG(mydb.measurement.value)
FROM mydb.measurement
WHERE 
mydb.measurement.property = 'temperature'
GROUP BY DATE(mydb.measurement.datetime)
HAVING DATE(mydb.measurement.datetime) = '2010-01-21');

Query 4

INSERT INTO property VALUE('visibility','m');
INSERT INTO installedsensor VALUE(GeomFromText('POINT(0 0)'),'visibility');
INSERT INTO measurement VALUE(GeomFromText('POINT(0 0)'),'visibility','2010-01-21 12:00:00',20);


Bonus Query

SELECT COUNT(*)
FROM mydb.measurement mRain, mydb.measurement mPressure
WHERE 
mRain.property = 'rain' AND mRain.value > 0 AND
mPressure.property = 'pressure' AND mPressure.value = 12 AND
mRain.dateTime = mPressure.dateTime;

SELECT COUNT(*)
FROM mydb.measurement mPressure
WHERE 
mPressure.property = 'pressure' AND mPressure.value = 12;

In the bonus query, the probability is obtained by dividing the result of the first query by the result of
the second one.
Č
Ċ
ď
v.1
weather.pdf
(230k)
Roberto Rossi,
Jan 12, 2011 6:43 AM
ċ
ď
v.1
weatherService.mwb
(9k)
Roberto Rossi,
Sep 16, 2010 5:08 PM