440 items tagged “projects”
Posts about projects I have worked on.
2019
Generating a commit log for San Francisco’s official list of trees
San Francisco has a neat open data portal (as do an increasingly large number of cities these days). For a few years my favourite file on there has been Street Tree List, a list of all 190,000 trees in the city maintained by the Department of Public Works.
[... 1,051 words]I commissioned an oil painting of Barbra Streisand’s cloned dogs
Last year, Barbra Streisand cloned her dog, Sammie.
[... 517 words]sqlite-utils: a Python library and CLI tool for building SQLite databases
sqlite-utils is a combination Python library and command-line tool I’ve been building over the past six months which aims to make creating new SQLite databases as quick and easy as possible.
[... 1,237 words]db-to-sqlite (via) I just released version 0.2 of a tiny CLI utility I’ve been working on. It builds on top of SQLAlchemy and lets you connect to any SQLAlchemy-supported database and convert the data from it to a local SQLite database file. The new --all option will mirror all available tables (including foreign key relationships), or you can use --sql to save the results of custom SQL queries.
Datasette 0.27 (via) The latest release of Datasette introduces an option to output tables and SQL query results as newline-delimited JSON—plus a new “datasette plugins” command for listing available plugins.
Exploring search relevance algorithms with SQLite
SQLite isn’t just a fast, high quality embedded database: it also incorporates a powerful full-text search engine in the form of the FTS4 and FTS5 extensions. You’ve probably used these a bunch of times already: many iOS, Android and desktop applications use SQLite under-the-hood and use it to implement their built-in search.
[... 1,390 words]2018
Building smaller Python Docker images
Changes are afoot at Zeit Now, my preferred hosting provider for the past year (see previous posts). They have announced Now 2.0, an intriguing new approach to providing auto-scaling immutable deployments. It’s built on top of lambdas, and comes with a whole host of new constraints: code needs to fit into a 5MB bundle for example (though it looks like this restriction will soon be relaxed a little—update November 19th you can now bump this up to 50MB).
[... 1,872 words]The interesting ideas in Datasette
Datasette (previously) is my open source tool for exploring and publishing structured data. There are a lot of ideas embedded in Datasette. I realized that I haven’t put many of them into writing.
[... 2,857 words]Analyzing US Election Russian Facebook Ads
Two interesting data sources have emerged in the past few weeks concerning the Russian impact on the 2016 US elections.
[... 922 words]datasette-vega (via) I wrote a visualization plugin for Datasette that uses the excellent Vega “visualization grammar” library to provide bar, line and scatter charts configurable against any Datasette table or SQL query.
Datasette 0.23: CSV, SpatiaLite and more (via) The big new feature in 0.23 is CSV export: any Datasette table or query can now be exported as CSV, including the option to get all matching rows in one giant CSV file taking advantage of Python 3 async and Datasette’s efficient keyset pagination. Also in this release: improved support for SpatiaLite and various JSON API improvements including the ability to expand foreign key labels in JSON and CSV responses.
Datasette Facets
Datasette 0.22 is out with the most significant new feature I’ve added since the initial release: faceted browse.
[... 1,189 words]Datasette 0.21: New _shape=, new _size=, search within columns. Nothing earth-shattering here but it’s accumulated enough small improvements that it warranted a new release. You can now send ?_shape=array to get back a plain JSON array of results, ?_size=XXX|max to get back a specific number of rows from a table view and ?_search_COLUMN=text to run full-text search against a specific column.
Make Near Me (via) The natural evolution of owlsnearme.com—Make Near Me uses the Zeit Now API to allow anyone to deploy their own version of Owls Near Me for any species! I announced this on stage at Zeit Day SF 2018 as part of my talk on Datasette and Datasette Publish.
Exploring the UK Register of Members Interests with SQL and Datasette
Ever wondered which UK Members of Parliament get gifted the most helicopter rides? How about which MPs have been given Christmas hampers by the Sultan of Brunei? (David Cameron, William Hague and Michael Howard apparently). Here’s how to dig through the Register of Members Interests using SQL and Datasette.
[... 1,167 words]JSON Escape Text. I built a tiny tool for turning text into an escaped JSON string—I needed it to help create descriptions and canned SQL queries for adding to Datasette’s metadata.json files.
csvs-to-sqlite 0.8. I released a new version of my csvs-to-sqlite tool this morning with a bunch of handy new features. It can now rename columns and define their types, add the CSV filenames as an additional column, add create indexes on columns and parse dates and datetimes into SQLite-friendly ISO formatted values.
Datasette plugins, and building a clustered map visualization
Datasette now supports plugins!
[... 751 words]Datasette 0.15: sort by column (via) I’ve released the latest version of Datasette to PyPI. The key new feature is the ability to sort tables by column, using clickable column headers or directly via the new _sort= and _sort_desc= querystring parameters.
owlsnearme source code on GitHub. Here’s the source code for our new owlsnearme.com project. It’s a single-page React application that pulls all of its data from the iNaturalist API. We built it this weekend with the SuperbOwl kick-off as a hard deadline so it’s not the most beautiful React code, but it’s a nice demonstration of how React (and create-react-app in particular) can be used for rapid development.
Owls Near Me. Back in 2010 Natalie and I shipped owlsnearyou.com—a website for finding your nearest owls, using data from the sadly deceased WildlifeNearYou (RIP). To celebrate #SuperbOwl Sunday we rebuilt the same concept on top of the excellent iNaturalist API. Search for a place to see which owls have been spotted there, or click the magic button to geolocate your device and see which owls have been spotted in your nearby area!
Analyzing my Twitter followers with Datasette
I decided to do some ad-hoc analsis of my social network on Twitter this afternoon… and since everything is more fun if you bundle it up into a SQLite database and publish it to the internet I performed the analysis using Datasette.
[... 1,314 words]Datasette Publish: a web app for publishing CSV files as an online database
I’ve just released Datasette Publish, a web tool for turning one or more CSV files into an online database with a JSON API.
[... 863 words]ftfy—fix unicode that’s broken in various ways (via) I shipped a small web UI wrapper around the excellent Python FTFY library, which can take broken unicode strings and suggest a sequence of operations that can be applied to get back sensible text.
2017
Datasette 0.14: customization edition. I just released the latest version of Datasette with a strong theme of customization: Datasette now supports custom templates and CSS styling hooks, and the metadata format has been expanded to allow per-database and per-table source/license/description information. You can also now define named canned queries which will be packaged up with your data.
New in Datasette: filters, foreign keys and search
I’ve released Datasette 0.13 with a number of exciting new features (Datasette previously).
[... 1,143 words]gzthermal-web (via) I built a quick web application wrapping the gzthermal gzip visualization tool and deployed it to Zeit Now wrapped up in a Docker container. Give it a URL and it shows you a PNG visualization of how gzip encodes that page.
csvs-to-sqlite: Refactoring columns into separate lookup tables. I just shipped a new version of csvs-to-sqlite with the ability to extract specified columns into a separate SQLite lookup table by passing additional command-line arguments.
Datasettes · simonw/datasette. I’m collecting examples of datasette-powered APIs on the project wiki.
Datasette for Polar Bears. I found a fun dataset of Polar Bear ear tag tracking data put out by the USGS Alaska Science Center and deployed it using datasette in just a couple of minutes—here’s how I did it.