Data: Databases & APIs

Announcements

  • Twilio Alternatives: Grapevine in South Africa, Africa’s Talking in East Africa, possibly Vumi across most of Africa
  • This week, Databases & APIs
  • Next week, Spring Break
  • March 23, User Personas & User Stories due, overview of Frontline SMS
  • March 30, Vision Documents due, overview of Ushahidi
  • April 6, Overview of Freedom Fone

Design Notebooks

Let’s take a few minutes to share some of your Design Notebook entries from this week.

User Personas & User Stories

Let’s take a few minutes to discuss User Personas & User Stories and to create a couple of examples on Google Drive.

For more info on writing great User Personas, check out this blog post.

For more info on writing User Stories, check out this one.

Databases & APIs

Databases

Over the last couple of weeks, whenever we need to store data for our apps, we wrote it to or read it from comma separated flat text files. This week we’ll have a look at PostgreSQL, “the world’s most advanced open source database”.

PostgreSQL is an object-relational database, which means it can ‘relate’ information together in the form of tables. It used a query language called SQL, or Structured Query Language, to create, update and delete data in the database.

PostgreSQL is used by thousands of organizations, including Etsy, Moby Games, the National Weather Service, Greenpeace, Apple & Skype.

TCNJ PostgreSQL

As it turns out, you all have access to a PostgreSQL instance at database.tcnj.edu. Let’s log in and create our database.

For our purposes, we’ll need to do three things to database-enable our apps:

  1. Create the table for our data
  2. Insert records into our data table
  3. Retrieve data from our table

Phonebook

Let’s create a phone book we can use for some of the apps we developed earlier in the semester.

  • Log into the TCNJ postgreSQL database and create a new schema called phonebook.
  • Now open up the phonebook.sql file from the examples in a text editor and add in your own phone numbers.
  • Click the SQL icon in the menu along the top of phpPgAdmin and let’s feed it your updated phonebook.sql file.
  • Now upload hello_mom_psql.php onto your TCNJ web account and update your Twilio phone number message url to point to it.
  • Text your Twilio phone number from your or a friend’s cell phone. Try calling from a number that’s in your phonebook database, and from a number that is not.
  • What happened?

Let’s have a look at the code for hello_mom_psql.php.

Incident Database

Let’s update the incident reporting app we made last week to use a database instead of a file to store our data.

  • Log into the TCNJ postgreSQL database and create a new schema called incidents.
  • Now open up the incidents.sql file from the examples in a text editor and add in your own phone numbers.
  • Click the SQL icon in the menu along the top of phpPgAdmin and let’s feed it your updated incidents.sql file.
  • Now upload sms_map_psql.phpmap_incidents.php, and postgresql_map.html onto your TCNJ web account, and update your Twilio phone number message url to point to it.
  • Text your Twilio phone number from your or a friend’s cell phone, keying in a location name, and its latitude and longitude coordinates, in a comma-separated list like so: Trenton, 40.2162772, -74.7741221
  • Now, in your web browser, navigate to your postgresql_map.html map that you uploaded; e.g http://www.tcnj.edu/~username/imm470-03/databases/postgresql_map.html
  • What happened?

Let’s have a look at the code for sms_map_psql.phpmap_incidents.php, and postgresql_map.html

APIs

API stands for Application Programming Interface and refers to hooks some applications have that allow other applications to connect to them.

For example, Twitter is an application that allows users to share short status updates through a web or mobile interface. But what if you wanted to show your latest tweets on your blog? You’d need to have some way to allow your blog to communicate with Twitter, application to application, to filter, collect, and present just the appropriate tweets on your blog. Since Twitter has an API, this is a fairly straight-forward thing to do.

Let’s work through a couple of examples of why we might want to use APIs in our applications:

Geocoding

Geocoding for Feature Phones

For our geocoding API needs today, we’ll use the Google GeoCoder.

  • Upload the geocoder.php file to your TCNJ web account, and update your Twilio number’s messaging request url to point to it.
  • Using your cellphone, text your Twilio phone number with address information of some sort; a zip code, a city name, a street address, etc.
  • What happened?

Let’s have a look at the code for geocoder.php.

Geocoding for Smartphones

Smartphones come with GPS capabilities built in. The trick is to integrate those capabilities into a sensible user interface that suits your application.

For SMS-based incident reporting, an elegant solution would be to allow users to geocode SMS messages using their phone’s GPS feature:

Cordova SMS plugin + Cordova GPS plugin = Smartphone GPS-enabled SMS client

Alternatively, you could develop a native app that posts directly to an incident database though the internet protocol (wifi or 3g /4g).

A Weather App

Let’s create an SMS app that let’s the user send an SMS with their Zip Code, City, or other address info and receive back the current weather conditions for that location.

  • Upload current_weather.php to your TCNJ web account.
  • Update your Twilio phone number’s message request url to point to that file
  • Text your Twilio number an address of some sort, a zip Code, a city name, a street address, etc.
  • What happened?

To accomplish this, we needed a couple of things:

  1. First we needed to implement a geocoder pattern to convert the address info into latitude & longitude.
  2. Then, we needed to find an API we can use to get the current weather conditions for our location.

Since we’ve got a working geocoder pattern, we went ahead and reused that to geocode user input. And since we were looking for a solution that will work reasonably well internationally, we used Forecast.io for our weather data.

Let’s have a look at the code for current_weather.php.

Challenge

  • Break up into your teams.
  • Using sms_map_psql.phpmap_incidents.phppostgresql_map.html and current_weather.php as your starting place, recreate the incident mapping app to include geocoding for the location input data.
  • Whereas before you had to look up the lat, lon info and input it along with a name in a comma separated line before, now you should only have to type in a zip code, city name or address for the incident to appear on the map.

Assignment

Partner Organization Research

With your group, complete & deliver your second round of questions to your partner organization. I’d recommend you use Qualtrix for this second round, and strongly urge you to request your partner organization complete the survey by the end of the week so you can use the data in your User Personas and User Stories.

User Personas & User Stories

  • Meet with your group and identify one user type your partner organization might have per member of your group.
  • Each member should create a User Persona for one user type, along with three User Stories for this user type.
  • Each team member should create a new Design Notebook entry and title it “User Persona” and today’s date. The User Persona should fit on this one slide.
  • Each team member should create a second Design Notebook entry and title it “User Stories” and today’s date. Write out three User Stories for the User Type you documented in the previous slide. The User Stories should fit on this one slide.
  • At the beginning of our next class, we’ll break into our groups, combining your User Persona & User Stories Design Notebook entries into a new, group presentation. We’ll the go around the room and present them by team.

When researching your User Personas, make use of resources such as AudienceScapes and/or MSU’s globalEDGE. These resources can help find answers regarding languages spoken, religions practiced, education levels, as well as information patterns including device & internet usage.

Try to include the following data points in your User Personas:

  • age
  • family structure
  • type of home
  • location of home
  • size of municipality
  • languages spoken, and in what context (circles of trust)
  • education level
  • employment status
  • marital status
  • health concerns
  • access to information
  • device & internet usage
  • etc

*Assignments are due before class begins on Mondays. Be prepared to present your work in class for discussion.

17 thoughts on “Data: Databases & APIs”

Leave a Reply to Jared Entin Cancel reply

Your email address will not be published. Required fields are marked *