rubbish geo
09/16/2020
rubbish-geo is a business intelligence backend I built as a freelance consulting project for SF-based trasher picker-upper startup Rubbish.
It's basically a serverless API over a PostGIS instance hosted on GCP. Litter pickups are written to a Firestore user database by the client, then pushed to the analytics database using a database listener. The records are cleaned, counted, and aligned to the street grid, creating a blockface-by-blockface view of street litter:
rubbish=#
SELECT (name, curb, rubbish_per_meter, num_runs)
FROM blockface_statistics
INNER JOIN centerlines
ON blockface_statistics.centerline_id=centerlines.id;
row
--------------------------------------------------------------------------------
("Polk Street b/w Green Street and Union Street",left,0.22538216309912568,1)
("Polk Street b/w Green Street and Union Street",right,0.2863445870030898,1)
("Polk Street b/w Green Street and Bonita Street",right,0.44559654370508806,1)
("Polk Street b/w Green Street and Bonita Street",left,0.9923351948727908,1)
("Polk Street b/w Vallejo Street and Bonita Street",left,1.205936820807271,1)
("Polk Street b/w Vallejo Street and Bonita Street",right,1.003636792752386,1)
("Polk Street b/w Vallejo Street and Broadway",left,0.77333135587109,1)
("Polk Street b/w Vallejo Street and Broadway",right,1.0993923061952355,1)
Authenticated clients then make GET requests to Cloud Function endpoints to report data as needed.