Search code examples
google-bigquerygeospatialgeojsonqgis

QGIS GeoJSON Newline Delimited format compatibility with BigQuery

I'm trying to use BigQuery UI to create a table from a GeoJSON file loaded in GCS.

enter image description here

And it looks like the file has been exported successfully from QGIS:

enter image description here

And yet when I go to create a table I still get these errors:

enter image description here

Error while reading data, error message: JSON table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more details. File: gs://path/world-countries-newlinedelimited.geojsonl.json

Error while reading data, error message: JSON processing encountered too many errors, giving up. Rows: 1; errors: 1; max bad: 0; error percent: 0

Error while reading data, error message: JSON parsing error in row starting at position 0: Nested arrays not allowed. File: gs://path/world-countries-newlinedelimited.geojsonl.json

Is there something I'm missing? And if not, how can I debug that the file meets BQ's criteria? I looked into this question:

GeoJSON conversion to GeoJSONL BigQuery load error

but I would like to do it either via WebUI or SQL, not bq command line.

Solution

  • Unfortunately, this has to be done in command line.

    BigQuery uses --json_extension=GEOJSON option to handle GeoJson correctly (it is different from regular new-line-delimited Json in how Geometry field is located and treated, all properties are nested inside properties element, etc). This option is not exposed in the UI, so you would need to drop to the command line.

    See these instructions for details https://cloud.google.com/bigquery/docs/geospatial-data#geojson-files

    it should be a simple

    bq load --source_format=NEWLINE_DELIMITED_JSON --json_extension=GEOJSON \
 --autodetect \
 DATASET.TABLE \
 FILE_PATH_OR_URI