Narrative
Between Sep 9 2011 and October 24 2011 I familiarized myself with the project and with the data Cathy Maynard and MSU had provided earlier. During this time, Cathy received a spreadsheet from DPHHS listing all of the establishments in Montana that have licenses to serve food.
On October 17 the project partners met and talked about ideas for how GIS could help them. I showed them the 12agr001 map at this meeting. We decided to do a pilot project to map the locations and functions of all the businesses in Lake, Missoula, Ravalli, Flathead, and Sanders counties.
Between November 11 and November 14 I combined all the establishments from the various tables and layers into a single table, tried to identify duplicate records, and added fields to the table to track which tables each record came from. There was a meeting on November 14 and I showed them the 12agr002 map. We created a list of function codes that could be assigned to each establishment and agreed that the records for the 5-county pilot project would be divided between the participants. I would send them each a pair of spreadsheets to record the functions of each business with. I sent out the spreadsheets on November 18.
By November 28 I had all of the spreadsheets back and entered the data in the Functions table. Between then and December 1 I ran the establishment database through the BMSC address matching service to create a spatial layer, worked on mapping locations of the un-matched records, especially in Lake County, and made the maps of the function locations for the 5 Valleys meeting in Arlee. I attended the meeting on December 2. The maps for the meeting are the 12agr003 maps and the Powerpoint Nancy and I gave is the Arlee_food_mapping_presentation.ppt file in this folder.
We received a litte bit more data at the Arlee meeting and I entered it into the database. The partners met on December 15 and decided I would create a web application for the partners to use to input functions for the establishment database and that this would be available around the state for knowledgeable people to update. I finished this application on January 12. Nancy sent out login IDs for people on about the 20th.
On January 19 I worked out a way to let an intern work with Google Earth to specify locations for the establishments that failed their address-matching. I met with the intern on the 19th, but he decided not to do it. I wrote up instructions for another intern (her name was Kallie) and sent them to Nancy on February 1. I received 183 records from the intern between February 22 and 28, at which point she said she had completed her assigned 20 hours of work.
Kevin Moore sent me spreadsheets with between 150 and 200 establishments in them on March 5. On March 6, I combined the records into a single .csv file and brought them into a temporary SQL Server table. I sorted the Food_Establishments table and this new table on the establishment names and entered an existing establishment ID in the new table for every record I could find in the existing table. I made some corrections to the existing data where I thought the new data looked right and sent Kevin a spreadsheet showing where other data in his new table disagreed with the old data. For each new record in the new table, I assigned a new establishment ID. I merged the new table into the old, setting the status equal to 2 for all the records and one of the table columns to "Aero_20120305", except where all 6 columns were already full. Kevin's spreadsheet contained a semicolon-delimited list of function codes for each record. I wrote an ASP page to parse the individual function codes and add them to the functions table.
Kevin sent more spreadsheets on March 14 and April 2 and I added them to the database. Several records were updated via the web form between March 15 and April 5. On April 6 I ran all the new records through the ITSD geocoding service and added the results to the main SQL server table
On April 9 I prepared two tables containing the 260 priority and 283 secondary records for Janet Hess-Herbert to geocode with Google Earth. The priority records are all the records where status = 2 (Someone said they ARE engaged in local food commerce) that do not have good locations. The secondary records are the records that came from one of the original databases Cathy Maynard gave us that do not have good locations. The queries for these are:
SELECT ID_Num, Loc_name, Establishment, Address, City, ZIP FROM sde.FOOD_ESTABLISHMENTS WHERE (Status = 2) AND (Loc_name IS NULL OR Loc_name = 'mtzipcodes') ORDER BY City SELECT ID_Num, Loc_name, Establishment, Address, City, ZIP FROM sde.FOOD_ESTABLISHMENTS WHERE (Status = -1) AND (Loc_name IS NULL OR Loc_name = 'mtzipcodes') AND ( (NOT Table1 IS NULL AND NOT Table1 LIKE 'dphhs%') or (NOT Table2 IS NULL AND NOT Table2 LIKE 'dphhs%') or (NOT Table3 IS NULL AND NOT Table3 LIKE 'dphhs%') or (NOT Table4 IS NULL AND NOT Table4 LIKE 'dphhs%') or (NOT Table5 IS NULL AND NOT Table5 LIKE 'dphhs%') or (NOT Table6 IS NULL AND NOT Table6 LIKE 'dphhs%') ) ORDER BY City
Janet and Nate Condron from AERO worked out a method for loading this data into Google Fusion Tables and saving the address-matched locations as kml files. We had a lot of trouble getting Google Earth to actually save the latitude and longitude coordinates into the kml file instead of just the addresses, but I think I eventually got all her locations, finishing around April 30. She did NOT try to find any of the establishments on the air photo, she just worked to find better addresses for records that didn't get geocoded. Google geocoded a lot more addresses than the ESRI locators did.
In May 2012 Kevin sent a few more data updates, and he and Nancy put together a list of maps they wanted to show as examples to the mapping project partners on June 6. These are the 12agr0005 maps. At about this time I also loaded the database into an ArcGIS Server service and developed an on-line map of the database at arcgis.com to allow anyone to browse the database. The current location of this map is here or it may be found by going to http://www/arcgis.com/home and searching for "Montana food infrastructure".
In June Kevin provided a few more database updates and had someone use the on-line forms to update certain things, such as all the Hutterite Colonies. He had asked me to make sure we had good locations for all the colonies, and that all the colonies were in there. I spent quite a lot of time on this, using http://www.cedrontech.com/hutterite.aspx?idx=MT as a source.
In early July I made some database updates requested by Kevin and made some maps for the Great Falls meeting (13agr0001). I attended the Great Falls meeting on July 11.
In late July I made some revisions to the ArcGIS Online map
On September 18 I made database updates requested by Kevin, updated the spatial database, and wrote the datadump.asp page to create a (hopefully) printable representation of the database. They want to distribute hardcopy versions of the database for people to mark up and return with updates.