Section 8 Creating an SQLite database

To create a new SQLite database and connect to it, use

mydb <- dbConnect(RSQLite::SQLite(), sqlite_filename)

The SQLite database is a single file with a specific structure. SQLite databases are simple to set up and use. The do not require the same type of software installation as other database applications. The SQLite database file can be used across different computers and operating systems by simply copying the file. Also the GPKG version can store spatial data, and the SpatiaLite extension to SQLite adds spatial data management and analysis capabilities within the database, similar to how PostGIS adds functionality to PostgreSQL.

In this part of the workshop we will go through a set of various operations using SQLite.

<!--<start rmd_add_06.txt>-->
# SQLite

## Create an SQLite database

```{r sqlite1}
# make the connection if it does not exist. If the file does not exist, it will be created.
if(!exists("mydb")){
    mydb <- dbConnect(RSQLite::SQLite(), "../data/rsql.sqlite")
}
```

## Copy some data into the SQLite database

We write the GPS data into the database. If the table already exists, we will get an error, so a test first will help.

```{r sqlite1}
# make the connection if it does not exist. If the file does not exist, it will be created.
if(exists("mydb")){
    if(!dbIsValid(mydb)){
        mydb <- dbConnect(RSQLite::SQLite(), "../data/rsql.sqlite")
    }
}
```

## Verify data

```{r sqlite3}
dbListTables(conn = mydb)
```

## Summarize data

```{r sqlite4}
gps_caldate_sum <- dbGetQuery(conn = mydb, statement = "select calendar_date, count(*) as n_records from gps_tracks group by calendar_date order by calendar_date;")

DT::datatable(gps_caldate_sum)
```

# Disconnect

Now that we are done, we will disconnect all connections.

```{r disconnectall}
# our custom PostgreSQL function
disconnectall(verbose = TRUE)

dbDisconnect(mydb)
```

<hr class="new4">
<!--<end rmd_add_06.txt>-->

and then render the file.

rmarkdown::render("rmd/r_sql.rmd")