# Creates an Excel checklist from pivot.geojson # Fields sorted largest to smallest, split across Timon/Joey/Dimitra side-by-side # Install packages if needed if (!requireNamespace("jsonlite", quietly = TRUE)) install.packages("jsonlite", repos = "https://cloud.r-project.org") if (!requireNamespace("openxlsx", quietly = TRUE)) install.packages("openxlsx", repos = "https://cloud.r-project.org") library(jsonlite) library(openxlsx) # ---- Load GeoJSON ---- geojson_path <- "laravel_app/storage/app/angata/pivot.geojson" gj <- fromJSON(geojson_path, simplifyVector = FALSE) features <- gj$features cat(sprintf("Total features: %d\n", length(features))) # ---- Shoelace area (degrees²) ---- shoelace <- function(ring) { n <- length(ring) lons <- sapply(ring, `[[`, 1) lats <- sapply(ring, `[[`, 2) area <- 0 for (i in seq_len(n)) { j <- (i %% n) + 1 area <- area + lons[i] * lats[j] - lons[j] * lats[i] } abs(area) / 2 } # ---- Approx area in m² ---- area_m2 <- function(ring) { R <- 6371000 lats <- sapply(ring, `[[`, 2) mean_lat <- mean(lats) lat_rad <- mean_lat * pi / 180 m_per_deg_lat <- R * pi / 180 m_per_deg_lon <- R * cos(lat_rad) * pi / 180 shoelace(ring) * m_per_deg_lat * m_per_deg_lon } # ---- Compute feature areas ---- compute_area <- function(feat) { geom <- feat$geometry total <- 0 if (geom$type == "MultiPolygon") { for (polygon in geom$coordinates) { total <- total + area_m2(polygon[[1]]) # outer ring } } else if (geom$type == "Polygon") { total <- total + area_m2(geom$coordinates[[1]]) } total } field_names <- sapply(features, function(f) f$properties$field) areas_m2 <- sapply(features, compute_area) areas_ha <- areas_m2 / 10000 df <- data.frame( field = field_names, area_ha = round(areas_ha, 2), stringsAsFactors = FALSE ) # Sort largest to smallest df <- df[order(df$area_ha, decreasing = TRUE), ] df$rank <- seq_len(nrow(df)) cat("\nTop 10 fields by area:\n") print(head(df[, c("rank", "field", "area_ha")], 10)) # ---- Split: Timon=1st, Joey=2nd, Dimitra=3rd ---- idx <- seq_len(nrow(df)) timon <- df[idx %% 3 == 1, ] joey <- df[idx %% 3 == 2, ] dimitra <- df[idx %% 3 == 0, ] cat(sprintf("\nSplit: Timon=%d, Joey=%d, Dimitra=%d\n", nrow(timon), nrow(joey), nrow(dimitra))) # ---- Build Excel ---- wb <- createWorkbook() addWorksheet(wb, "Field Checklist") # Header colors col_timon <- "1F6AA5" col_joey <- "2E7D32" col_dimitra <- "7B1FA2" alt_timon <- "D6E4F0" alt_joey <- "D7F0D8" alt_dimitra <- "EDD7F0" header_font <- createStyle(fontName = "Calibri", fontSize = 11, fontColour = "FFFFFF", halign = "CENTER", valign = "center", textDecoration = "bold", border = "TopBottomLeftRight") sub_font <- createStyle(fontName = "Calibri", fontSize = 10, fontColour = "FFFFFF", halign = "CENTER", valign = "center", textDecoration = "bold", border = "TopBottomLeftRight") # Title row writeData(wb, "Field Checklist", "Angata Pivot Field Checklist — sorted largest to smallest", startRow = 1, startCol = 1) mergeCells(wb, "Field Checklist", cols = 1:14, rows = 1) addStyle(wb, "Field Checklist", createStyle(fontName = "Calibri", fontSize = 13, textDecoration = "bold", halign = "CENTER", valign = "center", fgFill = "F0F0F0"), rows = 1, cols = 1) setRowHeights(wb, "Field Checklist", rows = 1, heights = 28) # Person block writer write_person_block <- function(wb, ws_name, data, start_col, hdr_color, alt_color, person_name) { end_col <- start_col + 3 # Person name header (row 2) mergeCells(wb, ws_name, cols = start_col:end_col, rows = 2) writeData(wb, ws_name, person_name, startRow = 2, startCol = start_col) addStyle(wb, ws_name, createStyle(fontName = "Calibri", fontSize = 12, fontColour = "FFFFFF", textDecoration = "bold", halign = "CENTER", valign = "center", fgFill = hdr_color, border = "TopBottomLeftRight", borderColour = "999999"), rows = 2, cols = start_col:end_col) # Sub-headers (row 3) sub_headers <- c("#", "Field", "Area (ha)", "Checked \u2713") writeData(wb, ws_name, as.data.frame(t(sub_headers)), startRow = 3, startCol = start_col, colNames = FALSE) addStyle(wb, ws_name, createStyle(fontName = "Calibri", fontSize = 10, fontColour = "FFFFFF", textDecoration = "bold", halign = "CENTER", valign = "center", fgFill = hdr_color, border = "TopBottomLeftRight", borderColour = "999999"), rows = 3, cols = start_col:end_col) # Data rows (starting row 4) n <- nrow(data) for (i in seq_len(n)) { row_num <- i + 3 bg <- if (i %% 2 == 0) alt_color else "FFFFFF" # Rank writeData(wb, ws_name, i, startRow = row_num, startCol = start_col) # Field name writeData(wb, ws_name, data$field[i], startRow = row_num, startCol = start_col + 1) # Area writeData(wb, ws_name, data$area_ha[i], startRow = row_num, startCol = start_col + 2) # Checked (empty) writeData(wb, ws_name, "", startRow = row_num, startCol = start_col + 3) row_style <- createStyle(fontName = "Calibri", fontSize = 10, halign = "center", fgFill = bg, border = "TopBottomLeftRight", borderColour = "CCCCCC") field_style <- createStyle(fontName = "Calibri", fontSize = 10, halign = "left", fgFill = bg, border = "TopBottomLeftRight", borderColour = "CCCCCC") addStyle(wb, ws_name, row_style, rows = row_num, cols = start_col) addStyle(wb, ws_name, field_style, rows = row_num, cols = start_col + 1) addStyle(wb, ws_name, row_style, rows = row_num, cols = start_col + 2) addStyle(wb, ws_name, row_style, rows = row_num, cols = start_col + 3) } } write_person_block(wb, "Field Checklist", timon, 1, col_timon, alt_timon, "Timon") write_person_block(wb, "Field Checklist", joey, 6, col_joey, alt_joey, "Joey") write_person_block(wb, "Field Checklist", dimitra, 11, col_dimitra, alt_dimitra, "Dimitra") # Column widths (col 5 and 10 = spacers) setColWidths(wb, "Field Checklist", cols = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14), widths = c(5, 14, 10, 12, 2, 5, 14, 10, 12, 2, 5, 14, 10, 12)) # Row heights setRowHeights(wb, "Field Checklist", rows = 2:3, heights = c(22, 18)) max_rows <- max(nrow(timon), nrow(joey), nrow(dimitra)) setRowHeights(wb, "Field Checklist", rows = 4:(max_rows + 3), heights = 16) # Freeze panes below header freezePane(wb, "Field Checklist", firstActiveRow = 4) # Save out_path <- "angata_field_checklist.xlsx" saveWorkbook(wb, out_path, overwrite = TRUE) cat(sprintf("\nExcel saved to: %s\n", out_path)) cat(sprintf("Total: %d fields — Timon: %d, Joey: %d, Dimitra: %d\n", nrow(df), nrow(timon), nrow(joey), nrow(dimitra)))