How we did it

Defining Data Records
We identified and defined the information to be captured, as well as the record classification for identifying unique records and tracking the latest revisions.We then created a spreadsheet in Google Docs, with the first sheet containing the required information structure.Our model can store records and the history of records. As such, a complete revision history is always stored in the system.
Data Records
Defining the Data Elements
A second sheet was created to hold a definition of the data elements stored in the data records sheet.  The following elements were defined: text, check lists, select lists, and data fields.  We also defined ‘system’ configuration fields for logical/automated fields.This data element definition sheet allows us to identify aspects of the data, such as, “is the field required?”,  “in what order should the data appear?”, or “does the information stored in the field come from an external source, and if so, which one?”.For example, the Countries field is:

  • required
  • checklist
  • and the values are stored and defined in a separate source (a controlled list)
Data Definition
Defining Controlled Lists
In a separate spreadsheet, we defined sheets to contain controlled lists for Countries (with geo-center point latitude and longitude, ISO and UN codes), CGIAR Centers, Partner Types, MTP Projects Lists (from CGMap with the CGIAR Center acronym), CGIAR Regions, as well as a sub-list of African Countries.
Controlled Lists
Providing a Simple Interface to Contribute Data
Building on the contribution mechanism provided by CGMap and taking advantage of Google Data APIs, we built an HTML form using the Google JavaScript libraries (JSAPI) to access the data record element definition (see Defining the Data Elements above). This rich and interactive form is presented to the contributor with all the required fields and values based on the data record element definition. We take advantage of the Google Data APIs’ mashup components to utilize the Yahoo Interface Libraries (for calendar date selectors) and our own custom scripts for checking the integrity of the information provided by the contributor.Saving the record submits the information, via the Google Spreadsheets Forms API, to the data record spreadsheet.
Putting the Research on the Map
Again, we took advantage of CGMap’s ability to navigate research projects via a map interface.A map is built using the Google Maps API, then we query the data records to obtain the latest records’ country field, which contains ISO Alpha-3 country codes.  The center point of each country with a record is plotted with a map marker by looking up the country’s coordinates in the Countries controlled list spreadsheet.When clicked, the map marker opens an embedded page (the project list bubble).  This page recognizes the currently open country map marker, and then queries the data records for those projects in that country.The project list bubble displays the project title with a link to display the data record as a fact sheet.  It also utilizes the Google Charts API to build a contextual map that provides a geographic visual overview of the project.The fact sheet queries the data records for all fields in the requested record.  A larger contextual map is then displayed, and based on the record elements definition, each field is displayed in the sheet.  When a record contains MTP project relationship data, the Google Search API is utilized to search CGMap to display relevant links to CGMap Project Factsheets.
Map Navigation


Take a peek at the ESA Research Maps!