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.

2 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 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>].

3 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>].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 *.)

4 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] & "%)

5 "if [Name3] >= "0" then strExp= strExp& vbNewLine& [Name3] & " (" & [PerInt3] & "%)"if [Name4] >= "0" then strExp= strExp& vbNewLine& [Name4] & " (" & [PerInt4] & "%)"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]

6 & </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 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?

7 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, 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 (*.)

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

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

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