SQL statements and visual specifications (Defense Mapping)

The rules in a visual specification determine the way a calculated representation or calculated field is going to appear in your map based on the attributes you are using. Rules are defined using either or both of the following:

• Structured Query Language (SQL) statements
• Scripts constructed using Visual Basic Scripting Edition (VBScript)

When you are creating calculated representation and calculated field rules, you must define the set of features to be symbolized or for which you are going to create fields.

Using SQL to specify features for rules

A SQL SELECT statement allows you to define a set of features based on any attributes within a feature class or on relationships between the feature class and other feature classes or stand-alone tables. For example, if the name of a feature is related to a record in a stand-alone table, you can write a query that states that the feature should be included as the query result if the field value is equal to the table value. If you want to select features from a feature class such as BuoyP, where the feature name is equal to the value in a field within a stand-alone table, you would configure a statement similar to the following:

SELECT OBJECTID FROM BuoyP, PRODMAP_FREL WHERE LNAM=PRODMAP_FREL.SRCNAM

SQL syntax support

VST SQL syntax is based on personal geodatabase (.mdb) syntax. When the Calculate Visual Specifications tool runs, VST updates SQL statements to conform to the underlying database.

The Query Builder

The standard ArcMap Query Builder allows you to construct a WHERE clause to filter or select the rows of a feature class or table based on attributes contained within it. With the Visual Specifications tool's Query Builder, you can construct full SQL SELECT statements, including inner joins of multiple tables and feature classes. If needed, you can fully specify all the clauses (SELECT, FROM, WHERE, GROUP BY, and ORDER BY) that a SQL query supports.

If you want to use valid SQL expressions for which there are no command buttons, you can edit the query text directly by checking the Edit query text check box and typing the expression directly.

If you want to specify a GROUP BY or ORDER BY clause, check the Edit query text check box and type the expression in the WHERE text box after any WHERE clause expressions. For example:

"LNAM=PRODMAP_FREL.SRCNAM ORDER BY LNAM"

Formatting the select list

For calculated representations, the SELECT list usually contains only the OBJECTID of the feature. This query needs to return a list of features to which a representation rule will be applied. For calculated fields, the SELECT list might contain several attributes that an associated Visual Basic (VB) expression formats into a single text string.

Multiple results per feature

Typically, a query will return one result row per feature. In advanced situations, a query might return multiple rows per feature. In that case, an associated VB expression would be needed to provide the additional logic to process the set of result rows that pertain to a particular feature down to a single rule ID or text string value.

Saving and recalling SQL statements

The statements you create can be saved and recalled for use with other calculated representation or calculated field rules. The statements are saved as query files, which place the SELECT, FROM, and WHERE information on separate lines, as shown below:

, CITY_NAME
, US_UrbanAreas
POP1990 = US_UrbanAreas.POPULATION

SQL statements and definition queries

When you are creating a SQL statement, you are associating the set of features that satisfy the query with a particular representation rule or field. Since specifications can be applied to feature classes in addition to layer files, definition queries are used only when the Update Feature Class(es) check box is checked, and those definition queries are not stored with the specification. This is because some definition queries include map-specific information that should not be included in a specification that will be applied to many maps.

If there are specific portions of a query that you want to include in the specification, these need to be explicitly defined in the query. The map-specific features will be filtered out, if required, when the specification is run on a layer file that contains the appropriate definition query.