Lecture 42: GIS Database Management and Thematic Mapping II

1
UST/ENV/PAD/PDD 642/742 Introduction to
Geographic Information Systems
Lecture 4-2 GIS Database Management and
Thematic Mapping II
2
Typical GIS Database Operation
Sorting arranging some or all records in a
database according to some order, ascending or
descending Selecting Querying selecting
records from a database that follow some
specified criteria Editing Populating
altering/entering field values of some
particular set of records or all existing
records Summarizing preparing summaries of
data, classifying data based on particular
groups, and providing statistical summaries for
the groups Generating Reports preparing
tables, charts, graphs to visually report data,
embed map graphics in text documents
3
Typical GIS Database Operation
Creating New data creating new records or
new fields for existing spatial feature
data Recalculating Fields altering the
values of some fields for several records
simultaneously Creating Thematic Maps using
the attribute data to generate the map graphic
display Generating Unique Identifiers
generating unique identifiers or IDs for
records Converting Data types converting
field values from one data type e.g. Number to
another e.g. text
4
Database Queries and Functions(Boolean Operators)

• AND
• (Statement A) AND (Statement B)
• record selected only if both Statement A and
  Statement B are True
• OR exclusive OR
• (Statement A) OR (Statement B)
• record selected if either Statement A or
  Statement B is True
• NOT
• NOT (Statement A)
• record selected only if Statement A is False
• XOR
• (Statement A) XOR (Statement B)
• record is selected if Statement A is True and
  Statement B is False or if Statement A is False
  and Statement B is True
• record is not selected if both Statement A and
  Statement B are True or if both Statement A and
  Statement B are False

5
Database Queries and Functions(Relational
Operators)

• (Variable A)
• record selected only if Variable A is less than
  Variable B
• (Variable A)
• record selected if Variable A is less than or
  equal to Variable B
• (Variable A) (Variable B)
• record selected only if Variable A is equal to
  Variable B
• (Variable A) (Variable B)
• record selected only if Variable A is greater
  than Variable B
• (Variable A) (Variable B)
• record selected only if Variable A is greater
  than or equal to Variable B

6
Database Queries and Functions (Arithmetic
Operators)

• mod
• (Variable A) mod (Variable B)
• calculates remainder after Integer division
• (Variable A) (Variable B)
• multiplies the two variables
• /
• (Variable A) / (Variable B)
• divides Variable A by Variable B
• (Variable A) (Variable B)
• adds the two variables
• -
• (Variable A) - (Variable B)
• subtracts Variable B from Variable A can be
  unary

7
Statements with Arithmetic Operators
Example 1 Result 5 3 3 24
No order of precedence Result 5 3
3 14 with order of precedence, then
When in doubt, use parentheses Result 5
( 3 3 ) 14 Example 2 Result
5 mod 3 5 7 2 11 58 No order of
precedence Result 5 mod 3 5 7 2 11
46 first mod, then , then and - When in
doubt, use parentheses Result ( 5 mod 3 )
( 5 7 ) 2 11 46
8
Example String Functions
1. Extracting Parts of Strings from the
left strVar Left( inputString,
lengthToExtract ) strVariable1 Left(
ArcGIS , 3 ) Arc 2. Extracting Parts of
Strings from the right strVar Right(
inputString, lengthToExtract ) strVariable1
Right( ArcGIS , 3 ) 3. Extracting Parts
of Strings from the middle strVar Mid(
inputString, startPosition , lengthToExtract
) strVariable1 Mid( ArcGIS , 1 , 3 )
Arc strVariable2 Mid( ArcGIS , 4 , 3 )
GIS strVariable1 Mid( ArcGIS , 3 )
cGIS 4. Upper Case and Lower Case
Conversion strVar UCase( inputString )
strVariable1 UCase( abba ) ABBA
strVariable2 LCase( ABBA ) abba 5.
Trimming Strings strVariable1 Trim(
world ) world strVariable2
LTrim( world ) world
strVariable3 RTrim( world )
world
9
Example Numeric Functions
1. Floor Integer l_intVar Int( inputNumber
) l_intVariable1 Int( 5.614 ) 5
l_intVariable1 Int( -3.14 ) -4 2.
Rounding Numbers singleVar Round(
inputNumber , numberOfDecimalPlaces )
singleVariable1 Round( 5.619 ) 6.000000
singleVariable1 Round( 5.619, 1 )
5.600000 singleVariable1 Round( 5.619, 2 )
5.620000 3. Absolute Numbers singleVar
Abs( inputNumber ) singleVariable1 Abs(
-43.3 ) 43.30000 singleVariable1 Abs(
24.9 ) 24.90000 4. Square Roots singleVar
Sqr( inputNumber ) singleVariable1 Sqr(
4 ) 2.000000 singleVariable2 Sqr( -13 )
ERROR! 5. Natural Logarithm of a
Number singleVar Log( inputNumber )
singleVariable1 Log( 10 ) 2.302585
singleVariable2 Log( -13 ) ERROR!
10
Summarizing Table

• Summarizing is often a preliminary step in
  point-in-polygon analyses or in preparing
  data for some other downstream application or
  used in reports
• Any table can be summarized on a specified
  field -- the results are stored in a new
  result table
• The result table contains one unique record
  for every unique value group encountered in the
  specified field of the original table
• The result table also contains a count field
  that reports the number of times that the
  unique value occurred in the specified field of
  the original table
• Additionally, the result table can contain
  aggregated information about other fields
  in the original table -- the aggregation
  technique can be specified by the user, as
  average, sum, standard deviation, minimum,
  first, last, etc.

11
Example of Summarizing Table Result Tables
Consider the following example of an experiment
on diet types There are 4 diet types -- A, B, C
and D There are 4 subjects -- Williams, Dalton,
Rogers and Trotville The subjects were on each
diet for a period of one week Their weights were
measured at the beginning and ending period of
the diet The raw data was recorded as shown in
the table below Your task is to summarize the
data by subject and by diet type!
12
Example of Summarizing Table Result Tables