Frontend developers who write SQL have traditionally been required to sequester their SQL queries to a backend server. The popular adage has more or less been 'don't run SQL directly from the frontend.'
For Splitgraph users, it turns out the old rules no longer apply. Thanks to the DDN HTTP API, the browser can work directly with raw SQL queries. In this post we demonstrate by building a client-side only dashboard with taxi ride data.
Whereas we previously focused on subway rides, cab rides are also a familiar sight to New Yorkers. Rendering taxi activity on a map of NYC may give us additional indicators into how the Covid recovery is going.
Taxi ride datasets are published at nyc.gov on a monthly basis. April is the most recent month available at the time of writing, so let's download yellow cab records for April, across 2020, 2021, and 2022. It looks something like this:
Note: as of May 2022 nyc.gov started publishing this data in Parquet format, but we need CSV. Thus please convert however you prefer.
A script is available. It
pip install pandas fastparquet
However you make your CSVs once they're ready, please upload them via the
$ sgr cloud upload $yourSplitgraphUser/nyctaxi yellow_tripdata_2020-04.csv yellow_tripdata_2021-04.csv yellow_tripdata_2022-04.csv
Once the CSVs have been ingested your repo should look like this:
Download and publish taxi data to Splitgraph: ✅
To populate the taxi zones with a 24 hour sample, let's roll up all taxi pickups and drop-offs that occurred during the given day.
Roll up taxi activity: ✅
The repo is available on GitHub.
Since we use
mapbox-gl for the interactive map, get yourself
a free API key at mapbox.com if you need one.
Changing the date and clicking 'load' will cause new data to be fetched and rendered on the app.
Clone and run the SPA: ✅
The DDN HTTP API offers a convenient way to move beyond the old rules of the road, and let frontend developers run queries from their end user's browser.
Thanks for reading and keep following the Splitgraph blog for more posts.