Querying polygon data imported from Google BigQuery GIS within R

Question

I have a geography data type column that each row is a polygons (representing South Africa suburbs) which I have to extract from a BigQuery database into R and from there plot these in an R Shiny app (or use for other analysis).

Unfortunately, I keep running into this error:

Downloading 77,850 rows in 8 pages. Error in bq_parse_files(schema_path, page_paths, n = page_info$n_rows, :
Unknown type GEOGRAPHY

I have tried various options such as:

query <- "SELECT *, ST_ASTEXT(ST_GEOFROMTEXT(wkt)) AS Geometry FROM od_matrix.cartographic_partitions"
tb <- bq_project_query("PROJECT_HARM", query)
data <- bq_table_download(tb) %>% 
  sf::st_as_sf(wkt = "Geometry")

and

DBI::dbGetQuery(conn, "SELECT *, ST_ASTEXT(wkt) AS Geometry FROM `PROJECT_HARM-prod.geography_data` LIMIT 10")

I keep having the same errors.

Here are some of the sources, I have tried:

• https://github.com/r-dbi/bigrquery/pull/376
• https://github.com/r-dbi/bigrquery/issues/313
• Manipulating polygon data imported from Google BigQuery GIS within R

Answer 1

Looks like R package does not understand and fails when it sees Geography type.

SELECT *, ST_ASTEXT(wkt) AS Geometry ... is good path, but you also need to exclude all Geography columns from query, use

SELECT * EXCEPT(wkt), ST_ASTEXT(wkt) AS Geometry ...

Make sure to list all Geography typed columns in EXCEPT clause.

You might also consider if using ST_AsGeoJSON function is more appropriate for your use case than ST_AsText. BigQuery uses spherical geography type, and the WKT it produces uses great-circle edges. ST_AsGeoJSON converts the shape to planar map, which might be more appropriate depending on application.