Loading geospatial data from GCS to BQ - JSON parsing error

Question

I am in the process of loading a jsonl file from Google Cloud Storage (GCS) into Big Query (BQ). When I load in data directly through BQ, I am not having any issues. However, I have moved on to larger datasets, so need to grab the data from GCS. I am getting the following error message:

<Error while reading data, error message: JSON parsing error in row starting at position 0: Flat value specified for record field. Field: geometry; Value: {"type": "Polygon", "coordinates": [[[-86.68803349853496, 35.99544552802683], [-86.68789410738025, 35.99547494329437], [-86.68790148084128, 35.9954734528859], [-86.6879283867707, 35.99546780557361], [-86.68802604649356, 35.99544717559614], [-86.68803349853496, 35.99544552802683]]]} File: gs://BUCKET/fld_haz_ar_nash.json>

Here is the first line of my jsonl file:

{"FID": 0, "DFIRM_ID": "47037C", "VERSION_ID": "1.1.1.0", "FLD_AR_ID": "47037C_1", "STUDY_TYP": "NP", "FLD_ZONE": "X", "ZONE_SUBTY": "AREA OF MINIMAL FLOOD HAZARD", "SFHA_TF": "F", "STATIC_BFE": -9999, "V_DATUM": " ", "DEPTH": -9999, "LEN_UNIT": " ", "VELOCITY": -9999, "VEL_UNIT": " ", "AR_REVERT": " ", "AR_SUBTRV": " ", "BFE_REVERT": -9999, "DEP_REVERT": -9999, "DUAL_ZONE": " ", "SOURCE_CIT": "47037C_STUDY1", "geometry": "{\"type\": \"Polygon\", \"coordinates\": [[[-86.68803349853496, 35.99544552802683], [-86.68789410738025, 35.99547494329437], [-86.68790148084128, 35.9954734528859], [-86.6879283867707, 35.99546780557361], [-86.68802604649356, 35.99544717559614], [-86.68803349853496, 35.99544552802683]]]}"}

Here is my input for geometry in BQ console:

Any suggestions on how to fix this?

Answer 1

I think your BigQuery schema should have just one line, name - geometry, type - Geography.

The value for geometry field is a string, note the value is quoted:

"geometry": "{\"type\": \"Polygon\", \"coordinates\": [[[...

Type and coordinates are parts of Geography value encoded as Geojson, they are not separate fields for BigQuery. That's what the error says: it expected the Record due to the schema, but saw flat (simple) Json value.