Montana Food Infrastructure File List and Project Narrative.
Gerry Daumiller
Web Files

- http://maps2.nris.mt.gov/food -- This folder has the web application for project partners to use to set the status and functions of establishments. The files are also in the Web_App folder in the 716 contract folder on the N: drive.
- \\mslhlnags01\geodatabases\NRIS\Food_Establishments.gdb -- This is a copy of the file geodatabase that contains the spatial representation of the data. Another copy is stored in the Data subfolder of the contracts folder, and the master database (which is not spatially enabled) is stored in SQL Server (see below). This copy of the file geodatabase supports the web map service and the ArcGIS Online mapping application for the database.
- \\mslhlnags01\geodatabases\NRIS\Montana_Food_Infrastructure.mxd -- This is the ArcGIS 9.3 map document that defines the web map service used by the ArcGIS Online mapping application.
- http://gisportal.msl.mt.gov/arcgis/rest/services/NRIS/Montana_Food_Infrastructure/MapServer -- This is the ArcGIS Server web map service used by the ArcGIS Online mapping application.
- http://www.arcgis.com/home/item.html?id=31b923c4cf944774ade671347aa06f79 -- This is the ArcGIS Online mapping application.
- http://nris.mt.gov/gis/food -- This folder has several files that were distributed to project members and shown during meetings. Everything here is saved somewhere else or is stuff that we don't need to save.

SQL Tables
The master tables for the database are in the DigitalAtlas database in SQL server on mslhlnblade08. In anticipation of the eventual shutdown of blade08, I copied the tables to the Food_Establishment.gdb geodatabase on May 6, 2015. I did not make a separate copy of the Food_Establishments table -- all of the information from it is in the Establishment_Locations layer.

- Food_Establishments -- List of all the establishments. The ID_Num field is the primary key for identifying an establishment. If two records are found to be duplicates of the same establishment, the Table1-Table6 entries need to be merged into one of the records and one of them should be deleted. The records for the establishment in the Functions table also need to be merged. The Status field tells what we know about whether it is engaged in local commerce. The values are explained in the Food_Status table. The Table1-Table6 fields list the database tables that the establishment appeared in.
- Functions -- crossreference list giving the IDs of establishments and all the functions they fulfill. This can be joined to the Food_Establishments table to create a layer showing the locations of any function.
- Food_Function_Locations -- This is the view that joins the functions table to the food_establishments table to create a table with the locations of all the functions. This list may be loaded into ArcMap and converted to an ArcGIS feature class.
- Food_Editors -- List of people who may use the web application.
- Food_Status -- explanation of the status codes in Food_Establishments
- Function_Codes -- explanation of the function codes in the Functions table

Contract Folder Files

- Data\Food_Establishments.gdb -- This file geodatabase contains the data layers. The Establishment_Locations layer is generated from the Food_Establishments table in SQL Server and the Function_Locations layer is generated from the SQL Server Food_Function_Locations view. The Function_Locations layer contains a separate feature for each function performed by each feature in the Establishment_Locations.
- Data\Create_Layers.mxd -- This ArcMap project has the SQL tables loaded as event layers, ready to be exported to the file geodatabase when the SQL tables have been updated.
- Data\Montana_Food_Infrastructure.mxd -- This is a copy of the ArcMap project from \\mslhlnags01 (see above) that defines the ArcGIS Server web map service used by the online map application.
- Maps -- This contains the maps produced for the project.
- Web_App -- This contains a copy of the web application for editing the establishment status and functions.
- Data\NRCS -- This contains all of the data we started out with, provided by Cathy Maynard of the NRCS.
- Data\Interim -- This contains various early versions of the database and results of the first geocoding effort.
- Data\Interim\Google Locations -- This was the working folder for transferring establishment locations specified by interns with Google Earth to the database. The instructions.html file in this folder explain what the files are and how this works.
- Data\Interim\Demo.mdb -- This personal geodatabase contains layers for the 12agr0002 maps showing population near grocery stores and convenience stores.
- Data\Interim\Pilot_GDB.mdb -- This is the geodatabase used to create the pilot project maps for the conference in Arlee.

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.