Differences

This shows you the differences between two versions of the page.

--- onlinedb [2022/03/08 11:08]
qcbs [Accessing PSQL]
+++ onlinedb [2025/04/08 14:34] (current)
qcbs [Exercise 3 - SELECT statement]
@@ Line 5: / Line 5: @@
-[[http://prezi.com/z1ib62c5ybmx/|Link to Prezi presentation]]
+[[https://prezi.com/view/Rd0TlmRUi3P27mbPYECg/|Link to Prezi presentation]]
-{{ ::prezi_opendb_qcbs.pdf |PDF version of the Prezi presentation}}
 ===== Useful resources =====
@@ Line 115: / Line 114: @@
 ====== Group discussion ======
-[[http://qcbs.ca/wp-content/uploads/2012/10/Crustacean_plankton_Canada.ods|Click here to download the original spreadsheet]]
+[[http://qcbs.ca/wp-content/uploads/2012/10/Crustacean_plankton_Canada.ods|Click here to download the original spreadsheet (ODS format)]]
+{{ ::crustacean_plankton_canada.xlsx |Click here to download the original spreadsheet (XLSX format)}}
@@ Line 324: / Line 325: @@
 CREATE TABLE species (species_id INT NOT NULL, full_name varchar(255), short_name VARCHAR(100));
 COPY species FROM 'C:/bdlibre/species_acro.csv' WITH csv HEADER DELIMITER AS ',';
+</file>
+===== Exercise 3 - Importing data into SQLite in R =====
+First install the RSQLite package
+<file rsplus>
+install.packages('RSQLite')
+</file>
+To load it in R, you will need to load the DBI package
+<file rsplus>
+library(DBI)
+</file>
+Then, create the file containing the database
+<file rsplus>
+mydb <- dbConnect(RSQLite::SQLite(), "QCBS_Workshop.sqlite")
+</file>
+Set this to where you have downloaded the files
+<file>
+setwd('C:\\User\MyName\Workshop\')
+</file>
+You can now load the CSV files in R as data frames
+<file>
+lakes <- read.csv('lakes.csv')
+lakes_species <- read.csv('lakes_species.csv')
+species_acro <- read.csv('species_acro.csv')
+</file>
+And then load those dataframes into the database
+<file rsplus>
+dbWriteTable(mydb, "lakes", lakes)
+dbWriteTable(mydb, "lakes_species", lakes_species)
+dbWriteTable(mydb, "species_acro", species_acro)
+dbListTables(mydb)
+</file>
+You can then run most queries below with dbGetQuery. For example:
+<file rsplus>
+lake_prairies<-dbGetQuery(mydb, "SELECT * FROM lakes WHERE ecozone='Prairies'")
 </file>
 ===== REFERENCES =====
@@ Line 405: / Line 448: @@
 **Question 1** :?: -  How many lakes in British Columbia receive more than 3000 mm of precipitation (totp_ann column)?
-\\ ++answer| 12 ++ \\
+\\ ++answer| SELECT count(*) FROM lakes WHERE province='BRITISH COLUMBIA' AND totp_ann>3000; 12 ++ \\
-**Question 2** :?: -  What is the average Elevation (mean_ele column) of all lakes in the Montane Cordillera Ecozone (use the avg() operator)?
+**Question 2** :?: -  What is the average Elevation (mean_elev column) of all lakes in the Montane Cordillera Ecozones (use the avg() operator)?
-\\ ++answer| 1364.36 ++ \\
+\\ ++answer| SELECT avg(mean_elev) FROM lakes WHERE ecoprov LIKE '%Montane Cordillera'; 1364.36 ++ \\
 \\
-Note: the % operator is a wildcard used to replace the beginning or the end of a string.
+Note: the % operator is a wildcard used to replace the beginning or the end of a string in a query using LIKE.
 \\
 Select all lake names that contain the word 'Small'
@@ Line 428: / Line 471: @@
 **Question 3** :?: - What is the maximum latitude of all lakes in Ecozones with names that start with 'Taiga'
-\\ ++answer| 68.8 ++ \\
+\\ ++answer| SELECT max(latitude) FROM lakes WHERE ecozone like 'Taiga%'; 68.8 ++ \\
 **Question 4** :?: - How many species of Daphnia are there in the species table?
-\\ ++answer| 17 (21 have the word 'Daphnia' somewhere in their name) ++ \\
+\\ ++answer| SELECT count(*) FROM species_acro WHERE full_name like 'Daphnia%'; 17 (21 have the word 'Daphnia' somewhere in their name) ++ \\
 ===== Exercise 4 - GROUPING =====
@@ Line 450: / Line 493: @@
 **Question 5** :?: -  Which province has the lake with the highest maximum temperature (column tmax_ann)?
-\\ ++answer| BRITISH COLUMBIA 14.60 ++ \\
+\\ ++answer| SELECT province, max(tmax_ann) FROM lakes GROUP BY province ORDER BY max DESC LIMIT 1;
+BRITISH COLUMBIA 14.60 ++ \\
 Update statement:
@@ Line 639: / Line 683: @@
   * In Access, click on External data... ODBC database, and then select "Link to the data source by creating a linked table" and then choose New DSN name. Specify a name for the file, and then fill the information to connect to the database. You can then choose the tables you want to link.
   * Those table will then be linked to PostgreSQL and you can edit then as you would with other Access tables.
-========= Basic concepts for the creation of web forms =========
-  * [[https://prezi.com/xd-ueiomgbj-/basic-concepts-for-the-creation-of-web-forms/|Access the Prezi presentation here]]
-**Useful link**
-  * [[http://www.codecademy.com/|Code Academy]] Great website to learn HTML, CSS, PHP, Javascript, JQuery or other.
-  * [[http://link.springer.com/book/10.1007/978-1-4302-0136-6|Beginning PHP and PostgreSQL 8. ]] Book that can be downloaded in e-book format. A bit outdated.
-**Main steps**
-  - Creation of a web interface with html code and CSS.
-  - Creation of a database to accept the form data.
-  - Creation of PHP code to receive the form data and send it to the database.
-  - Possibility to enrich the form with Javascript/JQuery
-===== Installation of PHP, PostgreSQL, apache and PHPPGAdmin =====
-[[https://bitnami.com/tag/postgresql|Download and install the WAPP server for pour Windows, or MAPP for Mac.]]
-For linux:
-<file>
-sudo apt-get install postgresql-9.5 apache2 php5 phppgadmin php5-pgsql
-</file>
-===== Part 1 - HTML form =====
-Main components needed for an HTML file
-<file html>
-<!DOCTYPE html>
-<html>
-<head>
-<title>Document title</title>
-</head>
-<body>
-The page content is here!
-</body>
-</html>
-</file>
-Creation of a form
-The basic form
-<file html>
-<form method="POST" action="phpfile.php">
-<input type="text">
-</form>
-</file>
-[[http://www.w3schools.com/tags/att_input_type.asp|List of html input types]]
-Text input
-<file html>
-<input type="text" name="name_text">
-</file>
-Dropdown menu
-<file html>
-<select name="select1">
-<option value="1">Option 1</option>
-<option value="2">Option 2</option>
-<option value="3">Option 3</option>
-</select>
-</file>
-Check box
-<file html>
-<input type="checkbox" name="poutine" value="yes"> I like poutine
-<input type="checkbox" name="sushis" value="yes"> I like sushi
-<input type="checkbox" name="poutine_sushis" value="yes"> I like poutine topped with sushi
-</file>
-Radio buttons
-<file html>
-I am a...
-<input type="radio" name="gender" value="m" > Man
-<input type="radio" name="gender" value="w"> Woman
-</file>
-Text area
-<file html>
-<textarea name="text_zone">
-</textarea>
-</file>
-===== Form styling with CSS =====
-[[http://www.w3schools.com/cssref/|CSS reference]]
-You can give an "id" to any html element, but there can only one element per id.
-<file html>
-<input type="text" name="fieldname" id="fieldid">
-</file>
-We can then reference it in the header of the css file with the pound symbol (#)
-<file html>
-<head>
-<style>
-#fieldid {
- width:300px;
- height:30px;
- color:red;
- background-color:#ffff00;
- font-family:verdana,arial;
- font-size:14px;
- font-weight:bold;
- padding:0px 5px 0px 5px; #top right bottom left
-}
-</style>
-</head>
-</file>
-We can also give a class to any html element. This class can be repeated for multiple elements.
-<file html>
-<input type="text" name="name_text1" class="class_text">
-<input type="text" name="name_text2" class="class_text">
-<input type="text" name="name_text3" class="class_text">
-</file>
-Et on peut y faire référence dans l'entête de style css du fichier html avec le point "."
-We can then reference it in the style section of the html header with the "."
-<file html>
-<head>
-<style>
-.class_text {
- width:300px;
- height:30px;
- color:red;
- background-color:#ffff00;
- font-family:verdana,arial;
- font-size:14px;
- font-weight:bold;
- padding:0px 5px 0px 5px; #top right bottom left
-}
-</style>
-</head>
-</file>
-====== Part 2 - Creation of the associated database ======
-[[https://secure.qcbs.ca/systeme/phppgadmin/|Link to phppgadmin]]
-====== Part 3 - PHP language basics ======
-All php commands must be contained within
-<file php>
-<?php
-PHP code is here
-?>
-</file>
-Comments
-<file php>
-<?php
-// Comments in a line
-/*
-Comments section
-*/
-?>
-</file>
-<file php>
-<?php
-// Creation of variables
-$myvar=5;
-$myvar='this is text';
-// Creation of an array
-$myarray=array('value1','value2','value3');
-echo $myarray[0];
-//value1
-$myarray=[];
-$myarray[0]='value1';
-$myarray[1]='value2';
-// Creation of a "key-value" array
-$myarray=array(
-'key1'=>'key1',
-'key2'=>'key2',
-'key3'=>'key3',
-);
-echo $myarray['key1'];
-//value1
-?>
-</file>
-Loops and if/else
-<file php>
-<?php
-if ($f==5){
-//Do something
-}else{
-//Do something else
-}
-for ( $i=0;​$ i<10;$i++){
-echo 'i='.$i;
-}
-//i=0
-//i=1
-//...
-//i=9
-foreach ( $myarray as$ my){
-//Do something here with $my
-?>
-</file>
-====== Part 4 - Insertion into PostgreSQL via PHP ======
-[[http://php.net/manual/en/book.pgsql.php|PostgreSQL functions in PHP]]
-This PHP file is the one which is specified in the "ACTION" part of the <form> field
-<file php>
-<?php
-// Connexion and selection of the database
-$dbconn = pg_connect("host=localhost dbname=database user=username password=my_password");
-// The $_POST contains all the data from the form. Each 'key' in this array corresponds to the name given to each html element.
- $element1=$ _POST['name_element1'];
- $element2=$ _POST['name_element2'];
- $element1=pg_escape_string($ element1);
- $element2=pg_escape_string($ element2);
-// Inserted into the databse
- $query = "​INSERT INTO ma_table (column1,​column2) VALUES ($ element1,$element2)";
- $result = pg_query($ query);
-// Close the connexion
-pg_close($dbconn);
-echo "Form successfully entered into the database|";
-?>
-</file>
-Accéder à différents types d'éléments
-<file php>
-//Check boxes
-foreach ( $_POST['​checkbox'​] as$ check){
-   //Faire quelque chose ici avec $check
-}
-//Convertir les valeurs en liste séparée par des virgules
- $check=implode(',',​$ _POST['checkbox']);
-</file>
-Retrouver des éléments dans la base de données
-<file php>
-// Connexion et sélection de la base de données
-$dbconn = pg_connect("host=localhost dbname=base_donnees user=nom_usager password=mot_de_passe");
-$query="SELECT * FROM nom_table";
-// Insertion dans la base de données
- $result = pg_query($ query);
-while ( $t=pg_fetch_array($ result)){
-	echo  $t['​prenom'​].'​ '​.$ t['nom_famille'];
-}
-// Fermer la connexion
-pg_close($dbconn);
-</file>
 ====== Linking R with PostgreSQL ======
@@ Line 935: / Line 714: @@
 lakes <- tbl(src, "lakes") # Define lakes table
 lakes_qc<-filter(lakes, province  %=% 'QUEBEC') # Select lakes in Quebec
-prov_tmean<-summarise(group_by(lakes, province), mean(tmean_an)) # Mean annual temperature per province
+prov_tmean<- lakes |> group_by(province) |> summarise(mean(tmean_an)) # Mean annual temperature per province
-prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame
+prov_tmean <- collect(prov_tmean) # Transfer result to standard R data frame
-lakes_qc2<-tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement
+lakes_qc2 <- tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement
 </file>