Transcript Tabular Data Lecture Part 1

Attribute Data [PART 1]
Feb. 20th, 2017
Wenwen Zhang
CP6514/Spring 2017
1
Agenda
• Attribute Table Concepts
1.
2.
3.
4.
5.
Overview of tables
Database management systems
Queries on tables
Statistics on tables
Field types
• About ArcGIS
1. Tables in ArcGIS
2. Editing and calculating fields
3. Importing tables
Wenwen Zhang
CP6514/Spring 2017
2
Overview of tables
• Table
• Definition – a data structure for storing multiple attributes about a location or
an object.
• Composed of rows (records) and columns (fields or attribute fields).
• Types in GIS
• Attribute tables – each row tied to a spatial feature (Feature ID or Object ID).
• Standalone tables – exist independently of a geographic data set. (Object ID).
Wenwen Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
3
STATISTICS
FIELD TYPE
Database management systems (DBMS)
• Flat file database
• Stores information not in structured ways.
• Simple but not efficient when finding and selecting certain records.
• Hierarchical database
• Parent tables linked to child tables through a key (a common field)
• Relationships between tables are fixed/inflexible
• Quick search but only a small set of operations possible
• Relational database [many government and companies uses]
• Relationships are not defined ahead of time
• Users can temporarily associate two tables with a common key (=join)
• More about table join in the next class
• Underlying database of ArcGIS
• dBase table for shapefiles / Microsoft Access for personal geodatabase / large-scale RDBMS
(e.g. SQL server) for enterprise geodatabase.
Wenwen Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
4
STATISTICS
FIELD TYPE
Queries on tables
• Query – extract certain records from a table based on specified conditions
• Structured Query Language (SQL)
• Most database use to write and execute queries.
SELECT * FROM landuse WHERE ZONE = 492
Table name
Field name
Logical
expression
SELECT * FROM landuse WHERE ZONE = 492 AND VALUE > 300000
Table name
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
OVERVIEW
Field name
Logical
expression
CP6514/Spring 2017
DBMS
QUERY
5
STATISTICS
FIELD TYPE
Statistics on tables
• Summary statistics
• Min, max, mean, median, standard deviation, histogram
• Can used to check the validity of the column values
Wenwen Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
6
STATISTICS
FIELD TYPE
Summarize
• Combine records into groups based on a categorical field.
• Calculate summary stats separately for each group.
• Create a new (standalone) table.
• This new table can be joined to a feature class for map making
• Essentially, one-to-many relationship is converted to one-to-one.
Earthquake standalone table
(Each state more than one record)
Earthquake summarized table
(Each state has one record)
Wenwen Zhang
OVERVIEW
Earthquake death by state
(joined to the state feature class)
CP6514/Spring 2017
DBMS
QUERY
JOIN/RELATE
7
STATISTICS
FIELD TYPE
How to summarize in ArcGIS
Right-click
a specific field
Choose statistics
Name output table
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
8
STATISTICS
FIELD TYPE
Field types
• Why do we care about field types? Because in a database,
• Each column contains one type of data (e.g. text or integers, but not both).
• Each field must be defined before use.
• Once a field definition is set, then it cannot be changed.
• Parameters for fields in ArcGIS
•
•
•
•
Field length – how many characters can be stored in text fields.
Precision – a storage width for numeric fields
Scale – the number of decimal places in numeric fields.
Examples
• Precision 5 & scale 0 – from -9999 to 99999 (‘-’ counted for precision)
• Precision 5 & scale 2 – from -9.99 to 99.99 (‘-’ & ‘.’ counted for precision)
Wenwen Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
9
STATISTICS
FIELD TYPE
How to store values
• A byte – the basic unit of storage space for a computer
•
•
•
•
A string of eight digits (bits), which are either zeros or ones.
Represents a number in base 2 (binary numbers)
One byte can store values from 0 to 255 (=28-1)
Two bytes for numbers up to 216-1 (=65,535)
In base 2
00000000 = 0
11111111 = 255
28=256
learn.mikroe.com
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
10
STATISTICS
FIELD TYPE
How to store values
• ASCII - American Standard Code for Information
Interchange [ArcGIS uses this to save Text Field]
• Number, letter, and symbol assigned a single-byte
code between 0 and 255. (e.g. three bytes for cat, and
five bytes for 147.6)
• Simple & standard
Length = max number of
characteristics can be
saved in the field
• Binary[ArcGIS uses this to save Numeric
(Integer) Field]
• A number is stored directly in base 2.
• 16 to a single byte of 00010000
• 14456 – five bytes in ASCII vs. two bytes in binary
• More efficient and faster than ASCII
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
OVERVIEW
Short Field Type: 2-byte
Long Field Type: 10-byte
CP6514/Spring 2017
DBMS
QUERY
11
STATISTICS
FIELD TYPE
How to store values
Float Field Type: 8
Significant field in
Mantissa
• Integer vs. float storage
• Scientific notation: 1,234,567 = 1.234567 X 106
• Scientific notation: 1,000,000,000 = 1 X 109
• Scientific notation: 0.000 000 001 = 1 X 10-9
Double Field Type: 16
Significant field in
Mantissa
• Exponential or floating-point data [ArcGIS uses this to save Numeric Field]
• Store values as a Mantissa and an exponent
• 12345678 -> 1.2345678 X 108 (or 1.23456789e08)
• Precision – a storage width for numeric fields
• A single-precision floating-point field – eight significant digits in mantissa
• A double-precision field – 16 significant digits
• Much more flexible than numeric or binary types
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
12
STATISTICS
FIELD TYPE
Field types
Wenwen Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
13
STATISTICS
FIELD TYPE
Exercise
• What kind of storage would be used to
store the following kinds of data? How
many bytes are needed for each field?
•
•
•
•
•
Last name
Number of children in household
Student grade point average
Year of birth
County population
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
OVERVIEW
CP6514/Spring 2017
DBMS
QUERY
14
STATISTICS
FIELD TYPE
Attribute Tables Operation in ArcGIS ArcGIS
Wenwen Zhang
CP6514/Spring 2017
15
Tables in ArcGIS
Options menu
Field
Right-click field name to get
menu
Title
Records
Status bar
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
16
Managing multiple tables
Arrange in tabs
Switch between
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
17
Layer Properties > Fields tab
• Field names – 13 or
fewer characters
(only letters,
numbers, and
underscores)
• An alternative name
Show/hide all
(alias) – more
descriptive names
are possible
Move field
up/down
Field alias
Format
Hide field
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
18
Field properties
Right-click
field name
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
19
Adding a field
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
20
Adding multiple fields
• Easier in ArcCatalog properties
•
•
•
•
type name in empty row
Set field type
Set field properties
Click apply
1
2
3
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
21
Editing fields
• Use Editor toolbar
1. Start editing
2. Type edits in fields
3. Save edits
4. Stop editing
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
22
Calculating fields
• Add a new field if necessary
• Consider whether you need decimal places.
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
23
Calculating fields
• Right-click field to
calculate
• Usually field should be
EMPTY
Be careful! Calculations
can’t be undone unless
you are in an edit session.
Enter
expression
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
24
Geodatabase geometry measures
• Automatically created and maintained
• Usually appear at end of table
• Shape_Area
• Shape_Length
• Units will match units of the coordinate system
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
25
Shapefile geometry measures
• Shapefiles DO NOT create or maintain area/length fields
automatically!
• Must be created and updated manually
• Some functions and operations can change the lengths and areas of features
• If you find an AREA field, there is no guarantee that it is correct.
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
26
Be careful!
AREA/LENGTH/PERIMETER
fields in shapefiles are NOT
updated when features
change.
After
Before
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
27
User geometry calculations
Choose type,
coordinate system, and
units
Right-click empty or
incorrect field to
update
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
28
True or false?
• A field named AREA in a shapefile will always have the correct area?
• False. Shapefile area fields are not automatically maintained.
• A field named AREA in a geodatabase will always have the correct
area?
• False. Automatically updated fields in a geodatabase are called
Shape_Area.
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
29
Import text (ASCII) tables
• Allowed in ArcGIS
1. Tab-delimited
2. Comma-delimited
3. Fixed-column
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
30
Excel files
• Overview
• Excel worksheets with suitable layout can be opened as tables in ArcGIS
• Read-only (Export as a standalone dbf file or a table in your geodatabase!)
• Most functions that do not involve changing the file will work (sort, query)
• Tables cannot be changed or edited
• Requirement
1.
2.
3.
4.
5.
First row must contain field headings with “legal” field names as defined earlier
No blank rows, merged cells, or formulas should be used
Each column should contain only text or numbers, but not both.
It is helpful for each column to be formatted as text, numeric, etc.
ArcMap cannot open files that are already open in Excel—they must be closed first
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
31
Acceptable Excel worksheet
Columns formatted
as text or numeric
Legal field names
No formulas or
blank lines
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
32
Excel workbooks and worksheets
• Workbooks and worksheets
• An Excel workbook file (.xls) may contain more than one worksheet.
• There may be one or more named worksheets.
• By default there are three named Sheet1, Sheet2, Sheet3.
• ArcMap can only open one worksheet at a time.
• You will open the workbook like a folder and select a single worksheet
Copyright
© 2015
by Maribeth H. Price
Wenwen
Zhang
TABLES
EDIT/CALCULATE
CP6514/Spring 2017
IMPORT
33
Class Exercises
• Mastering Skill Part from Chapter 6
• EXCEPT for “Table Joining”, “Relate”, and “XY Data”
Wenwen Zhang
CP6514/Spring 2017
34