Practical WK1
Harold Nelson
3/15/2021
# install.packages(c("sf", "tmap", "tmaptools", "RSQLite", "tidyverse"))
library(tidyverse)## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.0 ──## ✓ ggplot2 3.3.0 ✓ purrr 0.3.4
## ✓ tibble 3.0.5 ✓ dplyr 1.0.3
## ✓ tidyr 1.0.2 ✓ stringr 1.4.0
## ✓ readr 1.3.1 ✓ forcats 0.5.0## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()library(tmap)
library(tmaptools)
library(RSQLite)
library(sf)## Linking to GEOS 3.8.1, GDAL 3.1.4, PROJ 6.3.1Get the shapefile.
shape = st_read("/Users/haroldnelson/Dropbox/UCL/WK1/statistical-gis-boundaries-london/ESRI/London_Borough_Excluding_MHW.shp")## Reading layer `London_Borough_Excluding_MHW' from data source `/Users/haroldnelson/Dropbox/UCL/WK1/statistical-gis-boundaries-london/ESRI/London_Borough_Excluding_MHW.shp' using driver `ESRI Shapefile'
## Simple feature collection with 33 features and 7 fields
## geometry type: MULTIPOLYGON
## dimension: XY
## bbox: xmin: 503568.2 ymin: 155850.8 xmax: 561957.5 ymax: 200933.9
## projected CRS: OSGB 1936 / British National GridGet a summary.
summary(shape)## NAME GSS_CODE HECTARES NONLD_AREA
## Length:33 Length:33 Min. : 314.9 Min. : 0.00
## Class :character Class :character 1st Qu.: 2724.9 1st Qu.: 0.00
## Mode :character Mode :character Median : 3857.8 Median : 2.30
## Mean : 4832.4 Mean : 64.22
## 3rd Qu.: 5658.5 3rd Qu.: 95.60
## Max. :15013.5 Max. :370.62
## ONS_INNER SUB_2009 SUB_2006 geometry
## Length:33 Length:33 Length:33 MULTIPOLYGON :33
## Class :character Class :character Class :character epsg:NA : 0
## Mode :character Mode :character Mode :character +proj=tmer...: 0
##
##
## Plot it.
plot(shape)
Just the geometry.
shape %>%
st_geometry() %>%
plot()
Get the incidents.
mycsv <- read.csv("~/Dropbox/UCL/WK1/Incidents.csv")Merge.
shape <- shape%>%
merge(
mycsv,
by.x="GSS_CODE",
by.y="Row.Labels")
shape %>% head(n=10)## Simple feature collection with 10 features and 14 fields
## geometry type: MULTIPOLYGON
## dimension: XY
## bbox: xmin: 509702.4 ymin: 155850.8 xmax: 554089.2 ymax: 200933.9
## projected CRS: OSGB 1936 / British National Grid
## GSS_CODE NAME HECTARES NONLD_AREA ONS_INNER SUB_2009
## 1 E09000001 City of London 314.942 24.546 T <NA>
## 2 E09000002 Barking and Dagenham 3779.934 169.150 F <NA>
## 3 E09000003 Barnet 8674.837 0.000 F <NA>
## 4 E09000004 Bexley 6428.649 370.619 F <NA>
## 5 E09000005 Brent 4323.270 0.000 F <NA>
## 6 E09000006 Bromley 15013.487 0.000 F <NA>
## 7 E09000007 Camden 2178.932 0.000 T <NA>
## 8 E09000008 Croydon 8649.441 0.000 F <NA>
## 9 E09000009 Ealing 5554.428 0.000 F <NA>
## 10 E09000010 Enfield 8220.025 0.000 F <NA>
## SUB_2006 y2012_13 y2013_14 y2014_15 y2015_16 y2016_17 y2017_18 Grand_total
## 1 <NA> 449 530 369 627 1731 1826 5532
## 2 <NA> 2417 1282 2564 2361 2423 2628 13675
## 3 <NA> 1697 1779 11615 5999 7029 6153 34272
## 4 <NA> 1038 1078 1162 1110 1480 2105 7973
## 5 <NA> 6911 7001 12912 13198 17340 18609 75971
## 6 <NA> 2495 2809 3377 3343 3246 3067 18337
## 7 <NA> 11477 10950 8308 7268 6778 12170 56951
## 8 <NA> 11150 15113 18560 0 24797 19198 88818
## 9 <NA> 6352 5765 7257 7032 14270 13610 54286
## 10 <NA> 17871 31692 50121 70930 75614 3900 250128
## geometry
## 1 MULTIPOLYGON (((531145.1 18...
## 2 MULTIPOLYGON (((543905.4 18...
## 3 MULTIPOLYGON (((524579.9 19...
## 4 MULTIPOLYGON (((547226.2 18...
## 5 MULTIPOLYGON (((525201 1825...
## 6 MULTIPOLYGON (((540373.6 15...
## 7 MULTIPOLYGON (((528840.2 18...
## 8 MULTIPOLYGON (((535009.2 15...
## 9 MULTIPOLYGON (((510253.5 18...
## 10 MULTIPOLYGON (((531023.5 20...Do a thematic map.
library(tmap)
tmap_mode("plot")## tmap mode set to plottingshape %>%
qtm(fill = "y2013_14")
Save shape in a new geopackage.
shape %>%
st_write("Rwk1a.gpkg",
"london_boroughs_fly_tipping",
delete_layer=TRUE)## Deleting layer `london_boroughs_fly_tipping' using driver `GPKG'
## Writing layer `london_boroughs_fly_tipping' to data source `Rwk1a.gpkg' using driver `GPKG'
## Writing 33 features with 14 fields and geometry type Multi Polygon.Play with sqlite
library(readr)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(),dbname="Rwk1.gpkg")Look at the contents of the geopackage
con %>%
dbListTables()## [1] "gpkg_contents"
## [2] "gpkg_extensions"
## [3] "gpkg_geometry_columns"
## [4] "gpkg_ogr_contents"
## [5] "gpkg_spatial_ref_sys"
## [6] "gpkg_tile_matrix"
## [7] "gpkg_tile_matrix_set"
## [8] "london_boroughs_fly_tipping"
## [9] "original_csv"
## [10] "rtree_london_boroughs_fly_tipping_geom"
## [11] "rtree_london_boroughs_fly_tipping_geom_node"
## [12] "rtree_london_boroughs_fly_tipping_geom_parent"
## [13] "rtree_london_boroughs_fly_tipping_geom_rowid"
## [14] "sqlite_sequence"Add mycsv to the package and check to make sure it’s there.
con %>%
dbWriteTable(.,
"original_csv",
mycsv,
overwrite=TRUE)
dbListTables(con)## [1] "gpkg_contents"
## [2] "gpkg_extensions"
## [3] "gpkg_geometry_columns"
## [4] "gpkg_ogr_contents"
## [5] "gpkg_spatial_ref_sys"
## [6] "gpkg_tile_matrix"
## [7] "gpkg_tile_matrix_set"
## [8] "london_boroughs_fly_tipping"
## [9] "original_csv"
## [10] "rtree_london_boroughs_fly_tipping_geom"
## [11] "rtree_london_boroughs_fly_tipping_geom_node"
## [12] "rtree_london_boroughs_fly_tipping_geom_parent"
## [13] "rtree_london_boroughs_fly_tipping_geom_rowid"
## [14] "sqlite_sequence"Retrieve the fly-tipping sf object from the geopackage.
library(sf)
st_layers("Rwk1.gpkg")## Driver: GPKG
## Available layers:
## layer_name geometry_type features fields
## 1 london_boroughs_fly_tipping Multi Polygon 33 14
## 2 original_csv NA 326 8a = st_read(dsn = "Rwk1.gpkg", layer = "london_boroughs_fly_tipping")## Reading layer `london_boroughs_fly_tipping' from data source `/Users/haroldnelson/Dropbox/UCL/WK1/Flytipping_datasets_in_Excel_2018_19_rev2/Rwk1.gpkg' using driver `GPKG'
## Simple feature collection with 33 features and 14 fields
## geometry type: MULTIPOLYGON
## dimension: XY
## bbox: xmin: 503568.2 ymin: 155850.8 xmax: 561957.5 ymax: 200933.9
## projected CRS: OSGB 1936 / British National Gridhead(a)## Simple feature collection with 6 features and 14 fields
## geometry type: MULTIPOLYGON
## dimension: XY
## bbox: xmin: 515484.9 ymin: 156480.9 xmax: 554089.2 ymax: 198355.2
## projected CRS: OSGB 1936 / British National Grid
## GSS_CODE NAME HECTARES NONLD_AREA ONS_INNER SUB_2009
## 1 E09000001 City of London 314.942 24.546 T <NA>
## 2 E09000002 Barking and Dagenham 3779.934 169.150 F <NA>
## 3 E09000003 Barnet 8674.837 0.000 F <NA>
## 4 E09000004 Bexley 6428.649 370.619 F <NA>
## 5 E09000005 Brent 4323.270 0.000 F <NA>
## 6 E09000006 Bromley 15013.487 0.000 F <NA>
## SUB_2006 y2012_13 y2013_14 y2014_15 y2015_16 y2016_17 y2017_18 Grand_total
## 1 <NA> 449 530 369 627 1731 1826 5532
## 2 <NA> 2417 1282 2564 2361 2423 2628 13675
## 3 <NA> 1697 1779 11615 5999 7029 6153 34272
## 4 <NA> 1038 1078 1162 1110 1480 2105 7973
## 5 <NA> 6911 7001 12912 13198 17340 18609 75971
## 6 <NA> 2495 2809 3377 3343 3246 3067 18337
## geom
## 1 MULTIPOLYGON (((531145.1 18...
## 2 MULTIPOLYGON (((543905.4 18...
## 3 MULTIPOLYGON (((524579.9 19...
## 4 MULTIPOLYGON (((547226.2 18...
## 5 MULTIPOLYGON (((525201 1825...
## 6 MULTIPOLYGON (((540373.6 15...Disconnect from the geopackage.
con %>%
dbDisconnect()