I needed to convert a shapefile into a SQLite data base, which should be easy using R's rgdal/maptools and RSQLite packages, no? If you're new to R like me, you may run into some troubles, so here are some issues:
1. Basic RSQLite database interaction
The package RSQLite is (unfortunately) merely a driver for R's stanardized data base interface DBI, which is rather poorly documented and kind of unintuitive. This means that we need to use functions from the DBI package to interact with our database, which makes things unnecessarily complicated. But whatever, here is a basic interaction:
library(RSQLite)
library(DBI)
# set up SQLite DB connection
db <- dbConnect(dbDriver("SQLite"), dbname = "mydatabase.sqlite")
# query
addresses <- dbGetQuery(db, SELECT * FROM addresses")
# close
dbDisconnect(db)
If you google around a little, you'll see that we basically have to basic functions for those kinds of queries: dbGetQuery and dbSendQuery .. the difference here is that with the latter, you will have to catch the result and use the 'fetch' command to grab the data and then cleanup using the dbClearResult, i.e. you need three commands. This
must be done after every SQL query, even if you don't care about the response like after a "INSERT INTO" command. In a nutshell: use dbGetQuery which does this all for you.
2. "CREATE DATABASE does not work!!"
I'm not sure if this is a SQLite thing, but at least you can not/don't need to do that in R's RSQLite interface. In other words, you can only have one single database per SQLite-file. So, no big thing, just start off with "CREATE TABLE". :I
3. Large Amounts of Data
When one uses a database, normally there is quite a few data in the game. What to do if you need to get a lot of data into or out of the data base? As soon as you start using for loops to add rows from your data base, you'll probably run into performance problems since apparently setting up the connection for each query takes considerable time. Two work arounds:
a. dbGetPreparedQuery with manual transaction:
dbBeginTransaction(db)
dbGetPreparedQuery(db, "INSERT INTO addresses (name, phone, address) VALUES (?,?,?)", bind.data=new_empolyee_data);
dbCommit(db)
This does not excecute the query until dbCommit, in other words, you fill something like a buffer. The dbGetPreparedQuery automatically fills out the "?"-fields in your query string and submit those strings to the database. Again, there is a dbSendPreparedQuery alternative, where you'll have to clear the result manually after the command.
The bind.data parameter requires a data frame (what is that? boo.). So if you get an error like:
Error in function (classes, fdef, mtable) :
unable to find an inherited method for function "dbGetPreparedQuery", for signature "SQLiteConnection", "character", "matrix"
.. convert it to a data frame using the as.data.frame(..) function, see example below.
b. write or read tables as a whole:
You can conveniently add a full table to the data base using the dbWriteTable command. The field names in SQL are taken from the column names of your matrix. Numeric values are stored in a 'REAL' field, strings in a general TEXT field. I don't know if you can customize this, it's rather quick and dirty, so to say.
# give the data matrix appropriate column names
colnames(addr) <- c("key", "empolyee", "phone", "address")
# add the table as data.frame to the db
dbWriteTable(db, "new_addresses", as.data.frame(addr))
Here again, this must be a data frame to work correctly.
If you want to insert something into an already existing data base you can use SQL to do that on the database itself:
INSERT INTO addresses (name, phone, address)
SELECT * FROM new_addresses
