himalayandatabase - from dbf to json api

himalayandatabase.md

himalayandatabase

from a Visual FoxPro GUI to json api with datasette

http://himalayandatabase.com

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