Shell script to convert CivicData.com JSON to LIVES compliant CSV

lives.sh

##!/bin/bash

URL_BASE="http://www.civicdata.com/api/action/datastore_search_sql?sql=SELECT%20*%20from%20%22"
BUSINESSES_SQL="c8a749e9-dee3-4259-bdb4-7c3e45e0867e"
INSPECTIONS_SQL="19426f05-1814-4447-b63b-23c33b3706a7"
VIOLATIONS_SQL="f230b8d9-5605-422c-a2eb-dac203f62edb"
URL_SUFFIX="%22"

# inspections.csv
echo "business_id,score,date,description,type" > data/inspections.csv
curl -s $URL_BASE$INSPECTIONS_SQL$URL_SUFFIX | jq .result.records[] \
| jq '[.business_id, .score, .date, .description, .type]' \
| jq @csv \
| sed 's/\\//g;s/^.//;s/.$//' \
>> data/inspections.csv

# violations.csv
echo "business_id,date,code,description" > data/violations.csv
curl -s $URL_BASE$VIOLATIONS_SQL$URL_SUFFIX | jq .result.records[] \
| jq '[.business_id, .date, .code, .description]' \
| jq @csv \
| sed 's/\\//g;s/^.//;s/.$//' \
>> data/violations.csv

# businesses.csv
echo "business_id,name,address,city,state,postal_code,phone_number,latitude,longitude" > data/businesses.csv
curl -s $URL_BASE$BUSINESSES_SQL$URL_SUFFIX | jq .result.records[] \
| jq '[.business_id, .name, .address, .city, .state, .postal_code, .phone_number, .LAT, .LON]' \
| jq @csv \
| sed 's/\\//g;s/^.//;s/.$//' \
 >> data/businesses.csv
 
# feed_info.csv
echo "feed_date,feed_version,municipality_name,municipality_url,contact_email" > data/feed_info.csv
echo "\"%date%\",\"1.0.0\",\"City of Evanston\",\"http://www.cityofevanston.org/\",\"healthscore@cityofevanston.org\"" | sed s/%date%/$(date +"%Y%m%d")/ >> data/feed_info.csv

# Combine & Compress
zip yelp_evanston_healthscores.zip data/businesses.csv data/inspections.csv data/violations.csv data/feed_info.csv > /dev/null

# Move file and clean up
mv /var/www/html/lives/yelp_evanston_healthscores.zip /var/www/html/lives/yelp_evanston_healthscores.zip.bkup
mv yelp_evanston_healthscores.zip /var/www/html/lives/
rm data/*

• To run the above, you'll need to install jq.
• Make the script executable - ~$ chmod +x lives.sh
• Run it: ~$ ./lives.sh
• Bask in glory.

If lat/lon is not found, include -1/-1 - this signals to Yelp that no coordinates for location.

Empty string for no score