Converting and Uploading Shapefiles (.shp) into PAD

Shapefiles (.shp) are frequently used to define geographic boundaries for data visualization and analysis. Your organization may use these files to define boundaries such as State Senate districts, precincts, or other custom needs. If you plan to use these files in BigQuery or Looker Studio, you will need to convert them to a different format, GeoJSON, first.

Converting and uploading .shp files requires a three-step process. First, convert the .shp file to GeoJSON format. Next, the GeoJSON file is converted to the newline-delimited JSON format. Finally, upload the JSON file to BigQuery. Currently, it is not possible to upload GeoJSON files to BigQuery through the user interface. Therefore, they must be uploaded through the command line.

How do I get started converting .shp files?

The following code assumes you have the gcloud CLI, the GDAL CLI and the jq CLI tools installed, and have done the setup and authentication process for the gcloud CLI. Click on each to read more about how to install each (and if you have a Mac, we recommend homebrew for the GDAL CLI and jq CLI tools). We recommend running this code locally on your machine.  

Step 1: Convert to GeoJSON

Locate your .shp file folder, and ensure it is unzipped and contains all the necessary files. Copy the name and path of the file folder, and decide what your converted file will be called and where it will live. Make sure the converted file name has a .geojson extension at the end. Then, run the below code:

ogr2ogr -f GeoJSON -t_srs crs:84 <file path for the converted file> <file path for where your .shp folder lives>

For example, if you are a) sending the file to our downloads folder, b) wanted it to be called geojson.json and c) the original file path was ‘~/Downloads/Senate_Districts’, you would run:

ogr2ogr -f GeoJSON -t_srs crs:84 ~/Downloads/geojson.json ~/Downloads/Senate_Districts

Step 2: Convert your GeoJSON to newline-delimited JSON

Open up the GeoJSON file you just created and confirm everything looks right (e.g., not a ton of unexpected nulls). If something looks wrong, inspect the .shp file directory and confirm everything is included. From there, note the file path of your geojson file and decide on the file path for your new file. Make sure the new file still has the .geojson extension. Then, run the below code:

cat <file path of your converted GeoJSON file> | jq -c ".features[]" > <file path for where your newly created Newline-Delimited JSON file will live>

For example, if you wanted to take the geojson.json file created in our Downloads folder in the previous step and convert it to a file called ‘state_senate_jsonl.geojson’ in our Downloads folder, you  would run: 

cat Downloads/geojson.json | jq -c ".features[]" > Downloads/state_senate_jsonl.geojson

Step 3: Upload your Newline-Delimited JSON file to your BigQuery project

Create or locate the dataset name you want your file to be uploaded to, and locate your PAD project ID. Then, run the below code (note: we recommend typing this code block out and not copy/pasting, when copy/pasted this code sometimes throws a positional error):

bq load --project_id=<yourPADprojectIDhere> \

 --source_format=NEWLINE_DELIMITED_JSON \

 --json_extension=GEOJSON \

 --autodetect \

 <destination dataset.table name> \

<your newline-delimited JSON file path here>

For example, if you wanted to take the 'state_senate_jsonl.geojson’ file you created in our previous step in our Downloads folder and upload it to the ‘test’ dataset in the ‘test-555’ project in a table called ‘iamtesting’, you would run: 

bq load --project_id=test-555 \

 --source_format=NEWLINE_DELIMITED_JSON \

 --json_extension=GEOJSON \

 --autodetect \

 test.iamtesting \

 ~/Downloads/state_senate_jsonl.geojson

Using the data after it is uploaded:

Once you’ve uploaded your file to BigQuery successfully, you’re all set! You can query the data for analysis with some of Google’s built-in geography functions, or you can visualize it in Looker Studio or GeoViz (more info on that here). 

Have questions? Contact us at help@techallies.org!