1

I can't seem to find the answer to this anywhere. How can I read in and convert a CSV file with geometry column containing long/lat to sf object. Here is the dput for the file

structure(list(date = c("2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04"),
               is_boarded = c("0", "0", "0", "0", "1", "0"), 
               fire = c("0", "0", "0", "0", "0", "0"), 
               homeless = c("0", "0", "1", "1", "0", "0"), 
               address = c("1231 N harding ave", "5942 S peoria st", "6440 S seeley ave", "6428 S paulina st", "9015 S houston ave", "10917 S buffalo ave"), 
               zip_code = c("60651", "60621", "60636", "60636", "60617", "60617"), 
               ward = c("37", "16", "16", "15", "10", "10"), 
               community_area = c("23", "68", "67", "67", "46", "52"), 
               geometry = c("c(-87.7251002085875, 41.903236038454)", "c(-87.6473828702868, 41.7862165473861)", "c(-87.6750561873273, 41.7767719172303)", "c(-87.6666233031588, 41.7770234233244)", "c(-87.5499059450373, 41.731640678147)", "c(-87.5437832254962, 41.6970145984798)"), 
               PRI_NEIGH = c("Humboldt Park", "Englewood", "Englewood", "Englewood", "South Chicago", "East Side")
               ),
          row.names = c(NA, 6L), 
          class = "data.frame"
         )
5
  • Why your geometry seems to contains a stringify vector if you read it from a CSV? I'm unable to get the same result for geometry using my own sample with a column with both lon, lat values for the same column
    – ThomasG77
    Mar 3 '20 at 16:58
  • @ThomasG77...the csv came in this format and I read it in with read.csv.
    – ify
    Mar 3 '20 at 17:57
  • @GISKid, I read the above solution but since my geometry column is slightly different, I am not sure how to translate to my particular case
    – ify
    Mar 3 '20 at 17:58
  • @ify I have added an answer that explains how to adapt it in this case, hope it helps!
    – GISHuman
    Mar 3 '20 at 18:19
3

Here's one way to approach it:

library(sf)
library(dplyr)

data=structure(list(date = c("2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04"),
               is_boarded = c("0", "0", "0", "0", "1", "0"), 
               fire = c("0", "0", "0", "0", "0", "0"), 
               homeless = c("0", "0", "1", "1", "0", "0"), 
               address = c("1231 N harding ave", "5942 S peoria st", "6440 S seeley ave", "6428 S paulina st", "9015 S houston ave", "10917 S buffalo ave"), 
               zip_code = c("60651", "60621", "60636", "60636", "60617", "60617"), 
               ward = c("37", "16", "16", "15", "10", "10"), 
               community_area = c("23", "68", "67", "67", "46", "52"), 
               geometry = c("c(-87.7251002085875, 41.903236038454)", "c(-87.6473828702868, 41.7862165473861)", "c(-87.6750561873273, 41.7767719172303)", "c(-87.6666233031588, 41.7770234233244)", "c(-87.5499059450373, 41.731640678147)", "c(-87.5437832254962, 41.6970145984798)"), 
               PRI_NEIGH = c("Humboldt Park", "Englewood", "Englewood", "Englewood", "South Chicago", "East Side")
),
row.names = c(NA, 6L), 
class = "data.frame"
)

data_sf = data %>%
  mutate(geom = gsub(geometry,pattern="(\\))|(\\()|c",replacement = ""))%>%
  tidyr::separate(geom,into=c("lat","lon"),sep=",")%>%
  st_as_sf(.,coords=c("lat","lon"),crs=4326)

The gsub is removing any matching parentheses. We use \\ to escape them in the regex code. The | indicates we want to match ( , ) and the c character. Then use separate to separate the remaining values in the geometry column by a comma into two new columns.

st_as_sf will take your lat and lon coordinates that we created in the previous step and convert to an sf object. I guessed on the crs.

2
  • This worked beautifully! Thanks a lot!
    – ify
    Mar 4 '20 at 0:18
  • @ify glad to hear it! If this solution worked for you, please accept it as the answer!
    – GISHuman
    Mar 4 '20 at 14:19
2

I have no idea why you seem to have a concatenate on the geometry string but, so be it. We need to remove it before operating on the data.frame.

First, here is your data.

library(sf)

xy.dat <- structure(list(date = c("2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04", "2017-08-04"),
               is_boarded = c("0", "0", "0", "0", "1", "0"), 
               fire = c("0", "0", "0", "0", "0", "0"), 
               homeless = c("0", "0", "1", "1", "0", "0"), 
               address = c("1231 N harding ave", "5942 S peoria st", "6440 S seeley ave", "6428 S paulina st", "9015 S houston ave", "10917 S buffalo ave"), 
               zip_code = c("60651", "60621", "60636", "60636", "60617", "60617"), 
               ward = c("37", "16", "16", "15", "10", "10"), 
               community_area = c("23", "68", "67", "67", "46", "52"), 
               geometry = c("c(-87.7251002085875, 41.903236038454)", "c(-87.6473828702868, 41.7862165473861)", "c(-87.6750561873273, 41.7767719172303)", "c(-87.6666233031588, 41.7770234233244)", "c(-87.5499059450373, 41.731640678147)", "c(-87.5437832254962, 41.6970145984798)"), 
               PRI_NEIGH = c("Humboldt Park", "Englewood", "Englewood", "Englewood", "South Chicago", "East Side")
               ),
          row.names = c(NA, 6L), 
          class = "data.frame"
         )

Now, we remove the c in the geometry column, add the MULTIPOINT geometry WKT and coerce to sfc object. Then we can use this sfc geometry to create an sf object from the original data.frame.

xy.dat$geometry <- gsub('\\c', '', xy.dat[,"geometry"]) 
  xy <- sf::st_as_sfc(paste0("MULTIPOINT",gsub("\\) \\(","\\),\\(",gsub(","," ",xy.dat$geometry))))

xy <- sf::st_sf(data.frame(xy.dat[,-9], geom=xy))
1
  • Thanks so much for your input! For some reason, Rstudio kept freezing when I ran this script but it seems like it could work
    – ify
    Mar 4 '20 at 0:22
1

Here's another way that takes advantage of your geometry column being all set to pass to st_point as an argument. Let's make a bunch of text st_point calls, and wrap them in a text st_sfc call and evaluate that as text and create a spatial data frame from that:

dsf = st_sf(d,
 geom=eval(
  parse(
   text=
     paste0("st_sfc(",paste0("st_point(",d$geometry,")",collapse=","),")")
        )
       )
      )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.