By Anton Strand (as224xs) and Emil Larsson (el223nc)
We chose to create a "Compare weather" app. The user is able to compare the weather between two cities during a specified time limit.
This application is directed to people who are either looking for a place to spend their holiday or perhaps even looking for a new city to move to. By be able to compare the weather between two cities might make the decision easier to make.
This is just a prototype so the number of cities will be limited. The main functionality will be to be able to compare temperatures and rainfall between two cities during the time span specified by the user.
The data will be imported from SMHI.
We chose to have an entity set for the cities with attributes for the name as well as some information about the city. The weather data is collected by different types of weather stations. We chose to separate the relation to the stations, one for each type. This is to only have the right type of station in the entity set when trying to find data. There is no need to go through all rain stations when trying to find the temperature. The actual weather data is also separated for the same reason and from the station sets to reduce duplication and follow the normalisation rules. Instead of letting the city contain all possible stations for the city we chose to let the station know which city it belongs to. This makes it easy to remove all the stations for a city but not removing the city from the database. The same can be said about the weather data and the correlating station.
As for now all the information we need for the city is the name and the information text. The name is unique and can be used as a primary key. Since the name is use a lot in the queries we chose to use it as an index as well.
CREATE TABLE IF NOT EXISTS city
(
name varchar(20),
informationText text,
INDEX(name),
PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;We chose to convert the relation between the city and the temperature to a table called "tempStation" and the other one "rainStation". It contains all the information mentioned in the E/R diagram. Station is a unique name that SMHI has given the station. We will use this as a primary key. Even though they are more or less identical we chose to separate them since they contains different types of stations. This could have been solved by adding a "type" attribute but it would resolve in unnecessary stations to loop through to find the ones containing the specific type of information when doing queries.
CREATE TABLE IF NOT EXISTS tempStation
(
city varchar(20),
station varchar(20),
INDEX(station, city),
PRIMARY KEY (station)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;CREATE TABLE IF NOT EXISTS rainStation
(
city varchar(20),
station varchar(20),
INDEX(station, city),
PRIMARY KEY (station)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;The temperature for each day and station will be stored in the temperature table. Since the combination of the timestamp and the station is unique we chose to use it as primary key. Since the timestamps contains both date and time we chose to use the type TIMESTAMP since queries using timestamp can be cached.
CREATE TABLE IF NOT EXISTS temperature
(
timestamp TIMESTAMP,
station varchar(20),
temperature FLOAT,
INDEX(timestamp, station, temperature),
PRIMARY KEY (timestamp, station)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;The rain amount will be stored together with the date and station. In temperature we chose to use TIMESTAMP as type of the timestamp but since it is limited to 1970 and some of the rain reports are older and only has dates we chose to use DATE instead.
CREATE TABLE IF NOT EXISTS rainReports
(
timestamp DATE,
station varchar(20),
amount FLOAT,
INDEX(timestamp, station, amount),
PRIMARY KEY (timestamp, station)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;All queries will be implemented using prepared statements but for this demos we will use dummy data instead.
To be able to present the cities to be compared we need to find all cities in the database. Since it is almost impossible to do this in any other way it is hard to motivate the query.
SELECT name FROM cityThe application will provide some information about the chosen cities. This query will get that information. Since all information is available in one table a simple WHERE clause is used to find the specific informationText. "Kalmar" will be changed to a prepared statement.
SELECT informationText FROM city WHERE name='Kalmar'We join the rain reports and the rain stations where the rain station is in the provided city ("Kalmar" in this example) and where the station is matching the rain report station. Then we select sum of the amount of rain as well as the average amount of rain in the rain reports within and including the provided dates. A similar query will be used for getting the average temperature during the time span.
SELECT Sum(amount) AS totalRain,
Avg(amount) AS avgRain
FROM rainReports
INNER JOIN rainStation
ON rainStation.city = 'Kalmar'
AND rainStation.station = rainReports.station
WHERE rainReports.timestamp BETWEEN '2018-01-01' AND '2019-01-01'We want to be able to present both the date and the temperature of the coldest day within the time span. To be able to do this we use a similar query as the other one as a base to find the coldest day and then select the temperature and the timestamp. There is no real reason to why Min(temperature) is re assigned to coldestDay more than to make the query easier to read and understand.
SELECT temperature,
timestamp
FROM temperature
WHERE temperature = (SELECT Min(temperature) AS coldestDay
FROM temperature
INNER JOIN tempStation
ON tempStation.city = 'Kalmar'
AND tempStation.station =
temperature.station
WHERE temperature.timestamp BETWEEN '2018-01-01' AND '2019-01-01' )
AND temperature.timestamp BETWEEN '2018-01-01' AND '2019-01-01'This query is very similar to the coldest day but uses the rain reports and the rain stations instead. The inner join is used to combine the two tables where the provided arguments matches.
SELECT amount,
timestamp
FROM rainReports
WHERE amount = (SELECT Max(amount) AS rainiestDay
FROM rainReports
INNER JOIN rainStation
ON rainStation.city = 'Kalmar'
AND rainStation.station =
rainReports.station
WHERE rainReports.timestamp BETWEEN '2018-01-01' AND '2019-01-01')
AND rainReports.timestamp BETWEEN '2018-01-01' AND '2019-01-01'The implementation can be found at https://github.com/Elmona/weatherComparision. We used all the queries in the previous section but turned them into prepared statements. We also added queries for average temperature as well as warmest day.
- Node installed
- Npm installed
- Docker installed
Everything in this style are scripts that should be executed in your console.
git clone https://github.com/Elmona/weatherComparision.gitcd weatherComparisionchmod +x createConfigs.sh dockerOnlyMysql.sh./createConfigs.sh prod./dockerOnlyMysql.sh- Open new terminal
cd weatherComparision/addToMysqlnpm installchmod +x ./createFolders.sh && ./createFolders.shnode app.js<<- Adding data to databasecd ../server && npm inpm start<<- Starting server- Open new terminal
cd weatherComparision/clientnpm i && npm start<<- Starting client- Open browser at port 3000