Belfast Trees Database (Part One)

Being but men, we catalogue trees.

To get a csv list of around 38000 Belfast trees from the opendatani web site you can simulate clicking the download button with curl:

$ curl -s -G -L -A "Mozilla/5.0 (X11; Linux x86_64)" -o trees.csv \
      http://www.belfastcity.gov.uk/nmsruntime/saveasdialog.aspx?lID=14543&sID=2430

Alternatively you can use the data api, sending a query such as:

{'sql': 'SELECT * from "b501ba21-e26d-46d6-b854-26a95db78dd9"'}

as an encoded querystring parameter to the api endpoint. The returned data in this case is JSON so there needs to be further processing to convert to CSV (see the script below). The point of converting to CSV is so that the file can be used as a Foreign Data Wrapper in postgres; there are FDWs for JSON web apis but these need separate compilation, whereas the CSV data wrapper is builtin.

Download trees.csv

In the next post the csv file is set up as a foreign data wrapper so that it can be more easily queried.

get_belfast_trees_csv.py

Powered by Pelican and ZURB Foundation.