1

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:

2
  • How do you have "polygon shapefiles" in a BigQuery database? What have shapefiles got to do with it? Do you mean you have a column in your table of the GEOGRAPHY class? Is that column called wkt? Can you screenshot something to show us what you can see?
    – Spacedman
    Apr 16, 2020 at 17:24
  • Hi @Spacedman, I am sorry that I wasn't clear enough. Yes, I mean that I have a table of the Geography data type which is called wit. I took a snapshot of it and attached it to the question I sent to this group.
    – Smart D
    Apr 16, 2020 at 18:49

1 Answer 1

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.

1
  • The ST_ASTEXT function worked. I will try to do the same for ST_ASGEOJSON function.
    – Smart D
    Apr 16, 2020 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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