Example for RSQLite Database

[jmanacup]

Good day,

I stumbled upon the blog that talks about this package and was wondering if there is an SQLite version to it. It errors out once I called the addressMatchR::calc_match_addresses() function as it calls the nhsbsaR::oracle_unnest_tokens() under the hood. Here is a simple example using SQLite connection in DBI::dbconnect() :

library(DBI)
library(dplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

data_one <- data.frame(
  ADDRESS_ONE = c(
    "10 KINGS ROAD", "11 KINGS ROAD", "12 KINGS RD", 
    "13 KONGS ROAD", "14 KING ROD", "15A KINGS ROADD",
    "15B KINGS RD", "THE SHOP KINGS RD", "THE SHIP KINGS ROAD"
  ),
  POSTCODE_ONE = c(
    rep("ABCD 333", 2), rep("ABCD 333", 7) 
  )
)

data_two <- data.frame(
  ADDRESS_TWO = c(
    "10 KINGS ROAD", "11 KINGS ROAD", "12, kings, road",
    "13, kings, rd", "14, king, road", "15A, kings road",
    "15B, kings road", "the shop, king-rd", "the ship kings,,road"
  ),
  POSTCODE_TWO = c(
    "abcd123", "abcd123", "ABCD-123", "ABCD-123", "ABCD.123",
    "ABCD  123", "ABCD 123", "ABCD123###", "ABCD 123"
  )
)

DBI::dbWriteTable(con, name = "TEST_ADDRESS_DATA_ONE", value = data_one)
DBI::dbWriteTable(con, name = "TEST_ADDRESS_DATA_TWO", value = data_two)

data_one <- dplyr::tbl(con, "TEST_ADDRESS_DATA_ONE") %>% 
  addressMatchR::tidy_postcode(df = ., col = POSTCODE_ONE) %>% 
  addressMatchR::tidy_single_line_address(df = ., col = ADDRESS_ONE)

data_two <- dplyr::tbl(con, "TEST_ADDRESS_DATA_TWO") %>% 
  addressMatchR::tidy_postcode(df = ., col = POSTCODE_TWO) %>% 
  addressMatchR::tidy_single_line_address(df = ., col = ADDRESS_TWO)

results_db <- addressMatchR::calc_match_addresses(
    data_one,
    "ADDRESS_ONE",
    "POSTCODE_ONE",
    data_two,
    "ADDRESS_TWO",
    "POSTCODE_TWO"
)