How to Change the world

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 1
1
[email protected]
Mastering Expressions

2
Presentation Overview
Label
Expressions
Simple
Expressions
Functions
Advanced
Expressions
ArcGIS Formatting
Tags
Display Expression
Query
Expressions
Simple Queries
Combined Queries
Wildcards
NULL
Functions
Complex Queries
Calculator
Expressions
Simple
Expressions
Function
Expressions
Custom Functions

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 2
3
Label Expressions

4
Simple Expressions
Expression
– Single field
– Concatenate multiple fields
– Add static text
• Units
• New line
– VBScript or JScript
Single symbol for all labels
No change between ArcMap
9.3 and 10.

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 3
5
Single Field

6
Multiple Fields

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 4
7
Static Text
Double quotes around text.
Don’t forget a separator
between the attributes.
[OPERATOR] + " " + [TD] + " ft"

8
New Line
VBScript: vbNewLine
Jscript: “\n”
[OPERATOR] + "\n" + [TD] + " ft"

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 5
9
Functions - Example
[OPERATOR] & vbNewLine & Round(
[TD] * 0.3048, 1) & " m" &
vbNewLine & FormatDateTime(
[SpudDate] , 1)

10
Functions
VBS c r i pt J S c r i pt
Upper case string
UCase([<field>]) [<field>].toUpperCase()
Lower case string
LCase([<field>]) [<field>].toLowerCase()
Substring
Mid(([<field>],
<start>,
<length>)
[<field>].substr(
<start>, <length>)
Round
Round([<field>],
<decimals>)
Math.round([<field>])
Format Date field
FormatDateTime(
[<field>],
<format_code>)
[<field>].getMonth()
[<field>].getDate()
[<field>].getYear()
Format as
currency
FormatCurrency(
[<field>])
No equivalent. Write advanced
function.
Format as
percentage
FormatPercent(
[<field>])
No equivalent. Write advanced
function.

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 6
11
Label Expression Buttons
Filter fields by data type
Sort fields
Append selected field
List values in field
Verify expression
Help
Save expression as *.lxp
Load *.lxp

12
Advanced Expressions
Define a complete function
– Inputs are fields
– Output is label string
– Conditional statements
– Loops
Examples:
– Stack a label at a comma
in the text.
– Label Percent Interest
from multiple fields.

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 7
13
Advanced Expressions cont’d
Function FindLabel ([OPERATOR])
myArray = Split([OPERATOR], ",")
strLabel = myArray(0)
For i = 1 To UBound(myArray)
strLabel = strLabel & "," &
vbNewLine & myArray(i)
Next
FindLabel = strLabel
End Function

14
Advanced Expressions cont’d
Function FindLabel ([Name1], [PerInt1] ,
[Name2], [PerInt2], [Name3], [PerInt3],
[Name4], [PerInt4])
strExp = [Name1] & " (" & [PerInt1] & "%)"
if [Name2] >= "0" then strExp = strExp &
vbNewLine & [Name2] & " (" & [PerInt2] & "%)"
if [Name3] >= "0" then strExp = strExp &
vbNewLine & [Name3] & " (" & [PerInt3] & "%)"
if [Name4] >= "0" then strExp = strExp &
vbNewLine & [Name4] & " (" & [PerInt4] & "%)"
FindLabel = strExp
End Function

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 8
15
ArcGIS Formatting Tags
Use anywhere a text string and symbol are specified
– Label expressions
– Annotation
– Legend descriptions
– Graphic text
XML syntax rules
– Start and end tags
– Can be nested

16
Formatting Functions
Function Tag Example Output
& &amp;
[API] & “&amp;” &
[SPUD_DATE]
94736283950
& 4/1/2009
<
>
&lt;
&gt;
“&lt;” & [STATUS] & “&gt;” <Not leased>
Font <FNT> </FNT>
“<FNT name=„Comic Sans MS‟
size=„14‟>” & [API] &
“</FNT>”
94736283950
Color <CLR> </CLR>
“<CLR red=„255‟,
green=„0‟, blue=„0‟>” &
[API] & “</CLR>”
94736283950
Bold <BOL> </BOL> “<BOL>” & [API] & “</BOL>” 94736283950
Italics <ITA> </ITA> “<ITA>” & [API] & “</ITA>” 94736283950
Underline <UND> </UND> “<UND>” & [API] & “</UND>” 94736283950

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 9
17
Formatting Tag Help

18
Formatting Tags in a Legend

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 10
19
Display Expression
ArcGIS 10
– Replaces Primary Display Field
Used for
– MapTips
– Identify
– Attributes

20
Query Expressions

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 11
21
What is a SQL Query?
SQL = Structured Query Language
Asks a question of the data in the table
– Queries are used to extract data from tables
SQL where clause format:
<field_name> <operator> <value>
Examples:
– “TOTAL_DEPTH” > 500
– “API_NUM” = „89762535‟

22
Where can a Query be Used?
Anywhere ArcGIS talks to a table:
– Select By Attribute
– Field Calculator
– Definition Query
– Label Classes
– Etc.

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 12
23
Syntax Variations
• Queries are case sensitive
• '_' matches one character
• '%' matches any number of characters
• Enclose column names in double quotes:
"AREA"
• Date format: date „yyyy-mm-dd‟
File Geodatabase
(*.gdb), Shapefile,
ArcSDE,
dBASE table,
ArcInfo coverage
• Queries are not case sensitive
• '?' matches one character
• '*‟ matches any number of characters
• Enclose column names in square brackets:
[AREA]
• Date format: #mm-dd-yyyy HH:MM:SS#
Personal
Geodatabase
(*.mdb)

24
Combined Queries
AND
“OPERATOR” = „Sea Shell Gas‟ AND “TD” > 0 AND
“TD” < 3000
• Returns values that match each statement.
OR
“FIELD_TYPE” = „OIL‟ OR “FIELD_TYPE” = „GAS‟ OR
“FIELD_TYPE” = „OIL&GAS‟
• Returns values that match any of the statements.
PARENTHESES
(“TYPE” = „OIL‟ AND “WELL_DEPTH” > 750) OR
(“TYPE” = „GAS‟ AND “WELL_DEPTH” > 500)
• Sets the order in which the operators are used.

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 13
25
Complex Query Operators
IN
“FIELD_TYPE” IN („OIL‟, „GAS‟, „OIL&GAS‟)
• Reduces the query size by eliminating repetitive OR statements.
BETWEEN
"MODIFIED" between date '1979-01-01‟
AND date '1979-12-31‟
• Reduces the query size by eliminating repetitive
AND statements.
NOT
"XREFNO" NOT between '2400163' and '2400660'
• Performs the opposite query of any operator.

26
NULL Values
Use NULL keyword to find or exclude records in the table that
have not had an attribute set.
Examples:
“WELL_DEPTH” IS NULL
• Returns all wells in the table that have no value in the
WELL_DEPTH column.
“WELL_DEPTH” IS NOT NULL
• Returns all wells in the table that have any value in the
WELL_DEPTH column.

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 14
27
Wildcards
Use LIKE when querying with wildcards
– „?‟ and „_‟ match exactly 1 character
– „*‟ and „%‟ match any number of characters
– „#‟ matches exactly 1 digit in a text string
Examples:
“TYPE” LIKE „O?G‟
 Matches ‘O&G’, ‘O+G’, ‘O_G’
 Does not match ‘Oil’, ‘OilAndGas’
“TYPE” LIKE „%Shell%‟
 Returns anything containing ‘Shell’
 ‘Shell’, ‘Shell Offshore’, ‘Royal Dutch Shell’, ‘Sea Shell Gas’.
“TYPE” LIKE „4865#‟
 Matches ‘48651’, ‘48652’
 Does not match ‘4865’, ‘4865a’, ‘486599’

28
Mathematic Functions
*, /, +, -
“VOLUME_YEAR”/365 > “MAX_RATE”*0.75
• Mathematic operators without parentheses will proceed in
mathematic order, as displayed above (MDAS)
• Using + or – with date fields adds or subtracts days from that date
ROUND (<numeric_exp>, <int_exp>)
ROUND(“PRICE” * 1.0825, 2) BETWEEN „10‟ AND „20‟
• Rounds items to the specified number of decimal places.
TRUNCATE (<numeric_exp>, <int_exp>)
TRUNC(“POPULATION”, -3) = 15000
• Replaces the specified number of decimal places to a value of 0
• Negative numbers move to the left of the decimal

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 15
29
String Functions
Strings must always be surrounded by single quotes.
“STATE_NAME” = „Texas‟
Use UPPER or LOWER to avoid case sensitivity issues:
UPPER (“STATE_NAME”) = „TEXAS‟
LOWER (“STATE_NAME”) = „texas‟
Use >, <, >=, <= to retrieve strings based on sort order.
“STATE_NAME” <= „E‟
• Returns all states starting with the letters A through D.

30
String Functions cont’d
MID (<string>, <start>, <length>)
MID (“REFNO”, 3, 2)
• Returns values from within a string.
• First character has a start value of 1.
Left (<string>, <num_characters>)
Right (<string>, <num_characters>)
Left (“API_NUM”, 2) < 20
• Extracts characters from left or right side of the string.

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 16
31
More Functions
TRIM (<string>)
TRIM (“API_NUM”) = „1123400000‟
• Removes leading spaces from a string
• Works on all file types
*.gdb, *.shp and *.dbf allow additional syntax to remove any
leading/trailing character:
TRIM (leading „0‟ from “API_NUM”) = „1234‟

32
Date Functions
CURRENT_DATE()
“EXPIRATION_DATE” > CURRENT_DATE()
• Returns the system’s current date.
EXTRACT (<keyword>) FROM (<extract_source>)
EXTRACT (YEAR from "Expiration" ) >= 2010 and
EXTRACT (YEAR from "Expiration" ) < 2015
• Returns the specified portion of the date.
• Keywords: YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 17
33
Save and Load
Once a SQL expression is
working, save it for future
use.
– Saved as an Expression
(*.exp) file.
Use Load to re-use a saved
expression.
– Select by Attribute
– Definition Query
– Query Builder
– etc.

34
Calculator Expressions

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 18
35
Field Calculator
Edit many records at once (in or out of an edit session).
Right-click on the field to update Field Calculator.

36
Field Calculator cont’d
Runs on selected features
– All if none selected
Build a VB Script or Python
expression

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 19
37
Field Calculator Help
Use the Type options to see
functions available for the
different data types.
– Number
– String
– Date
Drop-down for field options.
Getting help:
– Help button
– F1
– Search online

38
Function Examples
•[LON] & “, “ & [LAT]
•str(!LON!) + ", " + str(!LAT!)
•UCase([NAME])
•!NAME!.upper()
•Left([STACOREFNO], 2)
•!STACOREFNO![0:2]
•[DEPTH_FT] * 0.3048
•!DEPTH_FT! * 0.3048
•Round([ROYALTY], 2)
•round( !ROYALTY!, 2)
Concatenate latitude and
longitude:
Convert a string field to
upper case:
Extract the state code from a
STACOREFNO field (the first
2 digits are the state code):
Convert depth in feet to
meters:
Round numeric data to 2
decimal places:

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 20
39
Function Notes
VB Script
• Field names surrounded
by [ ]
• & for concatenation
(all data types)
• Case insensitive
• <function_name>
([<field_name]>)
Python
• Field names surrounded
by ! !
• + for concatenation
(must be strings)
• Case sensitive
• !<field_name>!.<functi
on_name>()

40
Field Calculator Tricks
Have you thought of using a field calculation to…
– Re-order columns
– Concatenate columns
– Change data type
– Convert units
– Create label expression
– Force values to upper case (for case-insensitive searches)
– Remove leading/trailing spaces
– Extract values from columns
– Create incrementing values

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 21
41
Field Calculator Syntax
Concatenate columns
•str(!Township!) + "-" + str(!Section!)
Change data type
•!ExpireDate!
Convert units
•!ground_ele! * 0.3048
Change case
•!name!.upper()
Remove leading/trailing spaces
•!name!.strip()

42
String Slicing
Slice returns a
substring:
– 2 inputs (simple)
• Start, end
– 3 inputs (extended)
• Start, end, and
stride
All inputs are 0-based.
Negative inputs count
backwards from end.
Non-inclusive.
>>> str = “Hello world.”
>>> str[0:5]
„Hello‟
>>> str[2:-4]
„llo wo‟
>>> str[2:8:2]
„low‟

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 22
43
Custom Functions
Create incrementing values
Use FieldA, but if it’s NULL
use FieldB
Change in values
(percentage increase)
Random values
Maximum value from
selected values

44
Incrementing Value
rec=0
def autoIncrement():
global rec
pStart = 1 #adjust start value, if req'd
pInterval = 1 #adjust interval value, if req'd
if (rec == 0):
rec = pStart
else:
rec = rec + pInterval
return rec

© 2011 TeachMeGIS All rights reserved.
Revised 04/11 23
45
Copy Non-Null Value
#If the first field is NULL, use the second field
def UseNonNullAttribute(fld1, fld2):
if fld1:
value = fld1
else:
value = fld2
return value

46
Any questions?
[email protected]