Differences

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

--- spatialspecial [2019/04/15 16:58]
qcbs [Functions for basic queries on single layers]
+++ spatialspecial [2019/04/15 20:58] (current)
qcbs [Exercice 7]
@@ Line 84: / Line 84: @@
 **ST_AsSVG**(geometry) returns SVG text\\
 **ST_Transform**(geometry, SRID) change the reference system from the current one (of geom) to the one defined by SRID.\\
+Select all observations North of the 70th parallel
+<file postgresql>
+SELECT * FROM obis_whales WHERE latitude > 70
+</file>
+Select all observations South of the 60th parallel
+<file postgresql>
+SELECT tname, latitude FROM obis_whales WHERE latitude < -60
+</file>
+List the species that can be found South of the 60th parallel
+<file postgresql>
+SELECT DISTINCT tname FROM obis_whales WHERE latitude < -60
+</file>
 Create a new layer, with belugas only
@@ Line 160: / Line 175: @@
 ++++ To use a distance in meters |
+Add new empty column of type geography
 <file postgresql>
 ALTER TABLE obis_whales ADD column geog GEOGRAPHY(POINT,4326)
 </file>
+Update the geometry values as geography and store them in the new column
 <file postgresql>
 UPDATE obis_whales SET geog=geography(geom)
 </file>
+Repeat for the tm_world file
 <file postgresql>
 ALTER TABLE tm_world ADD column geog GEOGRAPHY(MULTIPOLYGON,4326)
@@ Line 220: / Line 237: @@
 \\
 \\
-===== Using PostgreSQL/PostGIS and GRASS in R =====
+===== Using PostgreSQL/PostGIS, GDAL, sf and GRASS in R =====
 {{::spatial_special_r_code.r|Download the R file containing the code for this part of the workshop}}
   * Package [[http://cran.r-project.org/web/packages/rpostgis/index.html|rpostgis]]
-  * Package [[http://cran.r-project.org/web/packages/rqgis/index.html|rQGIS]]
+  * Package [[https://github.com/jannes-m/RQGIS3|RQGIS3]]
   * Package [[http://cran.r-project.org/web/packages/rgrass7/index.html|rgrass7]]
   * Package [[http://cran.r-project.org/web/packages/RPostgreSQL/index.html|RPostgreSQL]]
@@ Line 231: / Line 249: @@
   * Package [[http://cran.r-project.org/web/packages/sf/index.html|sf - Simple Features]]
 ===== Using the Processing Framework in QGIS =====
+You first need to install the **Processing R Provider plugin** in QGIS. Then, restart QGIS!
   * [[https://docs.qgis.org/3.4/en/docs/training_manual/processing/r_syntax.html|R Syntax in Processing scripts. ]]
@@ Line 247: / Line 267: @@
 **folder**. A folder.\\
 **file**. A filename.\\
+To create a new R Processing script, click on the R logo on the top of the processing toolbox pane, and select Create New R Script. When you are done typing the script, give it an appropriate name and save it in the default folder that opens.
 ===== Exercice 5 =====
-Open the Processing Toolbox. Make sure that R is activated as a provider in the Toolbox options. Find "R Scripts > Create a new R script" in the toolbox. Copy and past this Processing/R script that creates 100 points distributed randomly on the earth in the latitude/longitude reference system (EPSG 4326) and that are imported as a QGIS layer.
+Create a new R script in the toolbox by copying and pasting this Processing/R script that creates 100 points distributed randomly on the earth in the latitude/longitude reference system (EPSG 4326) and that are imported as a QGIS layer.
 Overlay the result on top of the TM_World_Borders shapefile.
@@ Line 275: / Line 297: @@
 c2<-st_as_sf(countries)
 areas<-st_area(c2)
-plot(log(areas),log(c2$POP2005),xlab='Log Area',ylab='Log Population')
+plot(log(areas),log(as.numeric(c2$POP2005)),xlab='Log Area',ylab='Log Population')
-text(log(areas),log(c2$POP2005),c2$NAME,cex=0.7)
+text(log(areas),log(as.numeric(c2$POP2005)),c2$NAME,cex=0.7)
 </file>
 ++++
@@ Line 290: / Line 312: @@
 ##Layer=vector
 ##showplots
-data2=tapply(Layer$new_area,Layer$IHO_Sea,mean)
+#data2=tapply(Layer$new_area,Layer$IHO_Sea,mean)
+library('dplyr')
+Layer=data.frame(Layer)
+data2=Layer%>%group_by(IHO_Sea)%>%summarize(mean=mean(new_area))
 par(mar=c(10,4,4,2))
-barplot(data2, las=2)
+barplot(data2$mean, names.arg=data2$IHO_Sea, las=2)
 </file>
@@ Line 299: / Line 324: @@
 - Specify "Country seas" as the name of the model and "QCBS Workshop" as the group.
-- Add a vector layer to the model, name it "Countries" and specify polygon as the "shape type".
+- Add a vector layer to the model, name it "Countries" and specify polygon as the "geometry type".
 - Add a "String" to the model and name it, "Country Name".
-- Add "Extract by attributes" to the model (under QGIS geoalgorithms). Specify "Country" as the "Input Layer", = as the operator, NAME as the "Selection Attribute" and "Country Name" as the "Value".
+- Add "Extract by attributes" to the model (under Algorithms>Vector selection). Specify "Countries" as the "Input Layer", = as the operator, NAME as the "Selection Attribute", click on the Gear icon under Value, choose Model input and select "Country Name" as the "Value".
-- Add a "Fixed Distance Buffer" to the model. Specify "Output from Extract by Attributes" as the "Input", and specify the distance as 0.5 (it's in degrees since the map is in latitude/longitude). Choose to "dissolve the output".
+- Add a "Buffer" to the model. Specify "Output from Extract by Attributes" as the "Input", and specify the distance as 0.5 (it's in degrees since the map is in latitude/longitude). Choose to "dissolve the output" and leave other field blank.
-- Add another vector to the model. Name it "Zones". Specify "Polygon" as the "shape type". This will be our polygons layer containing the different seas.
+- Add another Vector Layer to the model. Name it "Sea Zones". Specify "Polygon" as the "shape type". This will be our polygons layer containing the different seas.
-- Add "Intersection" to the model. Specify an intersection between "output of the Buffer" and the layer "Zones".
+- Add "Intersection" to the model. Specify an intersection between "Buffered from Buffer" and the layer "Sea Zones". Leave other field blank.
-- Add "Field Calculator" to the model. Specify new_area as "Result Field Name" and $area as the fomula. This will calculate the area of each polygon of each section of sea in the buffer zone around the country in degrees squared.
+- Add "Field Calculator" to the model. Choose the intersection layer from the previous step as the input. Specify new_area as "Result Field Name" and $area as the fomula. This will calculate the area of each polygon of each section of sea in the buffer zone around the country in degrees squared.
 - Now, add the R script created at Step 2 to the model. "Input" is the output from Step 11 (Field Calculator). Call the output file "Country_seas"
@@ Line 317: / Line 342: @@
 Your model should look like this one:
-{{::screenshot_from_2017-04-19_11-54-37.png?direct&200|}}
+{{::screenshot_from_2019-04-15_16-51-00.png?direct&200|}}
 - You are now ready to run the model! First, save the model and name it Country_seas. Then, close the "Graphical Modeler" and double-click on your model in the Toolbox under Models. Specify TM_World_Borders as the Country layer and EEZ_IHO_union_v2 for the zones. Specify the name of a country of your choice (ex. Canada).