New Mexico and Colorado Spanish Survey Data Set Documentation

Sample Applications

1. The "spandial.accdb" Database

The Spanish Dialects database in MS Access format was developed with the purpose of converting the project's legacy data to a contemporary format within an application modeled after, and reproducing as closely as possible, the work flow and layout of the original Alpha 4 database.

The decision of which application or software package to use was based on ease of use, manufacturer support, institutional support, scalability, and interoperability with other formats and applications. MS Access was ultimately selected as the sample application because of its wide adoption, customizable interface, and relative portability. Additionally, the static condition of the data set and its size scale more effectively to a desktop, rather than an enterprise, database system.

Tables

The primary tables (COUNTY, GRAMMAR, LEXICON, MASTER, RANGE3, RANGE5, and RESPONSE) were imported from their corresponding .DBF files within the original Alpha 4 data directory. All imports were reported error free and no manual changes were made to the underlying table or data definitions. However, the following changes are noted:

  1. The MASTER table field LONGDEC and LATDEC were populated using calculated values in the source database. The calculations can be executed in the sample application as a query but have not been incorporated in the MASTER table design. However, the original expressions have been documented in the MASTER table definition.

All other tables, indexes, etc. in the source data directory were determined to be outputs or other products of Alpha 4 queries, routines, macros, etc. The functionality of those routines has been duplicated within the sample application, so the data themselves were not imported.

Table Descriptions

  1. COUNTY:
    Names, two letter county and state abbreviations, and sector codes. Used to create a pick list for standardized data entry.
  2. GRAMMAR:
    Interviewees responses to grammatical survey items. Grouped by response.
  3. LEXICON:
    Interviewees reponses to lexical survey items. Grouped by response.
  4. MASTER:
    Demographic information about interviewees. Several fields have been removed from the source table to protect the privacy of the NMCOSS consultants.
  5. RANGE3:
    Used to create pick lists to standardize entry of ranged values for ENG/SP speaking ability, accent, literacy and income.
  6. RANGE5:
    Used to create pick lists to standardize entry of ranged values for ENG/SP language use and fluency.
  7. RESPONSE:
    The comprehensive record of interviewees reponses to individual survey items.
  8. Switchboard Items:
    Generated by MS Access switchboard creation tool. The application switchboard reproduces the layout and workflow of the original Alpha 4 "SPANDIAL" and "VERIFY" applications.

2. Queries

The queries included in the sample application are intended to reproduce the map table export functions of the source database, as well as to recreate various demographic summary tables noted within the source data directory.

With regard to workflow, the original Alpha 4 applications made use of two large database "sets," which were essentially joined table views. The functionality that was therefore enabled has been maintained by the creation of corresponding query views, "SPANDIAL" and "SPANGRAM," as described below.

Queries which are directly analogous to corresponding Alpha 4 tables have been named with all capital letters. Queries which reproduce the functionality of macros or Alpha 4 routines have been named in "camel-case."

Query Descriptions

  1. ASPIRAT:
    Phonemes subject to aspirated formation by respondents. Includes location data. Query results can be sorted or filtered within Access or exported for mapping/visualiztion.
  2. BASE_SET:
    The "MASTER" and "RESPONSE" table join which provides the underlying view for the 'SPANGRAM' and 'SPANDIAL' queries.
  3. calculateDecimal:
    In the event of new original data entry, this query reproduces the LONGDEC and LATDEC field calculations that were not migrated into the MASTER table. See note above.
  4. englishTerms:
    An ordered list of survey terms, both lexical and grammatical. The list output by this query is used to submit the "ENG" term parameter to the "EngTermLoc" query described below.
  5. engTermLoc:
    Accepts the "ENG" term parameter passed by "locationByEng" form. Outputs a list of interviewees Spanish reponses for the selected term, along with location data. Query results can be sorted or filtered within Access or exported for mapping/visualiztion.
  6. PALATAL:
    Phonemes subject to palatal formation by respondents. Includes location data. Query results can be sorted or filtered within Access or exported for mapping/visualiztion.
  7. PARAGOGIC:
    Phonemes subject to paragogic formation by respondents. Includes location data. Query results can be sorted or filtered within Access or exported for mapping/visualiztion.
  8. responseLocs:
    The complete listing of interviewees Spanish responses to English survey items, including location data. This query is called by the "engTermLoc" query and filtered by the "englishTerms" item passed by the "locationByEng" form.
  9. SAMPLES:
    Location data for each interviewee.
  10. SPANDIAL:
    An update of the source Alpha 4 "SPANDIAL" set or joined table view. Includes all responses and choice codes for lexical survey items.
  11. SPANGRAM:
    An update of the source Alpha 4 "SPANGRAM" set or joined table view. Includes all responses and choice codes for grammatical survey items.

3. Forms

Forms provide the user interface to the application and model the combined layout of the original Alpha 4 "SPANIDAL" and "VERIFY" applications.

To maintain the original work flow, forms should be accessed via the Switchboard.

Form Descriptions

  1. GRAMMAR:
    Interface for changing the grammatical database.
  2. LEXICON:
    Interface for changing the lexical database.
  3. locationByEng:
    Interface for retrieving Spanish responses and location data per English survey term.
  4. MASTER:
    Interface for adding or verifying MASTER data entry.
  5. SPANDIAL:
    Interface for viewing lexical survey responses.
  6. SPANGRAM:
    Interface for viewing grammatical survey responses.
  7. Switchboard:
    Generated by the MS Access Switchboard tool. This form is the primary interface to the application and opens by default when the application is launched.

4. Exporting Data for Mapping

Although the Quantum GIS desktop mapping application described below is pre-loaded with shapefiles based on the "MASTER" and "RESPONSE" tables, the underlying data has not been normalized or cleaned in any way. Therefore it may be preferable to export normalized data from the "spandial.accdb" database for import into the mapping application, using the following procedure.

  1. From the main switchboard, select "Export Data for Mapping." Alternatively, double click the "locationByEng" form to open from the object pane.
  2. Select a survey term from the drop down menu.
  3. Click on "View Results."
  4. The query result will open in data sheet view. This is a read-only view and data cannot be edited.
  5. Click on a cell in the table and use CTL-A to select all.
  6. Use CTL-C to copy the data sheet.
  7. Open MS Excel or other spreadsheet application.
  8. Right click the first cell in the spreadsheet and select "Paste" or use CTL-V to paste the data.
  9. Save the file in comma delimited (CSV) format. Be sure to select "CSV" from the "Save as type" drop down menu.
  10. Click through any warnings that might appear.
  11. Open the "nmcoss.qgs" application described below. Make sure that the "Add Delimited Text Layer" plugin has been activated as per the instructions below.
  12. Click on the "Add Delimited Text Layer" icon.
  13. Select the "Browse" button and navigate to the CSV file just created.
  14. For "Selected delimiters" make sure that "Comma" has been selected.
  15. The "Add Delimited Text Layer" will attempt to identify the relevant X,Y coordinate fields. Make changes as needed and hit "OK."
  16. From the next screen, select "WGS 84" as the coordinate reference system and hit "OK."
  17. The georeferenced layer will be added to the map. Follow the instructions below to categorize the relevant symbology.

2. The Web Mapping Application

This application is intended to reproduce the project's original use of MapInfo within a dynamic, platform indepedent web mapping tool. As with the sample database application, the decision of which software package to incorporate was based on ease of use, manufacturer support, institutional support, scalability, and interoperability with other formats and applications. MapServer was ultimately selected for the application because it is a freely available, open source tool with substantial documentation and a large user base. Additionally, MapServer integrates well with desktop applications, notably the open source mapping tool, QuantumGIS.

The backend of the application consists of two software scripts, "nmcoss.map" and "nmcoss.php." Please refer to the files themselves for script documentation.

1. Installing the NMCOSS Web Application

  1. Download and install MapServer from http://mapserver.org/download.html#download. For an MS Windows installation, select the "MS4W" package under "Binaries." This package includes the required Apache server and PHP libraries.
  2. Go to the root directory of the MapServer installation. Following a Windows installation, this location will typically be "C:\ms4w\".
  3. Copy the file "httpd_nmcoss.conf" to the "C:\ms4w\httpd.d\" directory.
  4. Copy the entire directory "nmcoss" to the "C:\ms4w\apps\" directory.
  5. Restart the Apache server.
  6. Point your internet browser to "http://localhost/nmcoss/nmcoss.php". This should load a blank map.

2. Querying the NMCOSS Web Application

  1. Select a term from the drop down list on the right hand side of the screen.
  2. Enter a number in the field labeled "Exclusion Limit." This will filter out Spanish term responses falling below the indicated number.
  3. Click "Submit."
  4. Color coded results will be displayed on the map. Additionally, each response will generate a corresponding term/color legend.
  5. A table of Spanish term counts will also be displayed. Use this table to refine results based on revised exclusion limits. Please note that question marks ("?") have been automatically filtered from each response set.

2. The Desktop Mapping Application

This application is intended to reproduce the project's original use of MapInfo within a dynamic, platform indepedent desktop mapping tool. As with the sample database application, the decision of which software package to incorporate was based on ease of use, manufacturer support, institutional support, scalability, and interoperability with other formats and applications. Quantum GIS (QGIS) was ultimately selected for the application because it is a freely available, open source tool with substantial documentation and a large user base.

To use the included QGIS map file, download and install the "Standalone Installer" for Windows from the Quantum GIS website: http://hub.qgis.org/projects/quantum-gis/wiki/Download. Once installed, double click the "nmcoss.qgs" file to open the application. Please note that all layers are on by default and can be turned off or back on again by checking and unchecking the box next to each layer's name. This makes it possible to query either the "master" survey layer or "response" survey layer independently without having to view points from both databases.

1. Enable the "Add Delimited Text Layer" Plugin - Supports Mapping of CSV Files

  1. From the "Plugins" menu, select "Manage Plugins..."
  2. Check the box next to "Add Delimited Text Layer."
  3. Restart the application as needed.

2. Querying the Application

  1. Select the layer to be queried. For this example, the "response" layer will be queried.
  2. Open the layer properties by double clicking the layer. Alternatively, right click the layer and select "Properties."
  3. Under the "General" tab, select "Query Builder."
  4. From the "Fields" window on the left hand side of the query builder, double click to select the field to be queried. For this example, the query field is "ENG."
  5. In the "Operators" section, click the equal sign ("=").
  6. Under the "Values" window, click 'All." This will list all of the unique values in the selected field.
  7. From the list of all values, double click the query term in order to add it to the "SQL where clause" window.
  8. Select OK.
  9. Keep the Properties window open and select the "Style" tab.
  10. Select "Categorized" from the first drop down menu.
  11. From the "Column" menu, select the field containing the values to be classified. For this example, "SPANISH" should be selected.
  12. Select a color ramp from the menu provided.
  13. Hit the "Classify" button.
  14. Inspect the result and change the color ramp or reclassify the terms as needed.
  15. Hit "OK" to close the Properties window and apply the selected ENG/SPANISH term filters and classification to the map.