Expressions Simple - TeachMeGIS

1 2011 TeachMeGIS All rights reserved. Revised 04/11 1 Expressions 2 Presentation OverviewLabel ExpressionsSimple ExpressionsFunctionsAdvanced ExpressionsArcGIS Formatting TagsDisplay ExpressionQuery ExpressionsSimple QueriesCombined QueriesWildcardsNULLF unctionsComplex QueriesCalculator ExpressionsSimple ExpressionsFunction ExpressionsCustom Functions 2011 TeachMeGIS All rights reserved. Revised 04/11 2 3 Label Expressions 4 Simple ExpressionsExpression Single field Concatenate multiple fields Add static text Units New line VBScript or JScriptSingle symbol for all labelsNo change between ArcMap and 10. 2011 TeachMeGIS All rights reserved. Revised 04/11 3 5 Single Field 6 Multiple Fields 2011 TeachMeGIS All rights reserved.

2 Revised 04/11 4 7 Static TextDouble quotes around t forget a separator between the attributes.[OPERATOR] + " " + [TD] + " ft" 8 New LineVBScript: vbNewLineJscript: \n [OPERATOR] + "\n" + [TD] + " ft" 2011 TeachMeGIS All rights reserved. Revised 04/11 5 9 Functions -Example[OPERATOR] & vbNewLine& Round( [TD] * , 1) & " m" & vbNewLine& FormatDateTime( [SpudDate] , 1) 10 FunctionsV B S c r i p tJ S c r i p tUppercase stringUCase([<field>])[<field>].toUpperCase()Lower case stringLCase([<field>])[<field>].toLowerCase()SubstringMid(([<field>], <start>, <length>)[<field>].substr(<start>, <length>)RoundRound([<field>], <decimals>) ([<field>])FormatDate fieldFormatDateTime([<field>], <format_code>)[<field>].)

3 GetMonth()[<field>].getDate()[<field>].getYear()Formatas currencyFormatCurrency([<field>])No equivalent. Write advanced as percentageFormatPercent([<field>])No equivalent. Write advanced function. 2011 TeachMeGIS All rights reserved. Revised 04/11 6 11 Label expression ButtonsFilter fields by data typeSort fieldsAppend selected fieldList values in fieldVerify expressionHelpSave expression as *.lxpLoad *.lxp 12 Advanced ExpressionsDefine a complete function Inputs are fields Output is label string Conditional statements LoopsExamples: 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 dFunction FindLabel ([OPERATOR])myArray = Split([OPERATOR], ",")strLabel = myArray(0)For i = 1 To UBound(myArray)strLabel = strLabel & "," & vbNewLine & myArray(i)NextFindLabel = strLabelEnd Function 14 Advanced Expressions cont dFunction 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] & "%)

4 "FindLabel= strExpEnd Function 2011 TeachMeGIS All rights reserved. Revised 04/11 8 15 ArcGIS Formatting TagsUse anywhere a text string and symbol are specified Label Expressions Annotation Legend descriptions Graphic textXML syntax rules Start and end tags Can be nested 16 Formatting FunctionsFunctionTagExampleOutput&&[API] & & & [SPUD_DATE]94736283950 & 4/1/2009<> <> < & [STATUS] & > <Not leased>Font<FNT> </FNT> <FNT name= Comic Sans MS size= 14 > & [API] & </FNT> 94736283950 Color<CLR> </CLR> <CLRred= 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]

5 & </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 ExpressionArcGIS 10 Replaces Primary Display FieldUsed 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 LanguageAsks a question of the data in the table Queries are used to extract data from tablesSQL 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.

6 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, ArcInfocoverage Queries are notcase sensitive '?'matches one character '* matches any number of characters Enclose column names in square brackets: [AREA] Date format:#mm-dd-yyyyHH:MM:SS#Personal Geodatabase (*.mdb) 24 Combined QueriesAND OPERATOR = Sea Shell Gas AND TD > 0 AND TD < 3000 Returns values that match each FIELD_TYPE = OIL OR FIELD_TYPE = GAS OR FIELD_TYPE = OIL&GAS Returns values that match any of the ( TYPE = OIL AND WELL_DEPTH > 750) OR ( TYPE = GAS AND WELL_DEPTH > 500) Sets the order in which the operators are used.

7 2011 TeachMeGIS All rights reserved. Revised 04/11 13 25 Complex Query OperatorsIN FIELD_TYPE IN ( OIL , GAS , OIL&GAS ) Reduces the query size by eliminating repetitive OR "MODIFIED" between date '1979-01-01 AND date '1979-12-31 Reduces the query size by eliminating repetitive AND "XREFNO" NOT between '2400163' and '2400660' Performs the opposite query of any operator. 26 NULL ValuesUse NULL keyword to find or exclude records in the table that have not had an attribute : 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.

8 2011 TeachMeGIS All rights reserved. Revised 04/11 14 27 WildcardsUse LIKE when querying with wildcards ? and _ match exactly 1character * and % match any number of characters # matches exactly 1 digitin a text stringExamples: 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 * Mathematic operators without parentheses will proceed in mathematic order, as displayed above (MDAS) Using + or with date fields adds or subtracts days from that dateROUND (<numeric_exp>, <int_exp>)ROUND( PRICE * , 2) BETWEEN 10 AND 20 Rounds items to the specified number of decimal (<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.

9 Revised 04/11 15 29 String FunctionsStrings must always be surrounded by single quotes. STATE_NAME = Texas Use UPPERor LOWERto 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 dMID (<string>, <start>, <length>)MID ( REFNO , 3, 2) Returns values from within a string. First character has a start value of (<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.

10 Revised 04/11 16 31 More FunctionsTRIM (<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 FunctionsCURRENT_DATE() EXPIRATION_DATE > CURRENT_DATE() Returns the system s current (<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 LoadOnce a SQL expression is working, save it for future use.