Mapping food banks in Spain with CartoDB

We welcome Dani Latorre in this guest post to let him tell us about a little project in which he has used CartoDB as part of the development. Stay tuned for more stories like this and if you want just write and tell us your stories@cartodb.com

A couple of months ago I decided to look for information about food banks in Spain. After wasting a couple of hours searching I found only information at FESBAL website (Federación Española de Bancos de Alimentos). But my objective was to collect all the information about food banks whether they were of that federation religious organizations or totally self-managed organizations.

I don’t like charity recently I read a Eduardo Galeano quote: "Unlike solidarity which is horizontal and is exercised as equals charity is practiced top-down humiliates the recipient and never alteres one bit of power relations". Agreeing with this quote and looking that government solidarity is low at this moment it's more important that someone can have a plate of food even for charity.

So I thought about putting my two cents in this front creating a little project to map all food banks in Spain. CartoDB seemed the right tool for the job. And indeed it was! In less than 2 hours I had the raw data I had done the map and I finished the integration with the website developed with Sinatrarb.

Let me walk you through the process.

Getting the data

I started developing a web scraper for FESBAL website with Python (using Mechanize and BeautifulSoup) to generate a CSV file with the raw data to import to a CartoDB table.

Importing the data into CartoDB

##_NOT_FOUND_IMAGE_## https://i.imgur.com/Z4Vr4AY.png ##_NOT_FOUND_IMAGE_##

In the CartoDB dashboard I created a new table importing the CSV data file. Once imported I used CartoDB's georeference tool to automatically georeference using the food bank's addresses (I had to add some latitudes/longitudes manually for a few rare addresses).

##_NOT_FOUND_IMAGE_## https://i.imgur.com/ZFiibmt.png ##_NOT_FOUND_IMAGE_##

Styling my visualization

After that I created the visualization from that table and played with visualization wizards. Select map marker styles infowindows content map tiles…

Once I finished the visualization I embeded it on a little website created with sinatrarb using Foundation CSS for the layout.

Using CartoDB.js

The web is also integrated with CartoDB using SQL API to show all the food banks in a simple list and to allow visitors to add more food banks. In my opinion it's a powerful API if you have a minimal knowledge about SQL and to handle JSON responses.

In the list only georeferenced food banks are being shown. I'm not showing those who are not yet georeferenced; so I'm using this mechanism to moderate submitted content until it gets reviewed and georeferenced.

Even if you don't know much about Ruby the code is pretty self explanatory:

##_INIT_REPLACE_ME_PRE_##

sql = "SELECT * from bancosdealimentos WHERE the_geom is not NULL"
url = "https://danilat.cartodb.com/api/v2/sql?q=#{sql}&api_key=#{ENV['API_KEY']}"
response = RestClient.get URI.escape(url)
data = JSON.parse(response)
##_END_REPLACE_ME_PRE_## 

Adding new food banks is also quite evident with a minimum knowledge of SQL as shown in this code snippet:

##_INIT_REPLACE_ME_PRE_##

sql = "INSERT INTO bancosdealimentos (full_address  contact  type) VALUES ('#{address}'  '#{contact}'  '')"
url = "https://danilat.cartodb.com/api/v2/sql?api_key=#{ENV['API_KEY']}"
response = RestClient.post URI.escape(url)  :q => sql
##_END_REPLACE_ME_PRE_## 

##_NOT_FOUND_IMAGE_## https://i.imgur.com/goRd1UL.png ##_NOT_FOUND_IMAGE_##

The web can be seen in bancosdealimentos.herokuapp.com scraping and Sinatra application code are available for free use on my Github account.