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]

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

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

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

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

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