Connect with RODBC

RODBC merupakan package yang menghubungkan RDBMS dengan aplikasi dengan konektor ODBC. Sebelumnya lakukan konfigurasi terlebih dahulu pada ODBC DSN Configuration dengan beberapa langkah. Tambahkan System DSN dengan menggunakan tombol Add. Setelah itu, pilih SQLite3 ODBC Driver. Kemudian isikan Data Source Name, dan isikan Database Name berikut path database tersebut berada.

Jika sudah, maka library RODBC dapat digunakan.

library(RODBC)

Selanjutnya dapat membuat koneksi ke database dengan menggunakan fungsi odbcConnect. Koneksi tersebut disimpan dalam sebuah objek, sebagai contoh akan disimpan ke dalam objek con.

# koneksi dengan fungsi odbc connect terhadap DSN-nya
# nama DSN yang digunakan tadi adalah "northwind"
con <- odbcConnect("northwind", believeNRows = FALSE, rows_at_time = 1)

Untuk melihat tabel apa saja yang berada di dalam database, dapat menggunakan fungsi sqlTables

# Fungsi untuk me-list table di dalam database
sqlTables(con, "Northwind_large.sqlite")

   TABLE_CAT TABLE_SCHEM           TABLE_NAME TABLE_TYPE REMARKS
1       <NA>        <NA>             Employee      TABLE    <NA>
2       <NA>        <NA>             Category      TABLE    <NA>
3       <NA>        <NA>             Customer      TABLE    <NA>
4       <NA>        <NA>              Shipper      TABLE    <NA>
5       <NA>        <NA>             Supplier      TABLE    <NA>
6       <NA>        <NA>                Order      TABLE    <NA>
7       <NA>        <NA>              Product      TABLE    <NA>
8       <NA>        <NA>          OrderDetail      TABLE    <NA>
9       <NA>        <NA> CustomerCustomerDemo      TABLE    <NA>
10      <NA>        <NA>  CustomerDemographic      TABLE    <NA>
11      <NA>        <NA>               Region      TABLE    <NA>
12      <NA>        <NA>            Territory      TABLE    <NA>
13      <NA>        <NA>    EmployeeTerritory      TABLE    <NA>
14      <NA>        <NA>     ProductDetails_V       VIEW    <NA>

Untuk mematikan koneksi dengan database dapat menggunakan fungsi odbcClose

odbcClose(con)

Connect with RSQLite

Jika RODBC dapat digunakan untuk RDBMS apa saja selama tersedia driver-nya, package RSQLite ini digunakan khusus untuk SQLite.

Menyiapkan Database dengan Menggunakan dbConnect()

Untuk melakukan koneksi ke database, dapat dilakukan dengan beberapa cara. Cara pertama: menggunakan library DBI. Jika menggunakan library DBI, maka ketika membuat koneksi ke database, menggunakan RSQLite::.

library(DBI)
# koneksi ke database, 
mydb <- dbConnect(RSQLite::SQLite(), "D:\\Northwind_large.sqlite")
mydb

<SQLiteConnection>
  Path: D:\Northwind_large.sqlite
  Extensions: TRUE

Untuk mematikan koneksi dengan database dapat menggunakan fungsi dbDisconnect

# mematikan koneksi database
dbDisconnect(mydb)

Cara kedua: menggunakan library RSQLite

library(RSQLite)
# koneksi ke database, 
mydb <- dbConnect(SQLite(), "D:\\Northwind_large.sqlite")
mydb

<SQLiteConnection>
  Path: D:\Northwind_large.sqlite
  Extensions: TRUE

Untuk melihat tabel-tabel di dalam database tersebut dapat menggunakan fungsi dbListTables

dbListTables(mydb)

 [1] "Category"             "Customer"             "CustomerCustomerDemo"
 [4] "CustomerDemographic"  "Employee"             "EmployeeTerritory"   
 [7] "Order"                "OrderDetail"          "Product"             
[10] "ProductDetails_V"     "Region"               "Shipper"             
[13] "Supplier"             "Territory"           

dbGetQuery()

Fungsi dbGetQQuery memungkinkan kita untuk menuliskan query dan mengambil hasilnya. Query harus dituliskan menggunakan syntax SQL yang sesuai dengan tipe databasenya.

# menyimpan quey pada txtSQL
txtSQL <- "SELECT * FROM Product"

# retrieve data 
mydata <- dbGetQuery(mydb, txtSQL)

Batched queries

Jika data yang akan diambil besar dan memory tidak cukup untuk mengambil data sekaligus, maka pengambilan data dapat dibagi-bagi. Fungsi yang dapat digunakan dalam batched queries adalah dbSendQuery, dbFetch, dbHasCompleted, dbClearResults. Pada dbFetch, default-nya akan mengambil semua data yang ada pada suatu tabel. Hal ini dapat kita atur untuk mengambil sebagian datanya dengan me-setting paramater n-nya. dbHasCompleted akan memeriksa apakah semua data sudah terambil atau belum.

Sebagai ilustrasi, akan diambil data dari tabel Product yang berisi 77 baris, dengan menggunakan fungsi dbFetch dan memberi nilai n=10 pada parameter n-nya, maka yang diambil adalah 10 record saja. Selanjutnya fungsi dbHasCompleted akan memeriksa apakah semua data pada tabel tersebut sudah terambil atau belum.

rs <- dbSendQuery(mydb, txtSQL)

# mengambil data, default mengambil seluruh data, bisa diatur ambil berapa baris
d1 <- dbFetch(rs, n= 10)

# memeriksa apakah semua data sudah terambil atau belum
# bernilai false
dbHasCompleted(rs)

[1] FALSE

dbHasCompleted akan bernilai FALSE karena belum semua data di tabel tersebut terambil. Untuk mengambil sisa data di tabel tersebut, maka parameter n dapat di-setting dengan nilai -1 atau inv.

# mengambil sisanya dengan mengganti n=-1
d2 <- dbFetch(rs, n = -1)

Lakukan pengecekan apakah semua record sudah terambil. Jika sudah maka dbHasCompleted akan bernilai TRUE.

# mengecek apakah sudah selesai
dbHasCompleted(rs)

[1] TRUE

Jika sudah selesai bersihkan dengan dbClearResults.

dbClearResult(rs)

# mematikan koneksi
dbDisconnect(mydb)

SQL Engine

Cara lain untuk dapat menggunakan syntax SQL pada R adalah dengan men-setting koneksi dan engine-nya. Dengan menambahkan parameter connection dan engine="sql".

Selanjutnya jika sudah dilakukan setting, maka syntax SQL dapat langsung diaplikasikan. Sebagai ilustrasi, menggunakan fungsi select.

```{r, connection=mydb, engine="sql"}

SELECT * FROM Product;

Inner Join

Join merupakan sebuah proses untuk menggabungkan tabel. Ada berbagai cara untuk melakukan join yaitu inner join, left join, right join, full join, semi join, anti join, dll. Namun, penggunaan join dalam package RSQLite ini hanya akan dilakukan untuk dua jenis join, yaitu inner join dan left join. Sebagai ilustrasi akan dilakukan join tabel dari database Northwind.

Terdapat tabel Product, Supplier, yang akan digabungkan menjadi satu tabel berisi id, ProductName, CompanyName, dan Address, maka akan digabungkan dengan INNER JOIN. Inner join akan mengembalikan tabel yang mempunyai nilai yang sama pada kedua tabel.

SELECT a.Id, a.ProductName, b.CompanyName, b.Address
  FROM Product as a INNER JOIN Supplier as b
  ON a.SupplierId=b.Id;

Left Join

Misalkan kita ingin melihat informasi mengenai penjelasan kategori untuk masing masing produk, maka dapat dilakukan dengan menggabungkan tabel Products dan Categories menggunakan fungsi LEFT JOIN. Fungsi LEFT JOIN akan mengembalikan semua nilai dari tabel di sebelah kiri (tabel 1) yang mempunyai nilai yang sama dengan tabel di sebelah kanan (tabel 2). Jika di tabel sebelah kanan tidak ada data yang sama, makan akan terisi null.

SELECT a.Id, a.ProductName, b.CategoryName, b.Description
  FROM Product as a LEFT JOIN Category as b
  ON a.CategoryId=b.Id

Fungsi tbl

Menuliskan fungsi SQL dengan menggunakan fungsi tbl dari package dplyr. Berikut akan diambil beberapa tabel dari database chinook untuk dilakukan proses join. Untuk mengambil tabel tersebut dapat dilakukan dengan fungsi berikut.

tbl.albums <- tbl(mydb, sql("SELECT * FROM albums"))
tbl.playlistTrack <- tbl(mydb, sql("SELECT * FROM playlist_track"))
tbl.playlist <-tbl(mydb,sql("SELECT * FROM playlists"))
tbl.artists <- tbl(mydb, sql("SELECT * FROM artists"))
tbl.genres <- tbl(mydb, sql("SELECT * FROM genres"))
tbl.tracks <- tbl(mydb, sql("SELECT * FROM tracks"))

inner_join

Inner join tabel juga dapat dilakukan dengan package dplyr menggunakan fungsi inner_join. inner_join akan digunakan untuk menggabungkan Tabel Artist dan Album

inner_join(tbl.artists, tbl.albums)

Joining, by = "ArtistId"

Hasil dari inner_join tersebut adalah menampilkan seluruh nilai yang sama dari tabel “Artist” dan “Albums”, serta menampilkan semua kolom pada masing masing tabel.

left_join

Menggunakan left_join untuk menggabungkan tabel “Tracks” dan “Album”.

left_join(tbl.albums, tbl.tracks) 

Joining, by = "AlbumId"

right_join

Fungsi right join akan mengembalikan semua nilai dari tabel di sebelah kanan (tabel 2) yang mempunyai nilai yang sama dengan tabel di sebelah kiri (tabel 1). Nilai yang dikembalikan akan null dari tabel di sebelah kiri jika tidak ada data yang sama. Sebagai ilustrasi akan dilakukan right join dengan menggunakan fungsi right_join pada tabel “Album” dan “Artist”.

right_join(tbl.albums, tbl.artists) 

Joining, by = "ArtistId"

full_join

Full join akan mengembalikan semua nilai yang sama dari tabel di sebelah kiri (tabel 1) atau tabel di sebelah kanan (tabel 2). Full join dapat dilakukan dengan menggunakan fungsi full_join. Sebagai ilustrasi akan dilakukan full join dari tabel “Playlist” dan “Playlist Tracks”

full_join(tbl.playlist, tbl.playlistTrack)

Joining, by = "PlaylistId"

semi_join

Fungsi semi_join akan mengembalikan semua nilai yang sama pada tabel di sebelah kiri (tabel 1) dengan tabel di sebelah kanan (tabel 2), dengan mengembalikan semua kolom dari tabel 1. Ilustrasi yang sama akan digunakan pada fungsi ini adalah menggabungkan tabel Genre dan tabel Track.

trackFilter <- tbl.tracks %>% 
  rename(SongName = Name)
semi_join(tbl.genres, trackFilter)

Joining, by = "GenreId"

anti_join

Fungsi anti_join akan mengembalikan nilai pada tabel di sebelah kiri (tabel 1) yang tidak memiliki nilai yang sama dengan tabel di sebelah kanan (tabel 2), dan menampilkan semua kolom pada tabel 1. Ilustrasi yang sama akan digunakan pada fungsi ini adalah untuk tabel Artist dan Album.

anti_join(tbl.artists, tbl.albums)

Joining, by = "ArtistId"

Shapefiles

Shapefiles terdiri dari beberapa file dengan beberapa ekstensi yang disimpan dalam satu direktori dengan nama file yang sama. Ekstensi yang membentuk shapefiles adalah .shp, .shx, .dbf. Terdapat beberapa package di R untuk membaca shapefiles, tetapi yang sering digunakan adalah package sp dan rgdal.

Membaca Shapefiles

Untuk membaca shapefiles menggunakan package rgdal maka pertama buat objek berisi path dimana shapefiles tersebut berada. Gunakan perintah list.files(objek) untuk melist semua file di dalam folder yang dituju.

library(rgdal)
# menyimpan path folder ke dsn
dsn <- "D:\\SD\\SnowGIS_SHP"

# menampilkan semua file di folder tersebut
list.files(dsn)

 [1] "Cholera_Deaths.dbf"          "Cholera_Deaths.prj"         
 [3] "Cholera_Deaths.sbn"          "Cholera_Deaths.sbx"         
 [5] "Cholera_Deaths.shp"          "Cholera_Deaths.shx"         
 [7] "OSMap.tfw"                   "OSMap.tif"                  
 [9] "OSMap_Grayscale.tfw"         "OSMap_Grayscale.tif"        
[11] "OSMap_Grayscale.tif.aux.xml" "OSMap_Grayscale.tif.ovr"    
[13] "Pumps.dbf"                   "Pumps.prj"                  
[15] "Pumps.sbx"                   "Pumps.shp"                  
[17] "Pumps.shx"                   "README.txt"                 
[19] "SnowMap.tfw"                 "SnowMap.tif"                
[21] "SnowMap.tif.aux.xml"         "SnowMap.tif.ovr"            

Untuk melihat ada shapefiles apa saja di dalam folder dapat menggunakan fungsi ogrListLayers(objekPath).

# melihat ada berapa shapefiles, dan shapefilesnya apa saja
ogrListLayers(dsn)

[1] "Cholera_Deaths" "Pumps"         
attr(,"driver")
[1] "ESRI Shapefile"
attr(,"nlayers")
[1] 2

# mengetahui informasi tentang shapefiles
ogrInfo(dsn, layer = "Cholera_Deaths")

Source: "D:\SD\SnowGIS_SHP", layer: "Cholera_Deaths"
Driver: ESRI Shapefile; number of rows: 250 
Feature type: wkbPoint with 2 dimensions
Extent: (529160.3 180857.9) - (529655.9 181306.2)
CRS: +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs 
LDID: 87 
Number of fields: 2 
   name type length typeName
1    Id    0      6  Integer
2 Count    0      4  Integer

Untuk melihat informasi mengenai suatu shapefiles dapat menggunakan fungsi ogrInfo

ogrInfo(dsn, layer="Cholera_Deaths")

Source: "D:\SD\SnowGIS_SHP", layer: "Cholera_Deaths"
Driver: ESRI Shapefile; number of rows: 250 
Feature type: wkbPoint with 2 dimensions
Extent: (529160.3 180857.9) - (529655.9 181306.2)
CRS: +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs 
LDID: 87 
Number of fields: 2 
   name type length typeName
1    Id    0      6  Integer
2 Count    0      4  Integer

Untuk membaca data di dalam shapefiles dapat menggunakan fungsi readOGR.

# Membaca data shapefiles ke dalam objek R hasilnya adalah class khusus objek spasial
CholeraDeaths <- readOGR(dsn, layer = "Cholera_Deaths")

OGR data source with driver: ESRI Shapefile 
Source: "D:\SD\SnowGIS_SHP", layer: "Cholera_Deaths"
with 250 features
It has 2 fields

class(CholeraDeaths)

[1] "SpatialPointsDataFrame"
attr(,"package")
[1] "sp"

Untuk menampilkan statistik yang berada di dalam objek tersebut maka menggunakan fungsi summary

summary(CholeraDeaths)

Object of class SpatialPointsDataFrame
Coordinates:
               min      max
coords.x1 529160.3 529655.9
coords.x2 180857.9 181306.2
Is projected: TRUE 
proj4string :
[+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000
+y_0=-100000 +ellps=airy +units=m +no_defs]
Number of points: 250
Data attributes:
       Id        Count       
 Min.   :0   Min.   : 1.000  
 1st Qu.:0   1st Qu.: 1.000  
 Median :0   Median : 1.000  
 Mean   :0   Mean   : 1.956  
 3rd Qu.:0   3rd Qu.: 2.000  
 Max.   :0   Max.   :15.000  

Menampilkan Peta

Plot

data.shape <- readOGR(dsn, layer="Cholera_Deaths")

OGR data source with driver: ESRI Shapefile 
Source: "D:\SD\SnowGIS_SHP", layer: "Cholera_Deaths"
with 250 features
It has 2 fields

# menampilkan data dengan plot
plot(data.shape)

data.pump <- readOGR (dsn, layer = "Pumps")

OGR data source with driver: ESRI Shapefile 
Source: "D:\SD\SnowGIS_SHP", layer: "Pumps"
with 8 features
It has 1 fields

# menampilkan data dengan paket ggplot2
points(data.pump, col="red")

ggplot

# eksrak koordinat dengan fungsi coordinates
cholera.coords <- as.data.frame(coordinates(CholeraDeaths))
pumps <- readOGR(dsn, layer = "Pumps")

OGR data source with driver: ESRI Shapefile 
Source: "D:\SD\SnowGIS_SHP", layer: "Pumps"
with 8 features
It has 1 fields

pumps.coords <- as.data.frame(coordinates(pumps))
ggplot(cholera.coords) +
  geom_point(aes(x=coords.x1, y = coords.x2)) +
  geom_point(aes(x=coords.x1, y = coords.x2, col=I("red")),
               data = pumps.coords)