Data Modeling Overview

MobileMap requires ArcGIS Feature Services to define the data model for data access, data collection and editing. The first step in using MobileMap, therefore is to design and publish a data model. To do this, the general process is: Document > Build > Symbolize > Publish > Test.

Documenting Data Models

The WSG approach to documenting requirements is to use a spreadsheet format in which the layers, fields and allowed values are captured in two tabs of an Excel spreadsheet - data model and domains. The requirements gathering process starts typically starts with one or more templates that WSG can provide based on data models used for previous implementations, as well field protocols (cruise specs) and any existing field forms or data collection software user interface examples.

Data Model CSV format:

Notes:

• Layer

  • Layer name, no spaces

  • Recommended syntax

    • Use of plural nouns (e.g. Stands, Streams, Roads)

    • Where multiple words needed, use title case with underscores (e.g., Treatment_Areas)

    • For cruise use Stands, Plots, Trees, Logs

• Layer Type

  • Polygon, Line, Point, Table

• FieldName

  • Where multiple words needed, use title case with underscores (e.g., Plot_Type)

  • Keep it human readable by avoiding abbreviations, acroymns, slang (e.g. use Cruise_Type instead of CRZTYP)

• DataType

  • TEXT, LONG, SINGLE, DOUBLE

• MaxLength

  • Only for TEXT fields

  • Maximum number of characters that can be entered

• Precision

  • Only for SINGLE and DOUBLE fields

• AllowNullValues

  • Yes = field is not required, No = field is required

• Default Value

  • Enter the default value to be stored for the field when a new feature is created. If the field uses a Coded Value Domain, enter the code (not the description) here.

• Domain Name

  • Must match the domain name in the domains CSV file

Domains CSV format:

General Data Model Requirements

Feature Types

MobileMap supports the following ArcGIS Feature Classes:

• Points

• Lines

• Polygons (simple and multipart polygons)

It also supports Feature Tables. It does not support Annotations, Dimensions, Multipoints or Multipatches

Conventions

General Naming Conventions

While MobileMap is highly configurable and can accommodate custom field names, it is strongly encouraged to adopt the MobileMap conventions for table names, field names, alias names and data types. The general naming conventions are as follows:

• Table Names:

  • plural title case with underscores

  • Examples: Plots, Stands, Sale_Units, Access_Roads

• Field Names

  • Singular title case with underscores

  • Examples: Region, Count, Road_Length, Bark_Thickness

  • Exceptions: use Esri defaults as-is (e.g., editor tracking fields like ‘last_edited_date’, ‘created_date’)

• Aliases

  • Singular title case without underscores

  • Examples (match field names above): Region, Count, Road Length, Bark Thickness

  • Exceptions: retain underscores in aliases for ID fields that will typically not be entered by users (e.g., Plot_ID, Unique_ID)

Standard Field Names

The table below lists recommended field names with their data types and intended uses. While these fields are not all required and some may be substituted with custom or alternative names, these standards should be used unless there is a compelling reason to use an alternative name, and the alternative may need configuration and testing.

Esri Reserved Field Names

Certain words such as COUNT, COMMENT, and UPDATE are Esri reserved words and should not be used for identifiers such as Table, Feature Class and Column names.

For geodatabases there are a limited number (27) of reserved words. Attempting to use a reserved word as a table name will return an error that the table (or feature class) name is invalid. Using a reserved word as a column name will return an error indicating that the column name is invalid.

For ArcGIS Online and ArcGIS Enterprise there are a much higher number (287) of reserved words due to the inclusion of SQL database-reserved keywords. These SQL database-reserved keywords should not be used for feature service Field and Table names. Using these words may result in unexpected behavior during web-based workflows such as syncing or exporting. 

For a complete list of reserved words see the list at the bottom of this page, or consult the Esri documents where these are listed. Note that the ArcGIS Online and ArcGIS Enterprise is exhaustive - it includes all keywords that apply to File Geodatabases:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/overview/defining-fields-in-tables.htm

https://support.esri.com/en-us/knowledge-base/faq-what-are-the-reserved-words-for-esris-file-geodatab-000010906

Editor Tracking

MobileMap requires that ALL layers (Feature Classes and Feature Tables) have Editor Tracking enabled. MobileMap uses Editor Tracking fields (created_date, last_edited_date) to synchronize (upload/download) data to and from the Feature Service. We strongly recommend that you use the editor tracking fields that are auto-generated either by ArcMap or by the WSG data modeling python tool. Editor tracking should be enabled on each layer in the data model prior to publishing the Feature Service. If enabling Editor Tracking in ArcMap, right-click on the layer or table, select Manage, then click Enable Editor Tracking. This displays a message listing the fields that will be added to the dataset. More details can be found in Esri’s documentation here. We do not recommend enabling Editor Tracking after publishing your service to AGOL. While this is an option in AGOL, it will create Editor Tracking fields with different field names and will thus require non-default settings in MobileMap.

Cruise Data Model Requirements

There are a number of data model requirements that are specific to timber cruise:

Layers

For timber cruise, the general format format is:

• Stands - Required polygon Feature Layer, symbolized using Status field.

• Source_Stands - Optional polygon Feature Layer. Can be used as a lightweight polygon layer as a temporary destination when importing or copying stand polygons. Most or all fields should be optional (possible exceptions include Stand_ID).

• Plots - Required point Feature Layer, symbolized using Status field.

• Trees - Required business (attribute only, no spatial) table

  • For Coded Value Domains, the codes should only include capital letters and numbers. This is because MobileMap will convert the text entered (or selected when using popups) for Coded Value Domain fields to ALL CAPS then it will compare the modified value to the allowed codes. If the codes use lower case, they will not match.

    • Valid: C, CULL

    • Not valid: Cull, cull

• Logs - Optional, business (attribute only, no spatial) table. Used for recording defect, product or grade by log

  • For Coded Value Domains, the codes should only include capital letters and numbers. This is because MobileMap will convert the text entered (or selected when using popups) for Coded Value Domain fields to ALL CAPS then it will compare the modified value to the allowed codes. If the codes use lower case, they will not match.

    • Valid: C, CULL

    • Not valid: Cull, cull

Business Logic Data Model Requirements

Layers

MobileMap data models should include the following business logic tables:

• Rules - Required business (attribute only, no spatial) table. Used for recording business rules for in-field validation (e.g. if this then that). See https://woodlandsg.atlassian.net/wiki/spaces/MD/pages/307888249 for more information.

• Check_Rules - Optional business (attribute only, no spatial) table. Used for recording check cruise scoring business rules including the deduction applied when rule is violated. See for more information.

• Related_Domains - Required business (attribute only, no spatial) table. Used for recording attribute fields (that utilize Coded Value Domains) whose values are restricted based on the selection in another attribute field (that utilizes Coded Value Domains). See for more information.

Starting at MobileMap 4.4.14 the data model has been extended to include the following additional tables:

• Field_Specs - Optional business (attribute only, no spatial) table. Used for recording field specs (AKA cruise specs) for clients that intend to use a single data model but will need to apply different business rules or settings according to field spec.

• Settings- Optional business (attribute only, no spatial) table. Used for recording specific MobileMap settings that are associated with specific field specs.

Generic Data Model Requirements

Layers

MobileMap data models can optionally include additional feature layers. Examples include feature layers for recording roads, gates, culverts and other GIS data that may be needed for data collection, editing or even just reference in the field. Recommended layers include the following feature layers:

• Generic Points - Optional point Feature Layer used for recording any point features in the field (e.g., gates, culverts, hazards, etc.

• Generic Lines - Optional line Feature Layer used for recording any line features in the field (e.g., streams, harvest boundaries, proposed stand split boundaries, roads, trails, etc.

• Generic Polygons - Optional polygon Feature Layer used for recording any polygon features in the field (e.g., landings, riparian areas, proposed harvest polygons, etc.

Relationships

Each level of the timber cruise data model contains two important fields: Unique_ID and Parent_ID. Both fields store at Globally Unique Identifier (e.g., {94d4e046-bc4f-4dee-9ba9-f1bb48255b1a}) using a text field (not GUID data type, text length = 38). The Parent_ID field can be omitted from the Stands layer, but must be present on Plots, Trees and Logs. All 4 layers need the Unique_ID field.

Related Tables

In addition to the timber inventory data model convention for Stands, Plots, Trees, and logs described above, MobileMap also supports arbitrary relationships between spatial feature layers and business tables. Currently (as of MobileMap 4.4.6), any spatial feature layer in a service may have 1 related table.

To access data in related tables, open up the parent spatial feature and scroll to the bottom of the form to view that feature’s child records. A table at the bottom of the parent feature’s form will display existing child records, including buttons to create new child records and edit (including delete) existing records.

Open

In order to enable support for related tables, the Related Tables setting must be set (Settings > Data Model > Related Tables). The syntax for this setting is:

Relationship Classes

While Relationship Classes are not required for MobileMap (or InventoryManager) at this time, they are encouraged and help take advantage of aspect of the ArcGIS Platform, including the use of Esri software such as ArcMap, ArcGIS Pro, Dashboards, Collector, etc.

Note that Relationship Classes are not the same as Relates in ArcGIS. Relates are relationships that are defined in a map document (e.g., ArcGIS Pro *aprx). Relationship Classes, on the other hand, are database objects stored in the database schema itself. Relationship Classes, not Relates, should be created in the data model (File Geodatabase or Enterprise Geodatabase) to ensure that all ArcGIS software will have access to the appropriate relationships between layers and tables.

When using relationship classes, make sure to order the feature layers such that parent layers come before child layers. Thus, the layer order when publishing a service should be Stands, Plots, Trees, Logs. This will ensure that MobileMap will upload a new Plot before trying to upload a new Tree that is related to that plot. If MobileMap tried to do this in the opposite order, the upload may fail because the related record would not yet exist.

While relationship classes are certainly beneficial in most scenarios, the following should be taken into consideration:

• Relationship Classes are only supported in ArcGIS Pro for users at the Standard and Advanced levels. This means that Basic users of Pro cannot create Data Models (File Geodatabases) with Relationship Classes. Basic users cannot even create/edit/delete features in any table that participates in a Relationship Class. If you are a Basic user simply avoid using Relationship Classes.

• Using a Composite type, rather than Simple, would enforce the parent-child relationship. While this is not recommended, it can be used if needed. In that scenario, it would not be possible to create a Plot until the parent Stand. While this may be desired to help enforce data integrity, but requires that data sent from MobileMap be uploaded in the proper order (first Stands, then Plots, then Trees, etc.). MobileMap supports this at version 4.3.50 (released 8/17/2021) and above. In order for this to work as expected, the layers MUST be ordered sequentially, where Stands have a lower layer number then plots, plots have a lower order than trees, etc. (e.g., Stands = 0, Plots = 1, Trees = 6, Logs = 7). This is a change from the historical pattern of numbering Plots as layer 0 and Stands as layer 1.

• Even with Simple relationships, deleting a parent feature will remove the key from all of it’s children features. For example, if you have a Stand, then use the Plot Layout tool in MobileMap or InventoryManager you will have Parent_ID values in each plot that match the Unique_ID of the Stand. If you delete that Stand, it will not delete the Plots when using Simple relationships, but it will null out the Parent_ID field for those Plots. InventoryManager has a tool called Validate Plots that can be used to reassign these Plots to a new stand, if appropriate.

If relationship classes will be used, they can be created via the 'Create Relationship Class' Geoprocessing tool in ArcGIS Pro ( ). Note that when using a File Geodatabase (but not Enterprise), Relationship Classes can also be created from the Catalog Panel (right click on the database and select Manage > Create Relationship Class). The recommended Relationship Class settings are:

• Name: Stands_to_Plots
  Origin: Stands
  Destination: Plots
  Type: Simple (peer to peer)
  Labels: Plots, Stands
  Messages: None
  Cardinality: 1-many
  Add fields?: No
  Primary key: Unique_ID
  Foreign key: Parent_ID

• Name: Plots_to_Trees
  Origin: Plots
  Destination: Trees
  Type:Simple (peer to peer)
  Labels: Trees, Plots
  Messages: None
  Cardinality: 1-many
  Add fields?: No
  Primary key: Unique_ID
  Foreign key: Parent_ID

• Name: Trees_to_Logs
  Origin: Trees
  Destination: Logs
  Type: Simple (peer to peer)
  Labels: Logs, Trees
  Messages: None
  Cardinality: 1-many
  Add fields?: No
  Primary key: Unique_ID
  Foreign key: Parent_ID

Starting at MobileMap 4.4.14 the data model has been extended to include the following additional tables: Field_Specs and Settings. To support these new tables, the following Relationship Classes should be added.

• Name: Field_Specs_to_Rules
  Origin: Field_Specs
  Destination: Rules
  Type: Simple (peer to peer)
  Labels: Rules, Field_Specs
  Messages: None
  Cardinality: 1-many
  Add fields?: No
  Primary key: Unique_ID
  Foreign key: Parent_ID

• Name: Field_Specs_to_Check_Rules
  Origin: Field_Specs
  Destination: Check_Rules
  Type: Simple (peer to peer)
  Labels: Check_Rules, Field_Specs
  Messages: None
  Cardinality: 1-many
  Add fields?: No
  Primary key: Unique_ID
  Foreign key: Parent_ID

• Name: Field_Specs_to_Related_Domains
  Origin: Field_Specs
  Destination: Related_Domains
  Type: Simple (peer to peer)
  Labels: Related_Domains, Field_Specs
  Messages: None
  Cardinality: 1-many
  Add fields?: No
  Primary key: Unique_ID
  Foreign key: Parent_ID

• Name: Field_Specs_to_Settings
  Origin: Field_Specs
  Destination: Settings
  Type: Simple (peer to peer)
  Labels: Settings, Field_Specs
  Messages: None
  Cardinality: 1-many
  Add fields?: No
  Primary key: Unique_ID
  Foreign key: Parent_ID

Below is an example of creating the Trees to Logs Relationship Class using the Create Relationship Class Geoprocessing Tool in ArcGIS Pro when using ArcGIS Enterprise (note the table names with the database name).

Open

Below is an example of launching the Create Relationship Class Geoprocessing Tool in ArcGIS Pro from the Catalog Panel when developing the data model in a File Geodatabase. Note that it is not possible to launch this Geoprocessing Tool from Catalog when using ArcGIS Enterprise (SDE) geodatabase.

Templates

In order to facilitate the data modeling process for timber inventory, WSG has developed a number of templates that can be used as a starting point when data modeling.

Northeast template Excel file

Generating Data Models from TCruise Templates

InventoryManager supports tight integration with TCruise, including the ability to export and import to and from TCruise. To ensure compatibility with TCruise, we have developed a process to generate

1. Export an ‘WSG database’ SQLite file from TCruise

   1. Load a TCruise template (TCT)

      1. Workup toolbar

         1. Load TCT

         2. Select desired template

         3. File > Save As

            1. Save as TCD

   2. Stratify

      1. Select Strata

         1. Browse for files to add to the list

         2. Select relevant TCD file

   3. Stratify

      1. Batch CSV files Export for External Consumer

         1. SQL Lite db export

         2. OK

2. Open SQLite file using DB Browser or equivalent

3. Use SQL commands below to generate the following resources: Data Model CSV file, Domains CSV file and TCruise Export CSV file. For each of these items, the process is as follows

   1. Copy the SQL commands from the code sections listed below

   2. Run the SQL command

   3. Export the results as a CSV file

   4. Open CSV files in Excel and clean it up to

      1. Data model

         1. Remove unnecessary fields (e.g., TCruiseFieldName, LayerOrder, rowid).

         2. Verify all table names, field names, nullable status, data types, domains, etc.

            1. Field names - use Title_Case (underscores between words)

            2. Field aliases - use ‘=substitute(A2,“_”,” “)’ to change from Title_Case with underscores to Title Case

      2. Domains

         1. Update domain names to be both intuitive and use PascalCase (e.g., PlotSize) and ensure they are updated in the Data Model CSV file

         2. Ensure that all coded value domains have both codes and descriptions

            1. For coded value domains that are used in MobileMap Cruise Trees and Logs tables, text field codes should be in ALL CAPS. This is because MobileMap will convert the entered text (when not using popups) to ALL CAPS prior to validating against the Coded Value Domain. This speeds data entry by allowing users to type in lower case. For example, a Coded Value Domain for tree species in the Pacific Northwest might include the following values (code:desription): DF:Douglas fir,GF:grand fir,WH:western hemlock,… A user can type in “df” for the species and MobileMap will convert this to “DF” then compare with the codes and find a match, thus it will pass validation and store the correct value.

         3. Verify all codes, descriptions and range domains min and max values

      3. The result will be the same as what is described in sections above for Data Model and CSV files. Run the Python data modeling script to build the data model. Anticipate that initial runs of the Python script will identify issues with the data model and domain CSV files that need to be fixed.

4. SQL Code

   1. Data Model CSV file

   2.  

      -- SQL script runs on TCruise export SQLite file to produce data model for MobileMap -- run in DB Browser, then export results as CSV file -- after running data model and domain scripts, use the MBG Python toolbox to generate a FGDB -- It is likely that one or more errors will be generated, if so, manually fix the CSV files and re-run SELECT Layer, CASE LayerType WHEN "Lookup" THEN "Table" ELSE LayerType END as LayerType, CASE FieldName WHEN "TRACT" THEN "Stand_ID" WHEN "CRUISER" THEN "Cruiser_ID" WHEN "CRUISEDATE" THEN "Cruise_Date" WHEN "LANDAREA" THEN "Acres" WHEN "STPLOTSZ" THEN "Saw_Plot_Size" WHEN "STPLOTTYPE" THEN "Saw_Plot_Type" WHEN "PWPLOTSZ" THEN "Pulp_Plot_Size" WHEN "PWPLOTTYPE" THEN "Pulp_Plot_Type" WHEN "SMPLOTSZ" THEN "Submerch_Plot_Size" WHEN "RPPLOTSZ" THEN "Repro_Plot_Size" WHEN "PLOTNO" THEN "Plot_ID" WHEN "TREENO" THEN "Tree_ID" WHEN "COUNT" THEN "Tally" WHEN "SPC" THEN "Species" WHEN "HM" THEN "Height_to_Record_Top_Diameter" WHEN "HS" THEN "Saw_Stopper" WHEN "HP" THEN "Pulp_Stopper" WHEN "TM" THEN "Broken_Top_Diameter" WHEN "FC" THEN "Form_Class" WHEN "AGE" THEN "Age" WHEN "HT" THEN "Site_Index_Height" WHEN "PN" THEN "Product_Number" WHEN "RDG" THEN "Radial_Growth" WHEN "SBG" THEN "Single_Bark_Thickness" WHEN "RP" THEN "Reproduction" WHEN "SHT" THEN "Stump_Height" WHEN "SDM" THEN "Stump_Diameter" WHEN "RECTYPE" THEN "Cruise_Type" WHEN "GSHT" THEN "Grade_Stump_Height" WHEN "GBRKTOP" THEN "Grade_Broken_Top" WHEN "GSTOPTOP" THEN "Grade_Stopper" WHEN "GSTATUS" THEN "Grade_Status" ELSE FieldName -- just use incoming FieldName END AS FieldName, Alias, CASE DataType WHEN "INT" THEN "LONG" ELSE DataType END AS DataType, CASE (cast(MaxLength as text)) WHEN "0" THEN "" ELSE cast(MaxLength as text) END as MaxLength, CASE DataType WHEN "DOUBLE" THEN Precision ELSE "" END AS Precision, AllowNullValues, "" AS DefaultValue, CASE DomainName WHEN "SPC" THEN "species" WHEN "PNORI" THEN "product" WHEN "SBG" THEN "" ELSE lower(replace(DomainName,"_","")) END AS DomainName, FieldName as TCruiseFieldName, CASE Layer WHEN "Stands" THEN 1 WHEN "Plots" THEN 2 WHEN "Trees" THEN 3 END AS LayerOrder, ROWID FROM DataModel WHERE substr(FieldName,0,4) not in ('GRD','GLN','GDF') AND DbTable != "Header" AND DomainName NOT IN ('CharacterCase','DateFormat','CHARSET_AN') AND FieldName NOT IN ('LONGITUDE','LATITUDE','ELEVATION','FILENO','DELETED','ACTIVE','VISITED','PLOTID','MEASURESYS','RECTYPE','LOCATION','OWNER','SCONV','FCOPT','ICYRS','DPREC','HTBASE','HM2HT','MEASURETYPE','CRUISETYPE','PNORI','OP','PO','STTOP','PWTOP','TEMPLATEID','TEMPLATEPATH','DOCPATH','TCA','TCB') -- add in tree stand and plot status field s UNION SELECT "Stands" as Layer,"Polygon" as LayerType,"Status" as FieldName,"Status" as Alias,"TEXT" as DataType,10 as MaxLength,"" as Precision,"No" as AllowNullValues,"Planned" as DefaultValue,"status" as DomainName,"" as TCruiseFieldName,1 as LayerOrder,100 as rowid UNION SELECT "Plots" as Layer,"Point" as LayerType,"Status" as FieldName,"Status" as Alias,"TEXT" as DataType,10 as MaxLength,"" as Precision,"No" as AllowNullValues,"Planned" as DefaultValue,"status" as DomainName,"" as TCruiseFieldName,2 as LayerOrder,100 as rowid -- add in tree stand and plot notes field UNION SELECT "Stands" as Layer,"Polygon" as LayerType,"Notes" as FieldName,"Notes" as Alias,"TEXT" as DataType,1000 as MaxLength,"" as Precision,"No" as AllowNullValues,"" as DefaultValue,"" as DomainName,"" as TCruiseFieldName,1 as LayerOrder,102 as rowid UNION SELECT "Stands" as Layer,"Polygon" as LayerType,"Notes_Check" as FieldName,"Notes Check" as Alias,"TEXT" as DataType,1000 as MaxLength,"" as Precision,"No" as AllowNullValues,"" as DefaultValue,"" as DomainName,"" as TCruiseFieldName,1 as LayerOrder,103 as rowid UNION SELECT "Plots" as Layer,"Point" as LayerType,"Notes" as FieldName,"Notes" as Alias,"TEXT" as DataType,1000 as MaxLength,"" as Precision,"No" as AllowNullValues,"" as DefaultValue,"" as DomainName,"" as TCruiseFieldName,2 as LayerOrder,102 as rowid UNION SELECT "Plots" as Layer,"Point" as LayerType,"Notes_Check" as FieldName,"Notes Check" as Alias,"TEXT" as DataType,1000 as MaxLength,"" as Precision,"No" as AllowNullValues,"" as DefaultValue,"" as DomainName,"" as TCruiseFieldName,2 as LayerOrder,103 as rowid -- add in cruiser_id to plots, trees and logs UNION SELECT "Plots" as Layer,"Point" as LayerType,"Cruiser_ID" as FieldName,"Cruiser ID" as Alias,"TEXT" as DataType,100 as MaxLength,"" as Precision,"No" as AllowNullValues,"" as DefaultValue,"" as DomainName,"" as TCruiseFieldName,2 as LayerOrder,101 as rowid UNION SELECT "Trees" as Layer,"Table" as LayerType,"Cruiser_ID" as FieldName,"Cruiser ID" as Alias,"TEXT" as DataType,100 as MaxLength,"" as Precision,"No" as AllowNullValues,"" as DefaultValue,"" as DomainName,"" as TCruiseFieldName,3 as LayerOrder,101 as rowid UNION SELECT "Logs" as Layer,"Table" as LayerType,"Cruiser_ID" as FieldName,"Cruiser ID" as Alias,"TEXT" as DataType,100 as MaxLength,"" as Precision,"No" as AllowNullValues,"" as DefaultValue,"" as DomainName,"" as TCruiseFieldName,4 as LayerOrder,101 as rowid -- add in tree Cruise_Type field UNION SELECT "Trees" as Layer,"Table" as LayerType,"Cruise_Type" as FieldName,"Cruise Type" as Alias,"TEXT" as DataType,1 as MaxLength,"" as Precision,"No" as AllowNullValues,"S" as DefaultValue,"cruisetype" as DomainName,"" as TCruiseFieldName,3 as LayerOrder,99 as rowid -- use multiple UNIONs below to add on 4 Logs (Grade) field records UNION SELECT "Logs" as Layer, "Table" as LayerType, "Tree_ID" as FieldName, "Tree ID" as Alias, "LONG" as DataType, "" as MaxLength, "" as Precision, "No" as AllowNullValues, "" as DefaultValue, "positiveint" as DomainName, "" as TCruiseFieldName, 4 as LayerOrder, 1 as rowid UNION SELECT "Logs" as Layer, "Table" as LayerType, "Log_ID" as FieldName, "Log ID" as Alias, "LONG" as DataType, "" as MaxLength, "" as Precision, "No" as AllowNullValues, "" as DefaultValue, "positiveint" as DomainName, "" as TCruiseFieldName, 4 as LayerOrder, 2 as ROWID UNION SELECT "Logs" as Layer, "Table" as LayerType, "Length" as FieldName, "Length" as Alias, "LONG" as DataType, "" as MaxLength, "" as Precision, "No" as AllowNullValues, "positiveint" as DefaultValue, "" as DomainName, "" as TCruiseFieldName, 4 as LayerOrder, 3 as ROWID UNION SELECT "Logs" as Layer, "Table" as LayerType, "Grade" as FieldName, "Grade" as Alias, "LONG" as DataType, "" as MaxLength, "" as Precision, "No" as AllowNullValues, "" as DefaultValue, "grade" as DomainName, "" as TCruiseFieldName, 4 as LayerOrder, 4 as ROWID UNION SELECT "Logs" as Layer, "Table" as LayerType, "Cruise_Type" as FieldName,"Cruise Type" as Alias,"TEXT" as DataType,1 as MaxLength,"" as Precision,"No" as AllowNullValues,"S" as DefaultValue,"cruisetype" as DomainName,"" as TCruiseFieldName,4 as LayerOrder,5 as rowid -- sort by layer and field order ORDER BY LayerOrder, rowid

   3. Domains CSV file

      -- SQL script runs on TCruise export SQLite file to produce domains for -- MobileMap data model. Run in DB Browser, then export results as CSV file -- Currently assumes that grades from GROUP_INDEX = 0 -- will be used fo all species/products SELECT lower(replace(DomainName,"_","")) AS DomainName, CASE FieldType WHEN "INT" THEN "LONG" ELSE FieldType END AS FieldType, DomainType, Code, Description, CASE DomainType WHEN "Range" THEN Min ELSE "" END as Min, CASE DomainType WHEN "Range" THEN CASE FieldType WHEN "Long" THEN min(2147483647, Max) -- must not be more than 2^32 ELSE Max END ELSE "" END as Max from Domain where DomainName NOT IN('CharacterCase','DateFormat','CHARSET_AN','HT=HM','MeasureType','CruiseType','SpecialConvention','FormClassDef','HeightBase','IncCoreYears','DbhPrecision','RECTYPE') and DomainType not in('Lookup') union select * from (select "species" as DomainName, "LONG" as FieldType, "Coded" as DomainType, CAST(SPECIES_CODE as INT) as Code, SPECIES_NAME as Description, "" as Min, "" as Max from Species where SPECIES_NAME != "TBA" order by Code) UNION SELECT "grade" as DomainName, "LONG" as FieldType, "Coded" as DomainType, GRADE_INDEX as Code, replace(group_concat(distinct GRADE_NAME),',','|') AS Description, "" as Min, "" as Max from GradeList WHERE substr(GROUP_NAME,0,6) <> 'Group' GROUP BY GRADE_INDEX UNION SELECT "product" as DomainName, "LONG" as FieldType, "Coded" as DomainType, PRODUCT_INDEX as Code, replace(group_concat(distinct PRODUCT_NAME),',','|') AS Description, "" as Min, "" as Max --group_concat(GROUP_NAME || ":" ||PRODUCT_NAME,", ") AS GROUP_NAMES_TO_PRODUCTS from ProductList --WHERE GROUP_INDEX < 34 WHERE substr(GROUP_NAME,0,6) <> 'Group' GROUP BY PRODUCT_INDEX --add Status domain UNION SELECT "status" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "Planned" as Code,"Planned" as Description,"" as Min,"" as Max UNION SELECT "status" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "Active" as Code,"Active" as Description,"" as Min,"" as Max UNION SELECT "status" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "Cruised" as Code,"Cruised" as Description,"" as Min,"" as Max UNION SELECT "status" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "Checked" as Code,"Checked" as Description,"" as Min,"" as Max UNION SELECT "status" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "Archived" as Code,"Archived" as Description,"" as Min,"" as Max --add cruisetype UNION SELECT "cruisetype" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "S" as Code,"Standard" as Description,"" as Min,"" as Max UNION SELECT "cruisetype" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "C" as Code,"Check Cruise" as Description,"" as Min,"" as Max --add plotsize Domain UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "Tally" as Code,"Tally" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF5" as Code,"BAF 5" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF10" as Code,"BAF 10" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF15" as Code,"BAF 15" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF20" as Code,"BAF 20" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF25" as Code,"BAF 25" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF30" as Code,"BAF 30" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF35" as Code,"BAF 35" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "BAF40" as Code,"BAF 40" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX200" as Code,"Fixed 1/200" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX100" as Code,"Fixed 1/100" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX50" as Code,"Fixed 1/50" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX20" as Code,"Fixed 1/20" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX10" as Code,"Fixed 1/10" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX5" as Code,"Fixed 1/5" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX4" as Code,"Fixed 1/4" as Description,"" as Min,"" as Max UNION SELECT "plotsize" as DomainName, "TEXT" as FieldType, "Coded" as DomainType, "FIX3" as Code,"Fixed 1/3" as Description,"" as Min,"" as Max order by DomainType DESC,DomainName

   4. TCruise Export CSV file

   5.  

      -- SQL script runs on TCruise export SQLite file to produce ExportConfig.csv -- File to export from IM to TCruise -- run in DB Browser, then export results as CSV file SELECT Layer AS "table", row_number() OVER () AS "index", CASE FieldName --stands (and plots and trees) fields WHEN "TRACT" THEN "Stand_ID" WHEN "CRUISER" THEN "Cruiser_ID" WHEN "CRUISEDATE" THEN "Cruise_Date" WHEN "LANDAREA" THEN "Acres" WHEN "STPLOTSZ" THEN "Saw_Plot_Size" WHEN "STPLOTTYPE" THEN "Saw_Plot_Type" WHEN "PWPLOTSZ" THEN "Pulp_Plot_Size" WHEN "PWPLOTTYPE" THEN "Pulp_Plot_Type" WHEN "SMPLOTSZ" THEN "Submerch_Plot_Size" WHEN "RPPLOTSZ" THEN "Repro_Plot_Size" --plot (and tree) fields WHEN "PLOTNO" THEN "Plot_ID" --tree fields WHEN "TREENO" THEN "Tree_ID" WHEN "COUNT" THEN "Tally" WHEN "SPC" THEN "Species" WHEN "HM" THEN "Merch_Height" WHEN "HS" THEN "Saw_Stopper" WHEN "HP" THEN "Pulp_Stopper" WHEN "TM" THEN "Broken_Top_Diameter" WHEN "FC" THEN "Form_Class" WHEN "AGE" THEN "Age" WHEN "HT" THEN "Total_Height" WHEN "RDG" THEN "Radial_Growth" WHEN "SBG" THEN "Single_Bark_Thickness" WHEN "OP" THEN "OP" WHEN "PO" THEN "PO" WHEN "RP" THEN "Reproduction" WHEN "SHT" THEN "SHT" WHEN "SDM" THEN "SDM" WHEN "RECTYPE" THEN "Cruise_Type" WHEN "STTOP" THEN "STTOP" WHEN "PWTOP" THEN "PWTOP" WHEN "GSHT" THEN "GSHT" WHEN "GBRKTOP" THEN "GBRKTOP" WHEN "GSTOPTOP" THEN "GSTOPTOP" WHEN "GSTATUS" THEN "GSTATUS" WHEN "LONGITUDE" THEN "" WHEN "LATITUDE" THEN "" WHEN "ELEVATION" THEN "" WHEN "FILENO" THEN "" WHEN "DELETED" THEN "" WHEN "ACTIVE" THEN "" WHEN "VISITED" THEN "" WHEN "PLOTID" THEN "" WHEN "MEASURESYS" THEN "" WHEN "RECTYPE" THEN "" WHEN "LOCATION" THEN "" WHEN "OWNER" THEN "" WHEN "SCONV" THEN "" WHEN "FCOPT" THEN "" WHEN "ICYRS" THEN "" WHEN "DPREC" THEN "" WHEN "HTBASE" THEN "" WHEN "HM2HT" THEN "" WHEN "MEASURETYPE" THEN "" WHEN "CRUISETYPE" THEN "" ELSE FieldName -- just use incoming FieldName END as esriName, FieldName AS exportName, CASE DataType WHEN "TEXT" THEN "" ELSE CASE FieldName WHEN "FILENO" THEN "1" WHEN "CRUISETYPE" THEN "1" WHEN "ACTIVE" THEN "1" WHEN "VISITED" THEN "1" WHEN "STTOP" THEN "999" WHEN "PWTOP" THEN "3" ELSE "0" END END AS defaultValue, DataType as dataType FROM DataModel WHERE DbTable != "Header"

Build the Data Model

Once the data model has been documented using the table format described above, it can be built using a number of approaches. The simplest, but most time consuming, is to use ArcMap or ArcGIS Pro to manually create each feature class, each attribute field and the associated Range and Coded Value Domains. While this approach works, it can be quite time consuming and is prone to creation of unintentional errors.

To improve the efficiency and consistency of this data modelling process, WSG has developed a Python toolbox for ArcMap and ArcGIS Pro that automates this process by generating a File Geodatabase (FGDB) from a pair of CSV files (data model, domains). While this tool is provided as-is (no warranty or support), it is very useful in that it is fast, and has options to add in commonly used components, including:

• Fields: Unique_ID, Parent_ID, Latitude, Longitude, Elevation, GPS_Point_Count, Archived, Created, Edited, Created_Source, Edited_Source

• Layers: Generic Points, Generic Lines, Generic Polygons, Tracks, Rules, Check Rules, Related Domains

• Domains: Rules_Operators

• Relationship Classes: Stands_to_Plots, Plots_to_Trees, Trees_to_Logs

Data Model Validation

After the data model has been published (see ) it must be rigorously tested both in the office and in the field before using in production. While testing, remember that some business logic is implemented via the data model (available layers, available attribute fields, field order, required fields, default values, allowed values, etc.) while other business logic requires additional components including MobileMap settings, business rules and related domains. When validating data models it is recommended that multiple users complete multiple ‘data round trips’ where data are created and edited multiple times, by multiple users in multiple environments. For Inventory Management System users, this might look like the following:

1. Round Trip 1 Test

   1. User 1 uses InventoryManager to import a Stand boundary from a Shapefile, Source_Stands feature, or heads-up digitize a new polygon directly into the Stands layer. When creating the feature, user verifies that all required fields are actually required (can’t be null), that all dropdowns include the correct list of allowed values, that range domains enforce min/max values for relevant fields, etc.

   2. User 1 creates plots within the Stand created above and verifies required fields, allowed values, min/max values, etc.

   3. User 1 downloads Stands/Plots to MobileMap and verifies symbology and ability to search on relevant fields.

   4. User 1 simulates field data collection of Trees (and Logs where relevant) by opening at least 2 plots and collecting at least 5 trees per plot (and an appropriate number of logs, if relevant). When editing Plots and collecting Trees/Logs, user verifies required fields, allowed values, min/max values, etc.

   5. User 1 uploads data from MobileMap and verifies that there are no upload errors

   6. User 1 reviews data in InventoryManager to verify that Plot status and symbology are correct, that all Trees/Logs are present, that data values match what was entered in MobileMap

   7. User 1 uses InventoryManager to create 1 or more new trees, edit 1 or more existing trees and deletes 1 or more trees. If relevant, they do the same for Logs.

   8. User 1 uses the appropriate export workflow to export data as Excel, TCruise, FVS or other format and then uses their typical analysis workflow to verify results are as expected. The goal of this final step is to verify that test data collected in step 1d above are 100% valid and can support the entire data lifecycle through the final analysis step(s).

2. Round Trip 2 Test

   1. User 1 repeats steps 1a - 1b above for a new stand

   2. After downloading to MobileMap, user 1 makes one or more changes to Stand/Plots in InventoryManager and then re-downloads to verify that MobileMap receives the updates. Note that MobileMap will NOT download ‘deletes’

   3. User 1 repeats steps 1c - 1g above to verify that edits made after the initial MobileMap download doesn’t have any negative impact on the workflow

3. Multiple Users Test

   1. User 1 repeats steps 1a - 1b above for a new stand

   2. User 2 completes steps 1c - 1d above

   3. Both user 1 and 2 complete step 1f - 1g above to verify that multiple users interacting with the data doesn’t have any negative impact on the workflow

Related Articles:

ArcGIS Reserved Words List

The list below was created 8/14/2023 based on the following Esri documents: