Open Data, Bash, GIS, Oh My!

Due to a new commute I take for work, I found that Citi Bike has some open data!

I wrote the following script to take the Citi Bike data and transform it to a KML file. This script downloads data from Citi Bike, parses out the unique GPS coordinates, and transforms them in to a KML file.

Once you have this generated KML file, you can import this file to many pieces of GIS software.

The Script

#!/bin/sh
# Dependencies: csvtool, wget, unzip

set -e

wget https://s3.amazonaws.com/tripdata/201801-citibike-tripdata.csv.zip
unzip 201801-citibike-tripdata.csv.zip
cat 201801-citibike-tripdata.csv | csvtool -t ',' col 5,6,7 - | sort | uniq  > points.cvs
echo '<?xml version="1.0" encoding="utf-8"?><kml xmlns="http://www.opengis.net/kml/2.2"><Document>' > output.kml
cat points.cvs | while read LINE
do
	echo "$LINE" | sed 's/&/&amp;/' | awk -F ',' '{print "<Placemark><name>"$1"</name><Point><coordinates>"$3","$2",0</coordinates></Point></Placemark>"}' >> output.kml
done

wget https://s3.amazonaws.com/tripdata/JC-201801-citibike-tripdata.csv.zip
unzip JC-201801-citibike-tripdata.csv.zip
cat JC-201801-citibike-tripdata.csv | csvtool -t ',' col 5,6,7 - | sort | uniq  > points.cvs
cat points.cvs | while read LINE
do
	echo "$LINE" | sed 's/&/&amp;/' | awk -F ',' '{print "<Placemark><name>"$1"</name><Point><coordinates>"$3","$2",0</coordinates></Point></Placemark>"}' >> output.kml
done
echo '</Document></kml>' >> output.kml

This script could be modified easily enough to work with any CSV data to generate KML files.

Observations

• Despite this data being around Jersey City, NJ and New York City, NY, there were a couple points in Montréal.
• I also noted that NYC has a significantly denser network than NJ.