You might have already seen many ways to convert GeoJson files to something BigQuery can understand. Let’s invent one more wheel!
E.g. this python script from Lak Lakshmanan:
Or using org2org tools to produce CSV file:
How to load geographic data like shapefiles into BigQuery
Now that BigQuery has support for GIS queries, let’s look at how we could load in geographic data into BigQuery. I’ll…
But not everyone knows how to run Python and installing Node.js might be a mess, so here is a simpler way to do it, using
jq command line tool.
Jq (https://stedolan.github.io/jq/) is single-executable command line tool for processing json files. Install it following instructions at https://stedolan.github.io/jq/download/.
You can convert geojson files to ndjson files using this script:
jq --compact-output '.features | (.geometry | tojson) as $g | . = .properties | .geometry |= $g' < input_file > output_file
You do need to replace
output_file with proper file names.
OK, if you want to know how it works, it does the following (node.js scripts mentioned above use the same logic):
.featuresselects individual feature in top level feature collection
.geometry | tojsonsaves the geometry field as quoted string to variable
- we then create object based on feature’s
- and finally update this object’s
.geometryfield with saved variable
jqoutput every feature on its own line, which is exactly what BigQuery needs.
Some limitations of this approach:
- this only supports GeoJson with default coordinate reference system (other CRS were deprecated by standard, but are still used sometimes)
- this only supports GeoJson that contains
FeatureCollectionobject, would not work if file has a single top level