src_mowerplus <- function(backup_id, data_loc = "~/Data", overwrite = TRUE) {
  
  require(XML, quietly = TRUE, warn.conflicts = FALSE) # to read plist (property list) files
  require(tidyverse, quietly = TRUE, warn.conflicts = FALSE) # for printing and access to sqlite dbs
  
  # root of mobile backup dir for `backup_id`
  mb <- path.expand(file.path("~/Library/Application Support/MobileSync/Backup", backup_id))
  stopifnot(dir.exists(mb))
  
  data_loc <- path.expand(data_loc)
  stopifnot(dir.exists(data_loc))
  
  tf <- tempfile(fileext = ".sqlite")
  on.exit(unlink(tf), add=TRUE)
  
  # path to the extracted sqlite file
  out_db <- file.path(data_loc, "mowtrack.sqlite")
  
  file.copy(file.path(mb, "Manifest.db"), tf, overwrite = TRUE)
  
  manifest_db <- src_sqlite(tf)
  
  fils <- tbl(manifest_db, "Files")
  
  filter(fils, relativePath == "Library/Application Support/MowTracking.sqlite") %>%
    pull(fileID) -> mowtrackdb_loc
  
  file.copy(
    file.path(mb, sprintf("%s/%s", substr(mowtrackdb_loc, 1, 2), mowtrackdb_loc)),
    file.path(data_loc, "mowtrack.sqlite"),
    overwrite = overwrite
  )
  
  src_sqlite(out_db)
  
}

from_coredata_ts <- function(x, tz = NULL) {
  .POSIXct(ifelse(
    test = floor(log10(x)) >= 10, # If you're still using R in 2317 then good on ya and edit this
    yes = as.POSIXct(x/10e8, origin = "2001-01-01"), # nanoseconds coredata
    no = as.POSIXct(x, origin = "2001-01-01") # seconds coredata
  ), tz = tz)
}
library(hrbrthemes)

mow_db <- src_mowerplus("28500cd31b9580aaf5815c695ebd3ea5f7455628")

mow_db
## src:  sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
## tbls: Z_METADATA, Z_MODELCACHE, Z_PRIMARYKEY, ZACTIVITY, ZDEALER,
##   ZMOWALERT, ZMOWER, ZMOWLOCATION, ZSMARTCONNECTOR, ZUSER

See what’s in the ZMOWER table after 2 mows

glimpse(tbl(mow_db, "ZMOWER"))
## Observations: ??
## Variables: 23
## Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
## $ Z_PK                      <int> 1
## $ Z_ENT                     <int> 7
## $ Z_OPT                     <int> 11
## $ ZDECKSIZEINCHES           <int> 48
## $ ZDISMISSEDFULLSERVICETASK <int> 0
## $ ZDISMISSEDPERIODICTASK    <int> 0
## $ ZSMARTCONNECTOR           <int> NA
## $ ZUSER                     <int> 1
## $ ZBATTERYCHARGE            <dbl> NA
## $ ZENGINEHOURS              <dbl> 3.474705
## $ ZFULLSERVICEPERFORMED     <dbl> NA
## $ ZHMCLASTSEEN              <dbl> NA
## $ ZHMCOFFSET                <dbl> 0
## $ ZPERIODICSERVICEPERFORMED <dbl> NA
## $ ZSCLASTCONNECTED          <dbl> NA
## $ ZGENERICTYPE              <chr> NA
## $ ZHMCIDENTIFIER            <chr> NA
## $ ZMODEL                    <chr> "E140"
## $ ZSCPIN                    <chr> NA
## $ ZSCPERIPHERALID           <chr> NA
## $ ZSERIALNUMBER             <chr> "1GXE140EKKK116940"
## $ ZSERIES                   <chr> "E100"
## $ ZSCDATADICTIONARY         <blob> <NA>

This is the one from last time which has all the mowing metadata/summary data

glimpse(tbl(mow_db, "ZACTIVITY"))
## Observations: ??
## Variables: 20
## Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
## $ Z_PK           <int> 1, 2
## $ Z_ENT          <int> 3, 3
## $ Z_OPT          <int> 124, 93
## $ ZMONTH         <int> 6, 6
## $ ZYEAR          <int> 2019, 2019
## $ ZMOWER         <int> 1, 1
## $ ZUSER          <int> 1, 1
## $ ZISCOMPLETE    <int> 1, 1
## $ ZISMISSEDMOW   <int> 0, 0
## $ ZLASTLOCATION  <int> 7016, 12548
## $ ZCREATEDAT     <dbl> 581100260, 581778616
## $ ZENGINEHOURS   <dbl> NA, NA
## $ ZAREACOVERED   <dbl> 3.761875, 2.286811
## $ ZAVERAGESPEED  <dbl> 3.727754, 2.894269
## $ ZDISTANCEMOWED <dbl> 7.758894, 4.716564
## $ ZMOWINGTIME    <dbl> 6960.000, 5548.939
## $ ZNOTES         <chr> "First mow!", NA
## $ ZINTERVALNAME  <chr> NA, NA
## $ ZTYPE          <chr> NA, NA
## $ ZUUID          <blob> blob[238 B], blob[238 B]

tbl(mow_db, "ZACTIVITY")%>%
  collect() -> activity

activity %>% 
  select(
    mow_date = ZCREATEDAT, 
    area_covered = ZAREACOVERED, 
    avg_speed = ZAVERAGESPEED, 
    distance = ZDISTANCEMOWED, 
    duration = ZMOWINGTIME
  ) %>% 
  arrange(mow_date) %>% 
  mutate(
    duration = duration / 60 / 60, # hours
    mow_date = format(from_coredata_ts(mow_date), "%b %d"), # factors make better bars
    mow_date = factor(mow_date, levels = unique(mow_date)) # when there are just 2-of-em
  ) %>% 
  gather(measure, value, -mow_date) %>% 
  ggplot(aes(mow_date, value)) +
  geom_col(aes(fill = measure), width = 0.5, show.legend = FALSE) +
  scale_y_comma() +
  scale_fill_ipsum() +
  facet_wrap(~measure, scales = "free") +
  theme_ipsum_rc(grid="Y")

This has all of the captured mowing track data

zloc <- tbl(mow_db, "ZMOWLOCATION")

glimpse(zloc)
## Observations: ??
## Variables: 16
## Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
## $ Z_PK                <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 1…
## $ Z_ENT               <int> 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8,…
## $ Z_OPT               <int> 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
## $ ZISPAUSEDPOINT      <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ ZORDER              <int> 1, 2, 0, 11, 20, 58, 38, 43, 30, 25, 21, 10,…
## $ ZSESSION            <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
## $ ZSESSION2           <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ ZALTITUDE           <dbl> 42.64804, 42.70590, 40.99661, 39.54770, 38.2…
## $ ZCOURSE             <dbl> 358.242188, 332.226562, 18.281250, 260.85937…
## $ ZHORIZONTALACCURACY <dbl> 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5,…
## $ ZLATITUDE           <dbl> 43.25913, 43.25914, 43.25913, 43.25915, 43.2…
## $ ZLONGITUDE          <dbl> -70.80069, -70.80069, -70.80069, -70.80067, …
## $ ZSPEED              <dbl> 0.0000000, 0.4250179, 0.5592341, 0.3802792, …
## $ ZTIMESTAMP          <dbl> 581100271, 581100272, 581100270, 581100281, …
## $ ZVERTICALACCURACY   <dbl> 6, 6, 8, 6, 4, 4, 4, 3, 4, 4, 4, 6, 4, 4, 4,…
## $ ZKLVDATA            <blob> <NA>, <NA>, <NA>, <NA>, <NA>, <NA>, <NA>, <…

Try to figure out which is the “key” for unique moes

distinct(zloc, Z_ENT)
## # Source:   lazy query [?? x 1]
## # Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
##   Z_ENT
##   <int>
## 1     8
distinct(zloc, Z_OPT)
## # Source:   lazy query [?? x 1]
## # Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
##   Z_OPT
##   <int>
## 1     1
## 2     2
distinct(zloc, ZSESSION)
## # Source:   lazy query [?? x 1]
## # Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
##   ZSESSION
##      <int>
## 1        1
## 2        2

count(zloc, Z_OPT)
## # Source:   lazy query [?? x 2]
## # Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
##   Z_OPT     n
##   <int> <int>
## 1     1 12358
## 2     2   209

count(zloc, ZSESSION)
## # Source:   lazy query [?? x 2]
## # Database: sqlite 3.22.0 [/Users/hrbrmstr/Data/mowtrack.sqlite]
##   ZSESSION     n
##      <int> <int>
## 1        1  7018
## 2        2  5549

Def looks like ZSESSION is it:

group_by(zloc, ZSESSION) %>% 
  summarise(min_ts = min(ZTIMESTAMP), max_ts = max(ZTIMESTAMP)) %>% 
  ungroup() %>% 
  collect() %>% 
  mutate_all(from_coredata_ts)
## # A tibble: 2 x 3
##   ZSESSION            min_ts              max_ts             
##   <dttm>              <dttm>              <dttm>             
## 1 2000-12-31 19:00:01 2019-06-01 12:44:29 2019-06-01 14:41:26
## 2 2000-12-31 19:00:02 2019-06-09 09:10:19 2019-06-09 10:42:47

Speed check

zloc %>% 
  select(
    id = ZSESSION,
    zorder = ZORDER,
    lat = ZLATITUDE,
    lng = ZLONGITUDE,
    speed = ZSPEED,
    ts = ZTIMESTAMP
  ) %>% 
  collect() %>% 
  mutate(
    id = factor(id),
    ts = from_coredata_ts(ts)
  ) -> sessions

ggplot(sessions, aes(id, speed)) +
  ggbeeswarm::geom_quasirandom(
    aes(fill = id), show.legend = FALSE,
    shape = 21, size = 2, color = "white", stroke = 0.75
  ) +
  scale_fill_ipsum() +
  labs(x = "Mowing Session", y = "MPH", title = "Mowing Speed Comparison (mph)") +
  theme_ipsum_rc(grid="Y")