[R-sig-Geo] Reading tables without geometry from gdb?

[obrl soil]

Hi Mike,

Here's a demo workflow that uses package sf as well as a standalone
install of GDAL (I use the OSGeo4W package version, but any install
that has the OpenFileGDB driver will do). Ogr2ogr can write to csv so
its easy enough to pull out the non-spatial tables that way.

I downloaded your data (fair warning to others, 140MB - not that you'd
know from the metadata!) and unzipped it as 'NYCTrees.gdb'. Now:

options(stringsAsFactors = FALSE)
library(sf) # for spatial
library(readr) # for csv
library(dplyr)  # for join

setwd('C:/DATA')

# somewhere to dump outputs
dir.create(file.path(getwd(), 'tree_tables'))

# whats in the gdb?
gdb_contents <- st_layers(dsn = file.path(getwd(), 'NYCTrees.gdb'),
do_count = TRUE)

# this is easier to read than the list above:
gdb_neat_deets <- data.frame('Name' = gdb_contents[['name']],
                             'Geomtype' = unlist(gdb_contents[['geomtype']]),
                             'features' = gdb_contents[['features']],
                             'fields' = gdb_contents[['fields']])

# so the non-spatial tables have geometry == NA, lets get their names
nyctrees_nonspatial <-
as.list(gdb_neat_deets[is.na(gdb_neat_deets$Geomtype), 'Name'])
# names attrib is useful here
names(nyctrees_nonspatial) <-
gdb_neat_deets[is.na(gdb_neat_deets$Geomtype), 'Name']

# pull all the non-spatial layers out to csv using GDAL:
lapply(seq_along(nyctrees_nonspatial), function(x) {
  system2('C:/OSGeo4W64/bin/ogr2ogr.exe',
          args = c(# output format
                   '-f', 'csv',
                   # overwrite
                   '-overwrite',
                   # destination file
                   file.path(getwd(),
                             'tree_tables',
                             paste0(names(nyctrees_nonspatial[x]), '.csv')),
                   # src file
                   file.path(getwd(), 'NYCTrees.gdb'), nyctrees_nonspatial[x]))
})

# this is p quick, but one of those files has 800k rows so...not that quick!

# are they there?
tree_csvs <- list.files(file.path(getwd(), 'tree_tables'),
                        pattern = '\\.csv$',
                        full.names = TRUE)

# awesome! lets read a small one in and try a spatial join
LandCover_Metrics_PROW_Borough <- read_csv(tree_csvs[7])

# this spatial layer appears to match
PROW_Borough_sf <- st_read(dsn   = file.path(getwd(), 'NYCTrees.gdb'),
                           layer = 'PROW_Borough')

# only 5 geometries, but they're massive multipolygons so this is how
you view attributes without crashing Rstudio:
View(st_set_geometry(PROW_Borough_sf, NULL))

# join on BoroName ought to work
test_join <- left_join(PROW_Borough_sf,
LandCover_Metrics_PROW_Borough, by = 'BoroName')

# seems legit. A (terrible) plot:
plot(select(test_join, Grass_P), border = NA)

et voila

cheers
@obrl_soil

On Sun, Feb 11, 2018 at 2:22 PM, Michael Treglia <mtreglia at gmail.com> wrote:
>
> Hi All,
>
> I have a file geodatabase with non-spatial tables that can be joined to
> other spatial objects. Is there a best/easiest way to import the
> non-spatial tables in R these days?
>
> I've seen some solutions here:
> https://gis.stackexchange.com/questions/184013/read-a-table-from-an-esri-file-geodatabase-gdb-using-r
> but figured I'd see if I'm missing anything within sf.
>
> If it helps, the dataset I'm looking at is here:
> https://data.cityofnewyork.us/Environment/NYC-Urban-Tree-Canopy-Assessment-Metrics-2010/hnxz-kkn5
>
> When I try sf::st_read, as follows, I get the subsequent error:
> >comdists_data <-
> sf::st_read(dsn=fgdb,table="LandCover_Metrics_NYC_Community_Districts_Version10C")
>
> Error in st_sf(x, ..., agr = agr, sf_column_name = sf_column_name) :
>   no simple features geometry column present.
>
> Thanks all - best regards,
> Mike T
>
>         [[alternative HTML version deleted]]
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo