a failed attempt to fill spaces between multilinestrings

fill_line_holes.r

library(DBI)
library(RPostgreSQL)
library(sf)
library(dplyr)
library(readxl)
library(jsonlite)

congested_segments_file <- "data/WeekdayCongestedSegmentsList_2017_v2.xlsx" 
database_credentials_file <- "~/credentials/tbuckley_gisdb3.json"

#set the working directory to be that of the script
#assumes user is running r studio
setwd(dirname(rstudioapi::getActiveDocumentContext()$path))

cs_18 <- readxl::read_excel(congested_segments_file)

#connect to the db
db3_cred <- jsonlite::read_json(database_credentials_file)

db3 <- dbConnect(RPostgreSQL::PostgreSQL(),
                 dbname = db3_cred['database'], 
                 host = db3_cred['host'], 
                 port = 5432,
                 user = db3_cred['username'],
                 password = db3_cred['password'])

tmc_ids1 <- cs_18$`TMC ID`

tmc_query <- readr::read_file("congested_segments.sql")

tmc_query <- glue::glue_sql(tmc_query,
                            tmc_ids = tmc_ids1,
                            .con=db3)

dat <- dbGetQuery(db3, tmc_query)

df3 <- st_as_sf(dat, wkt = "geom", crs = 3857)

cs17_sf <- inner_join(df3,cs_18,by=c("tmcid"="TMC ID"))

cs17_limits <- cs17_sf %>% 
  group_by(LIMITS,startTime,endTime,
           `Route #`,DIR,`Segment Delay  (Veh-Hrs)`,Rank,`length (Miles)`) %>% 
  summarise()

cs17_limits$start_hour <- hour(lubridate::ymd_hms(cs17_limits$startTime))
cs17_limits$start_minute <- minute(lubridate::ymd_hms(cs17_limits$startTime))

cs17_limits$end_hour <- hour(lubridate::ymd_hms(cs17_limits$endTime))
cs17_limits$end_minute <- minute(lubridate::ymd_hms(cs17_limits$endTime))


############
##example 'hole'
############
multi_121 <- cs17_limits[121,]$geom

points_121 <- st_cast(multi_121, "MULTIPOINT")
xy_121 <- st_coordinates(points_121)
xy_121 <- as_tibble(xy_121)
xy_121 <- arrange(xy_121,X,Y)
st_geometry(cs17_limits[121,]) <- st_sfc(st_multilinestring(c(st_linestring(as.matrix(xy_121[,1:2])))))

m1 <- mapview(cs17_limits, zcol="Rank", legend=TRUE)
mapshot(m1, url = paste0(getwd(), "/congested_segments_map17.html"))

st_write(cs17_limits,"cs17.gpkg")
st_write(cs17_limits,"cs17.geojson")

###
#draft check for 'holes' methodically
####
#this is a stub--doesn't seem to work
#could be that the arrange(start_lat,start_long)
#needs more work--perhaps these arent good start lats and longs

multi <- "S VAN NESS AVE to I-80 MERGE"
cs17_ordered <- cs17_sf %>% 
  filter(LIMITS == multi) %>%
    arrange(start_latitude,
            start_longitude,
            end_latitude,
            end_longitude)

for (i in 1:length(cs17_ordered$geom)){
  cs17_ordered[i,'distance_from_next'] <- st_distance(cs17_ordered[i+1,'geom'],cs17_ordered[i,'geom'])
}

single <- "3RD ST to CESAR CHAVEZ ST"
cs17_ordered <- cs17_sf %>% 
  filter(LIMITS == single) %>%
  arrange(start_latitude,start_longitude,end_latitude,end_longitude)

for (i in 1:length(cs17_ordered$geom)){
  cs17_ordered[i,'distance_from_next'] <- st_distance(cs17_ordered[i+1,'geom'],cs17_ordered[i,'geom'])
}