simonw

Not sure what these are:

datasette ~/Library/Suggestions/*.db

Safari stuff:

datasette ~/Library/Safari/*.db

Your desktop background:

simonw

Running in Datasette at https://vice-police-shootings.now.sh/vice-bc7c892/ViceNews_FullOISData

Here's what I did:

First, I exported as CSV the data from https://docs.google.com/spreadsheets/d/1CaOQ7FUYsGFCHEqGzA2hlfj69sx3GE9GoJ40OcqI9KY/edit#gid=1271324584

Then I converted it to a SQLite database using https://github.com/simonw/csvs-to-sqlite like so:

csvs-to-sqlite ~/Downloads/ViceNews_FullOISData.csv /tmp/vice.db \

-c Fatal -c SubjectArmed -c SubjectRace -c SubjectGender -c OfficerRace \

simonw

select spatialite_version(), spatialite_target_cpu( ), proj4_version( ), geos_version( ), lwgeom_version( ), libxml2_version( ), HasIconv( ), HasMathSQL( ), HasGeoCallbacks( ), HasProj( ), HasGeos( ), HasGeosAdvanced( ), HasGeosTrunk( ), HasLwGeom( ), HasLibXML2( ), HasEpsg( ), HasFreeXL( ), HasGeoPackage( );

e.g. https://timezones-api.now.sh/timezones-4fbc08f?sql=select+spatialite_version%28%29%2C+spatialite_target_cpu%28+%29%2C+proj4_version%28+%29%2C+geos_version%28+%29%2C+lwgeom_version%28+%29%2C+libxml2_version%28+%29%2C+HasIconv%28+%29%2C+HasMathSQL%28+%29%2C+HasGeoCallbacks%28+%29%2C+HasProj%28+%29%2C+HasGeos%28+%29%2C+HasGeosAdvanced%28+%29%2C+HasGeosTrunk%28+%29%2C+HasLwGeom%28+%29%2C+HasLibXML2%28+%29%2C+HasEpsg%28+%29%2C+HasFreeXL%28+%29%2C+HasGeoPackage%28+%29%3B

simonw

How to compile and run a Pony program using Docker

Create a /tmp/helloworld directory and put the following in main.pony:

actor Main
  new create(env: Env) =>
    env.out.print("Hello, world!")

Now compile it like this (the Docker image will be downloaded if necessary):

simonw

How I got darkflow working on my OS X Laptop

Darkflow is a Python 3 tool that makes the Darknet open source neural networks available in Python using Tensorflow.

Here's how I got it working on my laptop:

cd /tmp
git clone https://github.com/thtrieu/darkflow
cd darkflow

virtualenv --python=python3 .venv

simonw

How to turn a list of JSON objects into a Datasette

This repository has a dataset of 184.879 crimes committed in Buenos Aires: https://github.com/ramadis/delitos-caba

Download the raw data like this:

wget 'https://github.com/ramadis/delitos-caba/releases/download/3.0/delitos.json'

Now use Pandas to load that into a dataframe:

simonw

Making a Datasette out of Freebase species data

I stumbled across an old export of Freebase data we used to build WildlifeNearYou:

https://github.com/devfort/wildlifenearyou/blob/master/freebase_data/data/organism_classification.tsv

It's a 6.89MB TSV file. Here's how I turned it into a Datasette instance at https://organisms.now.sh/

cd /tmp

wget https://raw.githubusercontent.com/devfort/wildlifenearyou/master/freebase_data/data/organism_classification.tsv

simonw

How I made a Who's On First subset database

Using whosonfirst-data-latest.db from https://dist.whosonfirst.org/sqlite/ - technique inspired by Paul Ford: https://twitter.com/ftrain/status/957833410017361921

If you fire up the sqlite3 CLI against an existing database you can and then mount a brand new database using attach database - then you can create tables in that new database and populate them using a select statement, extracting data from JSON columns using the json_extract() function.

Having populated the new table, I also create a full-text index against the name column.

Here's an example query against the resulting database, using a wildcard to implement prefix autocomplete: https://whosonfirst-metadata-only.now.sh/whosonfirst-metadata-only-c11ebe3/whosonfirst?_search=san+fra%2A

simonw

How I fixed a tricky merge conflict

I switched to another branch before the conflict.

git checkout -b fixbranch

Then I cherry-picked each commit that was failing to merge, one at a time.

For each of those cherry-picked commits I manually cleaned up any conflicts.

simonw

How I mada a Datasette out of a feather file

Fantastic data journalism by Christine Zhang at the LA Times: https://github.com/datadesk/homeless-arrests-analysis

First I grabbed the data - a zipped feather file:

wget https://github.com/datadesk/homeless-arrests-analysis/blob/master/arrests.zip?raw=true
mv arrests.zip\?raw\=true arrests.zip
unzip arrests.zip