MobileMap: Data Model / Data Modeling Process

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:

Layer

LayerType

FieldName

Alias

DataType

MaxLength

Precision

AllowNullValues

DefaultValue

DomainName

Stands

Polygon

Stand_ID

Stand ID

LONG

 

 

No

 

 

Stands

Polygon

State

State

TEXT

50

 

Yes

 

States

Stands

Polygon

Region

Region

TEXT

50

 

Yes

 

Regions

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:

DomainName

FieldType

DomainType

Code

Description

Min

Max

DBH

Short

Range

 

 

0

100

Regions

Text

 

NW

Northwest

 

 

Regions

Text

 

NC

North central

 

 

Regions

Text

 

NE

Northeast

 

 

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.

Field Name

Table

Data Type

Description

Field Name

Table

Data Type

Description

Plot_Size

Stands and/or Plots

Text (10 characters)

Stores the plot size (BAF or fixed area plot size) for use in limiting distance calculation and downstream use by compilers and 3rd party systems (TCruise, FVS, etc.). Requires domain that stores values using MobileMap-specific codes (e.g., BAF20 for BAF of 20, FIX10 for fixed area 1/10th acre plot). May have more than 1 field (e.g., Plot_Size AND Nested_Plot_Size). Alternative names: BAF, Nested_Plot_Size, Saw_Plot_Size, Pulp_Plot_Size, etc..

Status

Any spatial layer but especially for Stands and Plots

Text (10 characters)

Stores measurement status. Typically used to determine symbology. Often used for other logic like map filters, hosted feature layer views definition queries, export filters, compilation rules, etc. Common status values: Planned, Active, Cruised, Checked, Archived.

Latitude

All point layers

Double

Stores Y coordinate of point

Longitude

All point layers

Double

Stores X coordinate of point

Elevation

All point layers

Double

Stores elevation in preferred units

GPS_Point_Count

Point layers where collection may occur via averaged GPS

Integer (short)

Stores the number of individual GPS locations that were averaged to get the final coordinates

Photos

Any table where photos will be captured

Text, large number of characters (e.g., 1000 to support capture of multiple photos)

Stores the filenames of all photos captured via MobileMap for that feature.

Created

Any table

Date

MobileMap can store the date that a feature was created (this may be different than the created_date which is created when the feature is created in the service)

This field name should be included in MobileMap Settings > Data Model > Custom Created Date Fields

Edited

Any table

Date

MobileMap can store the date that a feature was last edited (this may be different than the last_edited_date which is updated when the feature is updated in the service)

This field name should be included in MobileMap Settings > Data Model > Custom Edited Date Fields or Custom Edited Date (No Overwrite) Fields.

Using the field name Cruise_Date and storing MobileMap Settings > Data Model > Custom Edited Date Fields or Custom Edited Date (No Overwrite) Fields

Created_Source

Any table

Text (50 characters)

MobileMap and InventoryManager can store the application and version in this field for tracking the tool that was used to last edit a given feature

Edited_Source

Any table

Text (50 characters)

MobileMap and InventoryManager can store the application and version in this field for tracking the tool that was used to last edit a given feature

Unique_ID

Stand, Plot, Tree and Log tables when used for timber inventory (cruising)

Text (38 characters)

Stores text representation of a GUID

Parent_ID

Plot, Tree and Log tables when used for timber inventory (cruising)

Text (38 characters)

Stores text representation of a GUID

Archived

Any table

Text (3 characters)

Stores ‘Yes’ or ‘No’ based on whether the feature should be treated as an archived record (may be ignored, e.g., hidden) in some applications.

Plot_X

Plots

Double

When desired this can be used to store the Longitude value from the GPS at the time that the plot is saved. This can help with data QA. This can be set as a hidden field in MobileMap settings so that cruisers do not see this.

Plot_Y

Plots

Double

When desired this can be used to store the Latitude value from the GPS at the time that the plot is saved. This can help with data QA. This can be set as a hidden field in MobileMap settings so that cruisers do not see this.

Distance_To_Plot

Plots

Integer (long)

When this field is included in the Plots data model, MobileMap will calculate the distance between the Plot point coordinates and the Plot_X/Plot_Y coordinates (see two fields above) in feet. Can be used to assess if plot data was entered when the user was farther than expected from the true location of the plot. This can be set as a hidden field in MobileMap settings so that cruisers do not see this.

Time_On_Plot

Plots

Integer (long)

When this field is included in the Plots data model, MobileMap will calculate a running tally of accumulated seconds that the tree list has been open in MobileMap and store it (in total seconds) in this field. Can be used to determine average time per plot and assess outliers (especially plots measured faster than expected). This can be set as a hidden field in MobileMap settings so that cruisers do not see this.

Notes

Any

Text (500+ characters)

(Optional) Also called ‘Comments’, ‘Note’.

Notes_Check

Stands/Plots

Text (500+ characters)

(Optional) Similar to Notes but can be included in list of check cruise fields to hide when not in check cruise mode. Allows separate notes to be recorded by cruiser and check cruiser on the same plot. Not needed on Trees/Logs as these will include a new record for each check Tree/Log; standard Notes field can be used, if relevant, when collecting check cruise data on Trees/Logs.

Check_Score

Plots

Short Integer

(Optional) receives calculated numeric check cruise score for checked plots when check cruise scoring is enabled within InventoryManager and check cruise rules have been defined. Note that standard calculation is: 100 minus plot-level deductions minus the sum of all tree-level deductions within that plot, where tree level deductions are typically assigned integer values. If tree-level deductions are assigned floating point values (e.g., 1.5), use a floating point data type for Check_Score too.

Check_Details

Plots

Text (1000 characters)

(Optional) receives text description of errors that lead to the calculated numeric check cruise score (above)

Check_Score_Date

Plots

Date

(Optional) receives the date at which the check score was LAST calculated/updated.

Sample_Type

Stands

Text (4 characters)

(Optional) Used for double sampling. This field should use a Coded Value Domain that lists all double sampling methods that are supported, and an option for 'None'.  The list of accepted values are: 

  • BB = Big BAF

  • STR = Sample Tree Ratio

  • FTN = First Tree North,

  • PCMC = Point Count or Measure

  • None = no sampling

Sample_Type

Plots

Text (4 characters)

(Optional) Used for double sampling. Same as above except that if Point Count or Measure approach is used, the Plots version of this field should use a Coded Value Domain that lists the following options: 

  • BB = Big BAF

  • STR = Sample Tree Ratio

  • FTN = First Tree North,

  • PCMC = Count Plot

  • PCMM = Measure Plot

  • None = no sampling

Big_BAF

Stands, Plots

Text (10 characters)

(Optional) Used for double sampling. If the Big BAF method is supported, this field should use a Coded Value Domain that lists all of the BAF values that are appropriate for Big BAF.  Allowed values should follow the same convention as the Plot_Size field

PCM_Ratio

Stands, Plots

Text (10 characters)

(Optional) Used for double sampling. If Point Count Measure method is supported, this field should use a Coded Value Domain that lists all of the allowed ratios in the format of '1:2', '1:3', '1:4', etc.

Sample_Tree_Ratios

Stands, Plots

Text (200+ characters)

(Optional) Used for double sampling. If Sample Tree Ratios are supported, this field will store a comma separated list of ratios (e.g., 'DF:S:5,DF:P:10,GF:XX:2,XX:XX:3'). Both MobileMap and InventoryManager contain tools for authoring sample tree ratios by selecting the species, product and ratio for each sample group.

Tally_Measure

Trees

Text (1 character)

(Optional) Used for double sampling. This field should use a Coded Value Domain with the values 'M' for measure and 'T' for tally. MobileMap will update this field automatically when it can determine if a tree should be measured or tallied. Automatic determination of T or M will be possible for PCM, FTN and STR, but not for Big BAF.

BAF_Type

Trees

Text (1 character)

(Optional) Used for double sampling. If Big BAF is supported, this field should be present in the Trees table and use a Coded Value Domain with the values ‘B' for big and 'S’ for small.

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 https://woodlandsg.atlassian.net/wiki/spaces/MD/pages/307789990 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.

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.

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:

Parent_Table_Name:Child_Table_Name[Fieldname_1|Fieldname_2]

where:

Parent_Table_Name is the exact name of the spatial feature layer

Child_Table_Name is the exact name of the child business table

Fieldname_1 is the exact name of the first field to display in the child records table (see ‘Vendor’ in image above)

Fieldname_2 is the exact name of the second field to display in the child records table (see ‘Treatment_Needed’ in image above)

 

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).

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:

 

File Geodatabase

ArcGIS Online/Portal

ABSOLUTE

X

ACCESS

 

X

ACTION

 

X

ADD

X

X

ALL

 

X

ALLOCATE

X

ALPHANUMERIC

X

ALTER

X

X

And

X

X

ANY

 

X

ARE

 

X

AS

 

X

ASC

 

X

ASSERTION

X

AT

 

X

AUTHORIZATION

X

AUTOINCREMENT

X

AVG

 

X

BEGIN

 

X

Between

X

X

BINARY

 

X

BIT

 

X

BIT_LENGTH

X

BOOLEAN

X

BOTH

 

X

BY

X

X

BYTE

 

X

CASCADE

 

X

CASCADED

X

CASE

 

X

CAST

 

X

CATALOG

 

X

CHAR

 

X

CHAR_LENGTH

X

CHARACTER

X

CHARACTER_LENGTH

X

CHECK

 

X

CLOSE

 

X

COALESCE

X

COLLATE

 

X

COLLATION

X

COLUMN

X

X

COMMENT

X

COMMIT

 

X

CONNECT

X

CONNECTION

X

CONSTRAINT

X

CONSTRAINTS

X

CONTINUE

X

CONVERT

 

X

CORRESPONDING

X

COUNT

 

X

COUNTER

 

X

CREATE

X

X

CROSS

 

X

CURRENCY

X

CURRENT

 

X

CURRENT_DATE

X

CURRENT_TIME

X

CURRENT_TIMESTAMP

X

CURRENT_USER

X

CURSOR

 

X

DATABASE

X

DATE

 

X

DATETIME

X

DAY

 

X

DEALLOCATE

X

DEC

 

X

DECIMAL

 

X

DECLARE

 

X

DEFAULT

 

X

DEFERRABLE

X

DEFERRED

X

DELETE

X

X

DESC

 

X

DESCRIBE

 

X

DESCRIPTOR

X

DIAGNOSTICS

X

DISCONNECT

X

DISALLOW

X

DISTINCT

 

X

DISTINCTROW

X

DOMAIN

 

X

DOUBLE

 

X

DROP

X

X

EQV

 

X

ELSE

 

X

END

 

X

END-EXEC

X

ESCAPE

 

X

EXCEPT

 

X

EXCEPTION

X

EXEC

 

X

EXECUTE

 

X

EXISTS

X

X

EXTERNAL

X

EXTRACT

 

X

FALSE

 

X

FETCH

 

X

FIRST

 

X

FLOAT

 

X

FLOAT4

 

X

FLOAT8

 

X

FOR

X

X

FOREIGN

 

X

FOUND

 

X

FROM

X

X

FULL

 

X

GENERAL

 

X

GET

 

X

GLOBAL

 

X

GO

 

X

GOTO

 

X

GRANT

 

X

GROUP

 

X

GUID

 

X

HAVING

 

X

HOUR

 

X

IDENTITY

 

X

IEEEDOUBLE

X

IEEESINGLE

X

IGNORE

 

X

IMMEDIATE

X

IMAGE

 

X

IN

X

X

IMP

 

X

INDEX

 

X

INDICATOR

X

INITIALLY

 

X

INNER

 

X

INPUT

 

X

INSENSITIVE

X

INSERT

X

X

INT

 

X

INTEGER

 

X

INTEGER4

 

X

INTEGER1

 

X

INTEGER2

 

X

INTERSECT

X

INTERVAL

X

INTO

X

X

IS

X

X

ISOLATION

X

JOIN

 

X

KEY

 

X

LANGUAGE

X

LAST

 

X

LEADING

 

X

LEFT

 

X

LEVEL

 

X

LIKE

X

X

LOCAL

 

X

LOCK

 

X

LOGICAL

 

X

LOGICAL1

 

X

LONG

 

X

LONGBINARY

X

LONGTEXT

X

LOWER

 

X

MATCH

 

X

MAX

 

X

MEMO

 

X

MIN

 

X

MINUTE

 

X

MODULE

 

X

MONTH

 

X

MOD

 

X

MONEY

 

X

NAMES

 

X

NATIONAL

X

NATURAL

 

X

NCHAR

 

X

NEXT

 

X

NO

 

X

NOT

X

X

NULL

X

X

NULLIF

 

X

NUMBER

 

X

NUMERIC

 

X

OCTET_LENGTH

X

OF

 

X

OFF

 

X

OLEOBJECT

X

ON

 

X

ONLY

 

X

OPEN

 

X

OPTION

 

X

OR

X

X

ORDER

X

X

OUTER

 

X

OUTPUT

 

X

OWNERACCESS

X

OVERLAPS

X

PAD

 

X

PARTIAL

 

X

PARAMETERS

X

PERCENT

 

X

PIVOT

 

X

POSITION

X

PRECISION

X

PREPARE

 

X

PRESERVE

X

PRIMARY

 

X

PRIOR

 

X

PRIVILEGES

X

PROCEDURE

X

PUBLIC

 

X

READ

 

X

REAL

 

X

REFERENCES

X

RELATIVE

 

X

RESTRICT

 

X

REVOKE

 

X

RIGHT

 

X

ROLLBACK

X

ROWID

 

X

ROWS

 

X

SCHEMA

 

X

SCROLL

 

X

SECOND

 

X

SECTION

 

X

SELECT

X

X

SESSION

 

X

SESSION_USER

X

SET

X

X

SHORT

 

X

SINGLE

 

X

SIZE

 

X

SMALLINT

X

SOME

 

X

SPACE

 

X

SQL

 

X

SQLCODE

 

X

SQLERROR

X

SQLSTATE

X

STDev

 

X

STDEVP

 

X

STRING

 

X

SUBTYPE

 

X

SUBSTRING

X

SUM

 

X

SYSTEM_USER

X

TABLE

X

X

TABLEID

 

X

TEMPORARY

X

TEXT

 

X

THEN

 

X

TIME

 

X

TIMESTAMP

X

TIMEZONE_HOUR

X

TIMEZONE_MINUTE

X

TO

 

X

TOP

 

X

TRAILING

 

X

TRANSACTION

X

TRANSFORM

X

TRANSLATE

X

TRANSLATION

X

TRIM

 

X

TRUE

 

X

TYPE

 

X

UNION

 

X

UNIQUE

 

X

UNKNOWN

X

UPDATE

X

X

UPPER

 

X

USAGE

 

X

USER

 

X

USING

 

X

VALUE

 

X

VALUES

X

X

VAR

 

X

VARCHAR

X

VARBINARY

X

VARP

 

X

VARYING

 

X

VIEW

 

X

WHEN

 

X

WHENEVER

X

WHERE

X

X

WITH

 

X

WORK

 

X

WRITE

 

X

XOR

 

X

YEAR

 

X

YESNO

 

X

ZONE

 

X