himalayandatabase
from a Visual FoxPro GUI to json api with datasette
The Himalayan Database is a compilation of records for all expeditions that have climbed in the Nepalese Himalaya. The database is based on the expedition archives of Elizabeth Hawley, a longtime journalist based in Kathmandu, and it is supplemented by information gathered from books, alpine journals and correspondence with Himalayan climbers.
The Himalayan Database is a Microsoft Visual Foxpro 9 program.
DISCLAIMER:
the license doesn't allow to redistribute the data. so run the following tutorial only locally for your personal use.
how to convert the data and publish with datasette
download the macos package
$ wget http://himalayandatabase.com/downloads/Himalayan%20Database%20Wine.zip
$ unzip Himalayan\ Database\ Wine.zip
$ cd Himalayan\ Database\ Wine
locate the DBF files
$ find . -name "*.DBF"
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/exped.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/filters.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/members.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/peaks.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/refer.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/SETUP.DBF
identify the DBF version
sf is siegfried
$ sf "./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/exped.DBF"
---
siegfried : 1.7.6
scandate : 2018-01-06T11:28:15+01:00
signature : default.sig
created : 2017-10-03T15:30:10+11:00
identifiers :
- name : 'pronom'
details : 'DROID_SignatureFile_V92.xml; container-signature-20170920.xml'
---
filename : './Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/exped.DBF'
filesize : 19662159
modified : 2017-12-03T22:12:04+01:00
errors :
matches :
- ns : 'pronom'
id : 'fmt/373'
format : 'FoxPro Database'
version : '2.x'
mime :
basis : 'extension match dbf; byte match at 0, 44'
warning :
convert from DBF to csv
Altough tika can be used to extract data from FoxPro Database, i got a cleaner result with dbfcsv
How to install:
$ go get -u github.com/gogap/godbf
$ git clone https://github.com/lancecarlson/dbfcsv.git
$ cd dbfcsv
Fix the import:
$ sed -i -s 's=code.google.com/p/go-dbf/godbf=github.com/gogap/godbf=' dbfcsv.go
Build
$ go build dbfcsv.go
$ cp dbfcsv ~/bin
$ dbfcsv -h
-d string
delimiter used to separate fields (default "|")
-h display headers
Go to HIMDATA directory:
$ cd "./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/"
Convert DBF to csv
$ parallel "dbfcsv -h {}.DBF > {}.csv" ::: exped members peaks refer
$ du -h *.csv
3.4M exped.csv
15M members.csv
84K peaks.csv
752K refer.csv
Convert csv to sqlite with csvs-to-sqlite (field separator is |)
$ pip3 install csvs-to-sqlite
$ csvs-to-sqlite -s "|" *.csv himalayandatabase.db
install and run datasette
$ pip3 install datasette
$ datasette himalayandatabase.db
Serve! files=('himalayandatabase.db',) on port 8001
[2018-01-07 12:51:40 +0100] [3461] [INFO] Goin' Fast @ http://127.0.0.1:8001
[2018-01-07 12:51:40 +0100] [3461] [INFO] Starting worker [3461]
Example queries:
— list of all expeditions with Renato Casarotto (json)
— peaks higher than 8000mt