Database dan Data Spasial

Library yang digunakan:

library(knitr)
library(kableExtra)
library(dplyr)
library(RSQLite)
library(DBI)
library(sf)
library(ggplot2)
library(tigris)

Koneksi Database

Database yang digunakan adalah chinook.db

chinook <-DBI::dbConnect(RSQLite::SQLite(), "C:/sqlite/db/chinook.db")

Daftar Tabel yang terdapat pada database chinook adalah:

RSQLite::dbListTables(chinook)
##  [1] "albums"          "artists"         "customers"       "employees"      
##  [5] "genres"          "invoice_items"   "invoices"        "media_types"    
##  [9] "playlist_track"  "playlists"       "sqlite_sequence" "sqlite_stat1"   
## [13] "tracks"

Tables

Tabel-tabel yang akan digunakan pada praktikum terdiri dari:

  • albums, artists , customers , invoice_items , invoices , playlists , playlist_track , tracks
albums<- dplyr::tbl(chinook, "albums")
artists<-dplyr::tbl(chinook,"artists")
customers<-dplyr::tbl(chinook,"customers")
invoice_items<-dplyr::tbl(chinook,"invoice_items")
invoices<-dplyr::tbl(chinook,"invoices")
playlists<-dplyr::tbl(chinook,"playlists")
playlist_track<-dplyr::tbl(chinook,"playlist_track")
tracks<-dplyr::tbl(chinook,"tracks")
kbl(head(albums), caption = "Head(Albums)") %>%  kable_styling()
Head(Albums)
AlbumId Title ArtistId
1 For Those About To Rock We Salute You 1
2 Balls to the Wall 2
3 Restless and Wild 2
4 Let There Be Rock 1
5 Big Ones 3
6 Jagged Little Pill 4 
kbl(head(artists), caption = "Head(artists)") %>%  kable_styling()
Head(artists)
ArtistId Name
1 AC/DC
2 Accept
3 Aerosmith
4 Alanis Morissette
5 Alice In Chains
6 Antônio Carlos Jobim 
kbl(head(customers), caption = "Head(customers)") %>%  kable_styling()
Head(customers)
CustomerId FirstName LastName Company Address City State Country PostalCode Phone Fax Email SupportRepId
1 Luís Gonçalves Embraer - Empresa Brasileira de Aeronáutica S.A. Av. Brigadeiro Faria Lima, 2170 São José dos Campos SP Brazil 12227-000 +55 (12) 3923-5555 +55 (12) 3923-5566 3
2 Leonie Köhler NA Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 +49 0711 2842222 NA 5
3 François Tremblay NA 1498 rue Bélanger Montréal QC Canada H2G 1A7 +1 (514) 721-4711 NA 3
4 Bjørn Hansen NA Ullevålsveien 14 Oslo NA Norway 0171 +47 22 44 22 22 NA 4
5 František Wichterlová JetBrains s.r.o. Klanova 9/506 Prague NA Czech Republic 14700 +420 2 4172 5555 +420 2 4172 5555 4
6 Helena Holý NA Rilská 3174/6 Prague NA Czech Republic 14300 +420 2 4177 0449 NA 5 
kbl(head(invoice_items), caption = "Head(invoice_items)") %>%  kable_styling()
Head(invoice_items)
InvoiceLineId InvoiceId TrackId UnitPrice Quantity
1 1 2 0.99 1
2 1 4 0.99 1
3 2 6 0.99 1
4 2 8 0.99 1
5 2 10 0.99 1
6 2 12 0.99 1 
kbl(head(invoices), caption = "Head(invoices)") %>%  kable_styling()
Head(invoices)
InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState BillingCountry BillingPostalCode Total
1 2 2009-01-01 00:00:00 Theodor-Heuss-Straße 34 Stuttgart NA Germany 70174 1.98
2 4 2009-01-02 00:00:00 Ullevålsveien 14 Oslo NA Norway 0171 3.96
3 8 2009-01-03 00:00:00 Grétrystraat 63 Brussels NA Belgium 1000 5.94
4 14 2009-01-06 00:00:00 8210 111 ST NW Edmonton AB Canada T6G 2C7 8.91
5 23 2009-01-11 00:00:00 69 Salem Street Boston MA USA 2113 13.86
6 37 2009-01-19 00:00:00 Berger Straße 10 Frankfurt NA Germany 60316 0.99 
kbl(head(playlists), caption = "Head(playlists)") %>%  kable_styling()
Head(playlists)
PlaylistId Name
1 Music
2 Movies
3 TV Shows
4 Audiobooks
5 90’s Music
6 Audiobooks 
kbl(head(playlist_track), caption = "Head(playlist_track)") %>%  kable_styling()
Head(playlist_track)
PlaylistId TrackId
1 3402
1 3389
1 3390
1 3391
1 3392
1 3393 
kbl(head(tracks), caption = "Head(tracks)") %>%  kable_styling()
Head(tracks)
TrackId Name AlbumId MediaTypeId GenreId Composer Milliseconds Bytes UnitPrice
1 For Those About To Rock (We Salute You) 1 1 1 Angus Young, Malcolm Young, Brian Johnson 343719 11170334 0.99
2 Balls to the Wall 2 2 1 NA 342562 5510424 0.99
3 Fast As a Shark 3 2 1 F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman 230619 3990994 0.99
4 Restless and Wild 3 2 1 F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider & W. Hoffman 252051 4331779 0.99
5 Princess of the Dawn 3 2 1 Deaffy & R.A. Smith-Diesel 375418 6290521 0.99
6 Put The Finger On You 1 1 1 Angus Young, Malcolm Young, Brian Johnson 205662 6713451 0.99

Fungsi Join

inner_join()

inner_join() menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x dan y.

inner_join() yang dicoba adalah antara tabel invoices dan invoice_items

inner_join_t = inner_join(invoices,invoice_items)
## Joining, by = "InvoiceId"
inner_join_t
## # Source:   lazy query [?? x 13]
## # Database: sqlite 3.34.1 [C:\sqlite\db\chinook.db]
##    InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState
##        <int>      <int> <chr>       <chr>          <chr>       <chr>       
##  1         1          2 2009-01-01~ Theodor-Heuss~ Stuttgart   <NA>        
##  2         1          2 2009-01-01~ Theodor-Heuss~ Stuttgart   <NA>        
##  3         2          4 2009-01-02~ Ullevålsveien~ Oslo        <NA>        
##  4         2          4 2009-01-02~ Ullevålsveien~ Oslo        <NA>        
##  5         2          4 2009-01-02~ Ullevålsveien~ Oslo        <NA>        
##  6         2          4 2009-01-02~ Ullevålsveien~ Oslo        <NA>        
##  7         3          8 2009-01-03~ Grétrystraat ~ Brussels    <NA>        
##  8         3          8 2009-01-03~ Grétrystraat ~ Brussels    <NA>        
##  9         3          8 2009-01-03~ Grétrystraat ~ Brussels    <NA>        
## 10         3          8 2009-01-03~ Grétrystraat ~ Brussels    <NA>        
## # ... with more rows, and 7 more variables: BillingCountry <chr>,
## #   BillingPostalCode <chr>, Total <dbl>, InvoiceLineId <int>, TrackId <int>,
## #   UnitPrice <dbl>, Quantity <int>

left_join()

left_join() menghasilkan semua baris pada table x, dan semua kolom pada x dan y, dimana untuk baris pada x yang tidak memiliki kesamaan dengan y diisi dengan nilai NA pada kolom yang baru.

left_join() yang dicoba adalah antara tabel playlist_track dan playlists

left_join_t = left_join(playlist_track,playlists)
## Joining, by = "PlaylistId"
left_join_t
## # Source:   lazy query [?? x 3]
## # Database: sqlite 3.34.1 [C:\sqlite\db\chinook.db]
##    PlaylistId TrackId Name 
##         <int>   <int> <chr>
##  1          1    3402 Music
##  2          1    3389 Music
##  3          1    3390 Music
##  4          1    3391 Music
##  5          1    3392 Music
##  6          1    3393 Music
##  7          1    3394 Music
##  8          1    3395 Music
##  9          1    3396 Music
## 10          1    3397 Music
## # ... with more rows

right_join()

right_join() menghasilkan semua baris pada table y, dan semua kolom pada x dan y, dimana untuk baris pada y yang tidak memiliki kesamaan dengan x akan diisi dengan nilai NA pada kolom yang baru.

right_join() yang dicoba adalah antara tabel tracks dan playlist_track

right_join_t = right_join(tracks,playlist_track)
## Joining, by = "TrackId"
right_join_t
## # Source:   lazy query [?? x 10]
## # Database: sqlite 3.34.1 [C:\sqlite\db\chinook.db]
##    TrackId Name  AlbumId MediaTypeId GenreId Composer Milliseconds  Bytes
##      <int> <chr>   <int>       <int>   <int> <chr>           <int>  <int>
##  1    3402 "Ban~     271           3      23 <NA>           294294 6.11e7
##  2    3389 "Rev~     271           2      23 <NA>           252376 4.11e6
##  3    3390 "One~     271           2      23 <NA>           217732 3.56e6
##  4    3391 "Sou~     271           2      23 <NA>           260154 4.23e6
##  5    3392 "Unt~     271           2      23 <NA>           230758 3.77e6
##  6    3393 "Ori~     271           2      23 <NA>           218916 3.58e6
##  7    3394 "Bro~     271           2      23 <NA>           228366 3.73e6
##  8    3395 "Som~     271           2      23 <NA>           213831 3.50e6
##  9    3396 "Sha~     271           2      23 <NA>           274597 4.47e6
## 10    3397 "Jew~     271           2      23 <NA>           233242 3.81e6
## # ... with more rows, and 2 more variables: UnitPrice <dbl>, PlaylistId <int>

full_join()

full_join() menghasilkan semua baris dan kolom dari x dan y. Jika ada nilai yang tidak sama (match) antara x dan y, maka akan bernilai NA.

full_join() yang dicoba adalah antara tabel artists dan albums

full_join_t = full_join(artists,albums)
## Joining, by = "ArtistId"
full_join_t
## # Source:   lazy query [?? x 4]
## # Database: sqlite 3.34.1 [C:\sqlite\db\chinook.db]
##    ArtistId Name                 AlbumId Title                                
##       <int> <chr>                  <int> <chr>                                
##  1        1 AC/DC                      1 For Those About To Rock We Salute You
##  2        1 AC/DC                      4 Let There Be Rock                    
##  3        2 Accept                     2 Balls to the Wall                    
##  4        2 Accept                     3 Restless and Wild                    
##  5        3 Aerosmith                  5 Big Ones                             
##  6        4 Alanis Morissette          6 Jagged Little Pill                   
##  7        5 Alice In Chains            7 Facelift                             
##  8        6 Antônio Carlos Jobim       8 Warner 25 Anos                       
##  9        6 Antônio Carlos Jobim      34 Chill: Brazil (Disc 2)               
## 10        7 Apocalyptica               9 Plays Metallica By Four Cellos       
## # ... with more rows

semi_join()

semi_join() menghasilkan semua baris pada table x yang memiliki kesamaan nilai dengan table y, dan semua kolom dari x. Ini mirip dengan inner_join(), bedanya inner_join() mengembalikan semua kolom dari x dan y.

inner_join() yang dicoba adalah antara tabel invoices dan customers

semi_join_t = semi_join(invoices,customers)
## Joining, by = "CustomerId"
semi_join_t
## # Source:   lazy query [?? x 9]
## # Database: sqlite 3.34.1 [C:\sqlite\db\chinook.db]
##    InvoiceId CustomerId InvoiceDate BillingAddress BillingCity BillingState
##        <int>      <int> <chr>       <chr>          <chr>       <chr>       
##  1         1          2 2009-01-01~ Theodor-Heuss~ Stuttgart   <NA>        
##  2         2          4 2009-01-02~ Ullevålsveien~ Oslo        <NA>        
##  3         3          8 2009-01-03~ Grétrystraat ~ Brussels    <NA>        
##  4         4         14 2009-01-06~ 8210 111 ST NW Edmonton    AB          
##  5         5         23 2009-01-11~ 69 Salem Stre~ Boston      MA          
##  6         6         37 2009-01-19~ Berger Straße~ Frankfurt   <NA>        
##  7         7         38 2009-02-01~ Barbarossastr~ Berlin      <NA>        
##  8         8         40 2009-02-01~ 8, Rue Hanovre Paris       <NA>        
##  9         9         42 2009-02-02~ 9, Place Loui~ Bordeaux    <NA>        
## 10        10         46 2009-02-03~ 3 Chatham Str~ Dublin      Dublin      
## # ... with more rows, and 3 more variables: BillingCountry <chr>,
## #   BillingPostalCode <chr>, Total <dbl>

anti_join()

anti_join() menghasilkan semua baris dari x yang TIDAK memiliki kesamaan dengan y, dan semua kolom yang berasal dari x.

anti_join() yang dicoba adalah antara tabel artists dan albums

anti_join_t = anti_join(artists,albums)
## Joining, by = "ArtistId"
anti_join_t
## # Source:   lazy query [?? x 2]
## # Database: sqlite 3.34.1 [C:\sqlite\db\chinook.db]
##    ArtistId Name                      
##       <int> <chr>                     
##  1       25 Milton Nascimento & Bebeto
##  2       26 Azymuth                   
##  3       28 João Gilberto             
##  4       29 Bebel Gilberto            
##  5       30 Jorge Vercilo             
##  6       31 Baby Consuelo             
##  7       32 Ney Matogrosso            
##  8       33 Luiz Melodia              
##  9       34 Nando Reis                
## 10       35 Pedro Luís & A Parede     
## # ... with more rows

Data Spasial

Peta

Peta yang digunakan pada praktikum ini adalah peta Kecamatan-Kabupaten Banda Aceh

adminKecamatan <- "F:/Admin3Kecamatan/idn_admbnda_adm3_bps_20200401.shp"
adminKecamatan <- st_read(adminKecamatan)
## Reading layer `idn_admbnda_adm3_bps_20200401' from data source `F:\Admin3Kecamatan\idn_admbnda_adm3_bps_20200401.shp' using driver `ESRI Shapefile'
## Simple feature collection with 7069 features and 16 fields
## geometry type:  MULTIPOLYGON
## dimension:      XY
## bbox:           xmin: 95.01079 ymin: -11.00762 xmax: 141.0194 ymax: 6.07693
## geographic CRS: WGS 84

Data

Data yang akan digunakan pada praktikum ini adalah Jumlah Tenaga Kesehatan Kota Banda Aceh menurut Kecamatan Tahun 2020

NakesBandaaceh<-read.csv("F:/Admin3Kecamatan/jumlah_nakes_banda_aceh_2020.csv")
kbl(NakesBandaaceh, caption = "Jumlah Tenaga Kesehatan Kota Banda Aceh menurut Kecamatan Tahun 2020") %>%  kable_styling()
Jumlah Tenaga Kesehatan Kota Banda Aceh menurut Kecamatan Tahun 2020
KodeKecamatan Kecamatan Jumlah_Tenaga_Kesehatan
ID1171010 Meuraxa 38
ID1171011 Jaya Baru 40
ID1171012 Banda Raya 43
ID1171020 Baiturrahman 36
ID1171021 Lueng Bata 39
ID1171030 Kuta Alam 61
ID1171031 Kuta Raja 30
ID1171040 Syiah Kuala 56
ID1171041 Ulee Kareng 46

Merge Data

Merge shapefiles dan dataset

merged_Bandaaceh <- geo_join(spatial_data=adminKecamatan, 
                                  data_frame=NakesBandaaceh, by_sp="ADM3_PCODE", 
                                  by_df="KodeKecamatan", how = "inner")

Data Spasial

gradient <- c("#ffb3d9","#ff4da6","#e60073","#800040" )

pDATA<-ggplot()+geom_sf(data=merged_Bandaaceh,aes(fill=Jumlah_Tenaga_Kesehatan
))+ scale_fill_gradientn(colours=gradient)+
  labs(title="Jumlah Tenaga Kesehatan Kota Banda Aceh Tahun 2020")
pDATA

Terima Kasih