CDC Malaria Mapping Project

This project aims to develop an Web-based GIS for the Center for Disease Control and Prevention (CDC) to manage and visualize malaria risk information worldwide. The system is targeted for use by both general public (travelers) and medical professionals. Usability, scalability and performance are the primary concerns with the existing CDC malaria mapping application. Our plan is to redesign the application to achieve a high performance system with an interactive interface while providing a better user experience. The varied nature and scope of the audience makes it imperative to present malaria risk information intelligently, and in a manner that is easily understood.

Deliverables

By August 3, 2009: Malaria map with updated databases and web-based administrator interface.

By September 30, 2009: Malaria map using freeware technologies, capable of rapid updates and dissemination of malaria information. Updated map to be made available to the public.

During October 1 – December 31, 2009: Improved technical implementations and deployments based on performance testing and user feedbacks.

Meetings

  • Meeting May 28 - Agreed on SOW and timeline listed therein

Task List

Environment Setup

  • Setup development environment (Anand)
    • Create a VM
    • Install PostGres database
    • Install PostGIS
    • Install Geoserver, Apache with PhP

Internal Deadline: Mid June

Database

  • Create the Postgres database and the corresponding matadata for allowing PostGIS function. (Anand)
  • Find the spatial data for provinces associated by either the FIPS or ISO31662 code. (Wenwu)
  • Find a list of cities (size >5000) with lat/long information. (Wenwu)
    • Enough information should be included to associate them with the province (FIPS/ISO31662)
    • If we are unable to associate it with province data directly in DB, we might have to take an indirect approach. This will entail performing spatial query on the data set to find that information.
  • Check the datasets provided by CDC for missing FIPS and ISO31662 code and design solutions to handle it. (Wenwu and Anand)
  • Find a spatial dataset that contains landmarks (Gazetteers). *Optional* (Wenwu)
    • As with city dataset we have to associate each entry in this dataset with the province it is located in.
    • Katherine will provide us with around 100 landmarks in late July.

Internal Deadline: Mid June

Administrative Web Interface

  • Design and develop a easy to use web interface that will allow our partners at CDC Malaria division to directly update the data. (Eric)
    • The access to the interface is controlled with some authentication module (login/password).
    • The interface should be able to import data from a excel file and put it into the postgres database.
      • Import will completely overwrite the data, will provide warnings if any columns have unexpexted values (e.g. risk field has values other than all, veries and none)
    • The interface should provide the portal users with ability to search countries and provinces (ideally dynamically updating search results as the user types).
    • The portal users should be able select a country or province and easily update the malaria risk information or prophylaxis recommendation.
      • User could choose to add a new province or country and provide data associated with it. The interface will clearly warn the user that though this newly added country/province will be searchable it will not be associated with any spatial data and will likely not have any city associated with it.
  • Export the country and province data as an excel file.
  • MAIN GOAL : Automating the Update Process for Malaria Map. In most cases malaria risk recommendation for a particular country or a region will be updated by CDC. CDC should be able to update recommendation. The map should get updated automatically when new information is entered by CDC. NOTE: Importing a new excel file to completely overwrite existing data, adding and removing new county/province will be rare use-cases.

Internal Deadline: End June

Database tables to be used

  • Spatial tables
    • country_spatial - Spatial data for country
    • province_spatial - Spatial data for province
    • populatedplaces_spatial - Spatial data for cities
    • fips_cntry and fips_admin are used as the foreign keys.
    • world - spatial dataset with province level information from GDAM (http://biogeo.berkeley.edu/gadm/). Not yet ready.
  • CDC Data Tables
    • country_risk_malaria - Country level malaria risk/prophylaxis information
    • province_risk_malaria - Province level malaria risk/prophylaxis information
    • Missing data are set to NULL
  • Search table
    • search_names (names, fips_cntry, fips_admin, type) list of places to search.
    • name is the only filed that is not null (enforced by db), also fips_cntry and type are also not null (in present instance)
    • Might need to add a trigger to repopulate this table if country_risk_malaria or province_risk_malaria tables are updated.
  • SQL statement used to create view for displaying risk

create view admin_risk_map as ( (select ps.fips_cntry,ps.fips_admin,prm.prov_mal_stat,ps.the_geom from province_spatial as ps inner join province_risk_malaria as prm on (ps.fips_admin=prm.fips_admin and ps.fips_cntry=prm.fips_cntry) ) UNION (select ps.fips_cntry,ps.fips_admin,crm.cntry_mal_stat,ps.the_geom from province_spatial as ps left outer join country_risk_malaria as crm on crm.fips_cntry=ps.fips_cntry where ps.fips_admin in (select ps.fips_admin from province_spatial as ps left outer join province_risk_malaria as prm on (ps.fips_admin=prm.fips_admin and ps.fips_cntry=prm.fips_cntry) except select ps.fips_admin from province_spatial as ps inner join province_risk_malaria as prm on (ps.fips_admin=prm.fips_admin and ps.fips_cntry=prm.fips_cntry))));

Wishlist of features based on current webpage

  • Check box to select country, province and city
  • Should be able to get a drop downlist that contains province and cities, not just countries
  • Update the Ajax list presented to the user without needing an enter (i.e. as they type both dropdown list as well as the static list are updated.)
  • Use the malaria table from CDC for searching.
  • Build style sheets with different colors for color blind people.
  • Under user string only allow A-Z,a-z (plus a few extras)
  • Be able to search city, province, country and city, country

Progress

  • Anand found the Country, Administrative domain, and city data from ArcGIS 9.3 (Jun 8)
    • Need to convert them into PostGIS format and match the datasets from CDC.
  • Eric has finished much of the code before leaving for vacation - progress listed below (Jul 16)
    • Code located - malariamap:/var/www
    • files
      • index.php - areas in the window are split between search map and output - search.php,map.php and output.php respectively
      • gets.php - these are the php functions which are available from ajax (look in php/all.php and php/search.php)
      • php/ol.php - this is where the openlayers information should be put
      • php/search.php - this is where the ajax / search functions should be put
    • MISSING CODE
      • in the gets.php file two functions need to be finished (editpentry and edicentry) the correct information is being passed to these functions, what needs to be added is the appropriate postgres sql commands to update the entry.
  • Eric has looked at the Windows IE bugs and found several new bugs from changed code FIXME (August 11)
    • new pg_header() function has critical errors and was commented out (needs fixing)
      • Anand: This is not a correct assessment. After further investigation, I figured out what Eric thought as a bug was a false positive. The actual bug was in the openlayers part of the code and I have hence been fixed.
    • index.php has a div tag with id 'body' which violates css
      • Agreed!
    • the style.css and color.css files are now empty and all css is inlined (bad coding practice, should revert back to css files)
      • Anand: It seems from this comment that I deleted Eric's original css. Just to be clear in his code these files were syill empty. When I did the redesign of the page, I just inlined the css due to time constraints (we had to show a draft on Aug 1). These have hence been moved to css.
    • openlayers has a bug and may need updating?
    • NOTE: I have not looked at the update feature since the pg_header() function contains login and needed to be commented out
      • Anand: Again pg_header() was not the problem

Technologies

  • GeoServer, Openlayers, PostGIS, Javascript, PHP, Web 2.0.

Attachments

  1. Country risk information (Excel file from CDC)
  2. Province risk information (Excel file from CDC)

Project Team: Ian Brooks, Anand Padmanabhan, Eric Shook, Wenwu Tang, Shaowen Wang