MobileMap: Data Model / Data Modeling Process
- 1 Data Modeling Overview
- 2 Documenting Data Models
- 3 General Data Model Requirements
- 3.1 Feature Types
- 3.2 Conventions
- 3.2.1 General Naming Conventions
- 3.2.2 Standard Field Names
- 3.3 Esri Reserved Field Names
- 3.4 Editor Tracking
- 4 Cruise Data Model Requirements
- 4.1 Layers
- 5 Business Logic Data Model Requirements
- 5.1 Layers
- 6 Generic Data Model Requirements
- 6.1 Layers
- 7 Relationships
- 8 Templates
- 9 Generating Data Models from TCruise Templates
- 10 Build the Data Model
- 11 Data Model Validation
- 12 Related Articles:
- 13 ArcGIS Reserved Words List
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 |
---|---|---|---|
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:
|
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:
|
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:
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 InventoryManager: Validation Rules And Related Domains 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.
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_IDName: 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_IDName: 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_IDName: 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_IDName: 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_IDName: 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.
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
Export an ‘WSG database’ SQLite file from TCruise
Load a TCruise template (TCT)
Workup toolbar
Load TCT
Select desired template
File > Save As
Save as TCD
Stratify
Select Strata
Browse for files to add to the list
Select relevant TCD file
Stratify
Batch CSV files Export for External Consumer
SQL Lite db export
OK
Open SQLite file using DB Browser or equivalent
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
Copy the SQL commands from the code sections listed below
Run the SQL command
Export the results as a CSV file
Open CSV files in Excel and clean it up to
Data model
Remove unnecessary fields (e.g., TCruiseFieldName, LayerOrder, rowid).
Verify all table names, field names, nullable status, data types, domains, etc.
Field names - use Title_Case (underscores between words)
Field aliases - use ‘=substitute(A2,“_”,” “)’ to change from Title_Case with underscores to Title Case
Domains
Update domain names to be both intuitive and use PascalCase (e.g., PlotSize) and ensure they are updated in the Data Model CSV file
Ensure that all coded value domains have both codes and descriptions
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.
Verify all codes, descriptions and range domains min and max values
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.
SQL Code
Data Model CSV file
-- 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
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
TCruise Export CSV file
-- 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:
Round Trip 1 Test
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.
User 1 creates plots within the Stand created above and verifies required fields, allowed values, min/max values, etc.
User 1 downloads Stands/Plots to MobileMap and verifies symbology and ability to search on relevant fields.
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.
User 1 uploads data from MobileMap and verifies that there are no upload errors
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
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.
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).
Round Trip 2 Test
User 1 repeats steps 1a - 1b above for a new stand
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’
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
Multiple Users Test
User 1 repeats steps 1a - 1b above for a new stand
User 2 completes steps 1c - 1d above
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 |