Montana Food Infrastructure File List
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.
Data Fields
Food_Establishments
- ID_Num – Unique ID number used to keep track of the establishments.
- Establishment – Name of the establishment
- Status – Code for what we know about the establishment’s participation in local commerce. The look-up table for these is the Food_Status table.
- Address, City, ZIP, and County
- Table1–Table6 – Indicates the source of the establishment information. If any of Table2-Table6 are filled out, this means we received information about the establishment from several sources and consolidated them into a single record. THERE COULD STILL BE DUPLICATE RECORDS IN THE TABLE THAT WE HAVE FAILED TO CONSOLIDATE.
- Edited_By – Code for the person who assigned a Status to the record
- Edited_Date – Date when the Status was edited
- Loc_name – Source of the coordinate. Some of the data sources came as shapefiles, so this could be the same as a source in one of the Table1-Table6 fields. If it has someone’s name, it means a person placed it by hand – question marks indicate some level of uncertainty. If it says Cadastral, someone matched it to a Cadastral parcel and placed it by hand. If it says ESRIMTStreets, MTStructures, or MTZipCodes, it means the ESRI Geocoder selected the location based on a hit in the indicated database using the Montana Geocoder Service at gisservice.mt.gov. If it contains the word “exact”, it means the location was automatically geocoded through Google. If it just says Google, someone probably hand-placed it based on a Google search of the address.
- Score, Match_Type, and Match_addr – are fields added by the ESRI Geocoder, indicating the strength of the match, the match type, and the address matched in the geocoder databases. These aren’t valid for locations that were edited later.
- Table_ID – The ID number from the separate tables we put the original records in. An attempt to have something to link us back to the original records, if necessary.
- Owner – mostly blank – a couple of the original tables had owner names for the establishments.
- Parent_ID_Num – was used when we were trying to identify duplicate records. This should usually be the same as the ID_Num. If it isn’t, there shouldn’t be a record in the table whose ID_Num equals this value – the duplicate should have been deleted.
- Latitude – Latitude of the establishment, from the Loc_name source.
- Longitude – Longitude of the establishment
- Loc_Class – Categorization of the location source.
Functions – Functions that have been assigned to establishments
- ID – Unique ID number for the function record
- ID_Num – Link to the ID_Num primary key in the Food_Establishments table
- Code – Link to the Code primary key in the Function_Codes table
- Edited_By and Edited_Date – ID of the person who entered the record, and date entered.
Food_Function_Locations – This query gives the location and establishment information of all the records in the Functions table. It is included here because the way the query is written in the SQL Server database doesn't work for Microsoft Access. The following query does work in Access.
SELECT Food_Establishments.Establishment, Food_Establishments.Address,
Food_Establishments.City, Food_Establishments.ZIP, Functions.Code AS Function_Code,
Function_Codes.Type_ AS Function_Name, Functions.ID AS Function_ID,
Food_Establishments.Status AS Explanation,
Food_Establishments.ID_Num AS Establishment_ID, Food_Establishments.County,
Food_Establishments.Latitude, Food_Establishments.Longitude
FROM ((Functions INNER JOIN Food_Establishments ON Functions.ID_Num = Food_Establishments.ID_Num)
INNER JOIN Function_Codes ON Functions.Code = Function_Codes.Code)
INNER JOIN Food_Status ON Food_Establishments.Status = Food_Status.StatusID
WHERE ((Not (Food_Establishments.Latitude) Is Null))