Converting table columns to spatial objects

Question

I'm looking for the best and workable solution when it comes to converting raw data from db to sf object. I need to create POINTs of raw coordinates (of type float). I found one solution to do this where we create sf object from raw data like this:

lnd_point = st_point(c(0.1, 51.5)) 
lnd_geom = st_sfc(lnd_point, crs = 4326)
lnd_attrib = data.frame(
  name = 'London',
  temperature = 25,
  date = as.Date('2017-06-21')
)
lnd_sf = st_sf(lnd_attrib, geometry = lnd_geom)

Unfortunately, it doesn't apply to table columns. Here is my database table (named 'tableDB'):

Like in the example above, first I wanted to create 2 separate geom columns of POINT type (for lonCust/latCust and lonApp/latApp using st_point), then create list of points (using st_sfc) and finally create sf object (using st_sf) but it doesn't work in my case.

Generally speaking, I need to create POINT data type of lonCust/latCust and lonApp/latApp in 2 separate columns (2 geometry columns placed at the end of the data frame). First I want to convert them to POINTs but st_point doesn't work for columns, as I mentioned before. What I found in the book and on the internet is this:

newDF <- tableDB %>%
  st_as_sf(coords = c("lonCust","latCust"), crs = 4326)

After that, my 'newDF' became 'sf' and 'data.frame' object what is ok but I still need to make 2 geom columns, not one. Don't know how to add second and more geom columns at the end of df. Summing up, I have a few questions:

What's the best way to convert raw data from db into spatial objects (e.g. POINTs)?
How to add more geom columns to a data frame (like in my example, I need to have 2 geom POINT columns: for ..Cust and ..App)?
Is it good practice to subtract e.g. lat/lon columns from original df and create a new, separate sf object? If so then how can I pair/assign each spatial point with an attribute (like with ZLP or LP in my example above)?

I'm really interested in good practices in terms of converting raw data into spatial data - I want to write Shiny app and need to know where to start.

user_7547327 · Answer

An sf object can have two geometry columns, but only one will be 'active'. Example and explanation here: https://cran.r-project.org/web/packages/sf/vignettes/sf6.html

Starting from the dataframe that you posted in the image & naming it my_df:

library(dplyr)
library(sf)

my_sf <- my_df %>% 
  st_as_sf(coords = c('lonCust', 'latCust')) %>%
  st_set_crs(4326)

my_sf$geom2 <- my_df %>% st_as_sf(coords = c('lonApp', 'latApp')) %>%
  st_geometry()

Should work. An example with made up data below:


x <- data.frame(id = 1:10,
                lon = sample(1:10, size = 10, replace = T),
                lat = sample(1:10, size = 10, replace = T),
                lon2 = sample(1:10, size = 10, replace = T),
                lat2 = sample(1:10, size = 10, replace = T))

xsf <- st_as_sf(x, coords = c('lon', 'lat'), remove = F)

xsf$geometry2 <- st_as_sf(x, coords = c('lon2', 'lat2')) %>%
  st_geometry()

head(xsf)
#> Simple feature collection with 6 features and 5 fields
#> Active geometry column: geometry
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 1 ymin: 1 xmax: 8 ymax: 7
#> CRS:            NA
#>   id lon lat lon2 lat2    geometry    geometry2
#> 1  1   8   1    4    4 POINT (8 1)  POINT (4 4)
#> 2  2   8   6   10    8 POINT (8 6) POINT (10 8)
#> 3  3   7   7    2    6 POINT (7 7)  POINT (2 6)
#> 4  4   1   1    7    2 POINT (1 1)  POINT (7 2)
#> 5  5   5   3    3    9 POINT (5 3)  POINT (3 9)
#> 6  6   6   2    3    8 POINT (6 2)  POINT (3 8)

^{Created on 2020-12-29 by the reprex package (v0.3.0)}

Converting table columns to spatial objects

1 Answers