Create single column GeoJSON using R

Question

I am trying to create an excel file, ready for use within Power Apps Geospatial features: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/geospatial-map-draw-shapes#import-geojson-shapes-into-a-map

This needs a single excel file, where each row is a seperate feature, which has a GeoJSON column which is a self contained GeoJSON object.

I can't work out how to do this using R and R Studio. My input file is a GeoJSON, but could be a shapefile.

I have tried a number of methods, but on each occasion I am tripping up with trying to get the features to export

Answer 1

I do not have access to Power Apps Geospatial so I can't test the end result but this:

library("openxlsx")

bob <- '{"type":"FeatureCollection","properties":{"kind":"state","state":"WA"},"features":[{"type":"Feature","properties":{"kind":"county","name":"Adams","state":"WA"},"geometry":{"type":"MultiPolygon","coordinates":[[[[-118.9503,47.2640],[-117.9590,47.2586],[-117.9699,46.8697],[-118.0466,46.7711],[-118.2109,46.7383],[-119.2132,46.7383],[-119.3720,46.7383],[-119.3665,46.9135],[-118.9832,46.9135],[-118.9777,47.2640]]]]}}]}'

test <- data.frame(
  County = "Adams",
  GeoJson = bob,
  TotalCases =  1689,
  Color = "RGB(184,210,232)"
)

openxlsx::write.xlsx(test, "test.xls")

is giving a similar result to your link.

"Bonus" (from a geojson):

# testing it from a geojson
writeLines(bob, "test.geojson") # create a small geojson

bill <- readLines("test.geojson")

test2 <- data.frame(
  County = "Adams",
  GeoJson = bill,
  Color = "RGB(184,210,232)"
)
# same result
openxlsx::write.xlsx(test2, "test2.xls")

Answer 2

Through brute force trying only, I've found a way to do this:

# Load the required library
library(sf)
library(geojsonsf)
library(jsonlite)
library(dplyr)
library(openxlsx)

# Set column name so it can be easily amended for different files.
# Could replace this with extraction from GeoJSON?
colnameselect <- ""

# Read the GeoJSON file
geojson <- st_read("input.geojson")

# Simplify the file to a 100m resolution
shp_simpl <- st_simplify(geojson, 
                         preserveTopology = FALSE, 
                         dTolerance = 100)


geojson_new <- sf_geojson(shp_simpl, atomise = T)

geojson_new_unlist <- lapply(geojson_new, unlist)

geojson_df <- do.call(rbind.data.frame, geojson_new_unlist)
names(geojson_df)[1] <- "GeoJSON"

shp_join <- shp_simpl %>% select(all_of(colnameselect))
shp_join <- st_drop_geometry(shp_join)

join_list <- cbind(shp_join,geojson_df)

openxlsx::write.xlsx(join_list, "test_Geojson.xls")

This seems to do the job! Thanks to everyone for their help - lots of different ideas all helped me sort it out. Particularly the unlisting of the GeoJSON