Integrating CKAN open data with CFapi

Hello everyone. In my last post I introduced CFapi (CFapi.riaforge.org), a dynamic API generator for Coldfusion, Railo and Lucee Server CFML engines, now in version 1.0.5.

Today I am going to show you how easy it is to integrate CKAN open data server with data coming from a CFapi generated API.

In this example, I will use a database table that contains the neighbourhoods of the city of Rio de Janeiro. The word for "Neighbourhood" in portuguese is "Bairro". So that is the name of our database table:

Bairro table:

It has only three fields: BAIRRO (the name of the neighbourhood), LATITUDE and LONGITUDE.

The goal here is to create an API exposing the Bairro table as CSV, and then creating a CKAN new dataset resource to feed it *in real time*.

Of course it is possible to save a CSV file and deploy it to CKAN server, but that approach would demand updates. By using an API entry point, on the other hand, the information will always be up to date, because the CSV is generated by request.

So lets begin by pointing our browser to CFapi main page (CFapi/index.cfm) and fill in the form like this:

 Then press the "Generate API" button. The following screen will show up:

Now we have to follow the CFapi instructions. First, lets click on the "download API" link and deploy our just created City API to our web server root folder, something like "c:\inetpub\wwwroot\City" if you use IIS or "c:\Program files\Apache Software Foundation\Apache2.2\htdocs\City" if you use Apache Server.

After that we will click on the link that calls our City API main page: http://localhost/city/index.cfm - That will show the City API welcome screen and also fill the memory caches:

Now lets switch back to CFapi page and click on the link that runs the CSV file generation:

http://localhost/city/api/v1/csv/neighbourhoods.cfm?token=8AD8E16D-BE6B-AEC8-958DA1D111013363&filter=&download=true&delimiter=,

Notice that the browser tries to download the generated CSV file:

That is not what we want, as CKAN will not understand a download request as data feed. So we have to change the URL parameter "download=true" to "download=false". The delimiter *MUST* be a comma (,) to work with CKAN.

http://localhost/city/api/v1/csv/neighbourhoods.cfm?token=8AD8E16D-BE6B-AEC8-958DA1D111013363&filter=&download=false&delimiter=,

The result is that now we receive the CSV information within the browser screen:

Fine! Now we have got what it takes to integrate data with CKAN Server. Now lets open a CKAN dataset edition page (you must be logged in CKAN) and click on "Edit" button:

CKAN Server dataset page

 Now lets click on "Add New Resource" button:

The "Add New Resource" page will show up. Choose "Link for an API" and fill in the form field "Resource" with our CFapi generated City API link, like below (don´t forget to specify CSV in the format field). Then click on the "Add" button.

http://yourServer/city/api/v1/csv/neighbourhoods.cfm?token=8AD8E16D-BE6B-AEC8-958DA1D111013363&filter=&download=false&delimiter=,

With this we have added a new resouce called "Neighbourhoods" to our CKAN dataset. To visualize the result, lets just click on its name:

CKAN will then show in a table display format, the contents of the Neighbourhoods dataset (whose source data is coming from our City API, created dynamically by CFapi) :

To make things more beautiful, lets click on the "Map" tab and put the value "0-1000" in the range options. The end result is all the Rio de Janeiro´s city neighbourhoods showing on a map:

This example shows the endless possibilities of CFapi instant API generation when used in conjunction with CKAN or other opendata systems. Some time ago it was a real pain to create and integrate  APIs to open data systems, now we can do it in less than ten minutes.

And... That´s it! Let me hear from you about your experience with CFapi. That is important so I keep improving its tools.

Thanks a lot.