Attribute Data Input and Management

1
GEOG 3150

• Attribute Data Input and Management

2
Tabular Data
GEOG 3150

• Part I
• Database Concepts
• and
• Databases within the ESRI Software Environment

3
Tabular Data
GEOG 3150

• Geographic Information Systems store both spatial
  and attribute data.
• Depending on the GIS data model, spatial and
  attribute data can be stored
• Together in one or more tables in a database, or
• Separately in a wide variety of formats.

4
GEOG 3150

• Table and Database

5
GEOG 3150

• Spreadsheet vs. Database Table

6
GEOG 3150

• Columns in Database Tables
• have defined Data Types

7
GEOG 3150

• Individual Cells in Spreadsheets
• can contain any Data Type

8
Tabular Data
GEOG 3150

• A spreadsheet is the application of choice for
  most documents that organize numbers such as
  budgets, financial statements, grade sheets,
  sales records, etc.
• A spreadsheet can perform simple or complex
  calculations on the numbers you enter in rows and
  columns.

9
Tabular Data
GEOG 3150

• Spreadsheets and database tables exist within
  software environments that are capable of
  manipulating the data they contain.
• These software environments are optimized to
  perform certain types of operations.

10
Tabular Data
GEOG 3150

• Spreadsheets and spreadsheet programs are
  optimized to perform operations on numbers. These
  operations can be performed on entire rows,
  entire columns, groups of cells, or individual
  cells.
• Most mathematical operations are performed with
  formulas that are stored within cells.

11
Tabular Data
GEOG 3150

• Database tables and database management systems
  are optimized to manage lists that are not
  entirely numbers such as addresses and phone
  numbers, inventories, and membership rosters.
• Database programs allow the user to sort data
  more easily and effectively than can be done with
  a spreadsheet program, and to more effectively
  query the data.

12
Tabular Data
GEOG 3150

• Database programs can also be used more
  effectively to create forms that can be used to
  enter, edit or display the data.

13
Tabular Data
GEOG 3150

• A database program can also be used to easily
  create reports that display a subset of the data
  within the database.

14
Tabular Data
GEOG 3150

• A text database contains data that is separated
  into records by carriage returns and into columns
  by spaces, tabs, commas, or a some other special
  character
• Name Age Sex Height Weight
• Joe 23 M 68 161
• Mary 19 F 63 124
• Sam 20 M 71 212
• Bill 20 M 69 155
• Kip 21 M 70 171

15
Tabular Data
GEOG 3150

• A flat database contains files which contain
  records which contain fields.
• Database records are arranged in rows, and
  database fields (a.k.a. items) are arranged in
  columns.

16
Tabular Data
GEOG 3150

• Each record represents a particular instance of
  the features stored in the database.
• Each field contains a particular kind of data for
  all the records in the database. ESRI uses the
  term item instead of field.
• Each cell in a field contains that fields value
  for that particular record.

17
Tabular Data
GEOG 3150

• In a GIS feature attribute table, each row
  represents a feature and each field contains the
  specific values of a particular attribute for the
  features represented in the table.
• Feature attribute tables include (among others)
• Point attribute tables (PAT),
• Line (arc) attribute tables (AAT), and
• Polygon attribute tables (PAT).

18
Tabular Data
GEOG 3150

• Not all GIS database tables are feature attribute
  tables.
• Any number of additional tables in the database
  can be linked to a feature attribute table or to
  each other through a key field. More on that
  later.

19
Tabular Data
GEOG 3150

• Unlike a spreadsheet
• All of the data in a database field must contain
  the same type of information (integer, real,
  text, date, etc.),
• Each cell in a field must be identically-defined
  (type, length, precision, etc.), and
• Each record must contain a single cell for every
  field in the table.

20
Tabular Data
GEOG 3150

• The advantages of flat databases are that they
  are
• Easy to set up,
• Easy to understand, and
• Easy to manipulate with software.

21
Tabular Data
GEOG 3150

• The disadvantages of flat databases are that
  they
• Often require redundant data storage, i.e. the
  same data values are contained in many individual
  cells,
• A single record may not fit within the available
  display area, and
• Hardcopy output may be difficult to read due to
  line wrap.

22
Tabular Data
GEOG 3150

• A relational database differs from a flat
  database because the tables it contains are
  linked to each other by the values contained in
  one or more key fields.

23
Tabular Data
GEOG 3150

• The advantages of relational databases and
  relational database management systems (RDBMS)
  are that they
• Reduce redundant data entry and storage,
• Allow faster searches,
• Allow individual tables to be separately
  maintained,
• Can be used to create forms and reports that
  display only the data you want to see.
• Can be used to create queries to answer
  questions that are hard or impossible to answer
  in flat databases.

24
Tabular Data
GEOG 3150

• The disadvantages of relational databases and
  RDBMSs
• Designing efficient databases requires a
  thorough understanding of database design
  concepts, and
• It is harder for database users to understand
  how all the parts relate to each other.

25
Tabular Data
GEOG 3150

• Database management programs allow the user to
  query the database to create subsets of the data
  in order to answer questions.
• Virtually all modern RDBMSs use a common language
  to create such queries, i.e. Structured Query
  Language (SQL).

26
Tabular Data
GEOG 3150

• The syntax of SQL is obtuse and difficult to
  learn.
• Fortunately, most database programs provide forms
  that allow users to create SQL queries. Many
  database users are unaware that they are using
  SQL.

27
Tabular Data
GEOG 3150

• Databases in the ESRI product family
• Early versions of Workstation ArcInfo
• INFO A simple, standalone, relational database
  management system.
• Later versions of Workstation ArcInfo
• Simple links to commercial RDBMSs.
• ArcSDE (Spatial Data Engine) Dedicated
  intermediary software linking ArcInfo to
  commercial RDBMSs.

28
Tabular Data
GEOG 3150

• Databases in the ESRI product family (cont.)
• ArcView 1.0 through 3.x dBase files and
  databases.
• ArcGIS and ArcView 8.0 and later
• INFO databases for coverages.
• dBase databases for shapefiles.
• Microsoft Access for personal geodatabases.
• ArcSDE and commercial RDBMSs for geodatabases.

29
Tabular Data
GEOG 3150

• ArcSDE is a server software product used to
  access massively large multi-user geographic
  databases stored in relational database
  management systems (RDBMSs).
• With the release of ArcGIS 9.2, ArcSDE is an
  integrated part of ArcGIS Desktop.
• http//www.esri.com/software/arcgis/arcsde/index
  .html

30
Tabular Data
GEOG 3150

• ArcSDEs primary role is to act as the GIS
  gateway to spatial and attribute data stored in a
  RDBMS.
• ArcSDE also provides software services that
• Enhance data management performance,
• Extend the range of data types that can be
  stored in a RDBMS,
• Enable schema portability between supported
  RDBMSs, and
• Offer configuration flexibility.

31
Tabular Data
GEOG 3150

• All of the commercial RDBMSs supported by ArcSDE
  offer robust
• Database management functions, and
• Database access security functions.
• This means that ESRI can avoid the need to
  incorporate similar functionality into each of
  their software products that can be linked to
  databases stored in RDBMSs.

32
Tabular Data
GEOG 3150

• In ArcGIS 9.1 and earlier versions, ArcSDE was
  not needed for personal geodatabases which use
  Microsoft Access as their database management
  system, but was required to manage geographic and
  attribute data stored in any one of four
  commercial databases
• IBM's DB2 Universal Database,
• IBMs Informix Dynamic Server,
• Oracle, and
• Microsoft SQL Server.

33
Tabular Data
GEOG 3150

• Additionally, ArcSDE can be used to serve spatial
  data to
• Desktop products including ArcGIS, ArcReader,
  ArcView, ArcEditor, and ArcInfo,
• Desktop applications developed with ArcObjects,
  ArcGIS Engine, and ArcGIS Server.
• Internet clients through Internet Map Server
  (ArcIMS), and
• ESRI's file-based data formats using ArcSDE for
  Coverages.

34
Tabular Data
GEOG 3150

• Part II
• Working with Tabular Data in ArcGIS

35
Tabular Data
GEOG 3150

• The tables used with ArcGIS are of two types
• Tables associated with spatial data, including
  feature attribute tables, and
• Tables whose records are not associated with
  spatial data. Examples include
• Lookup tables, and
• Summary statistical tables.

36
Tabular Data
GEOG 3150

• A lookup table is often used to key one or more
  data items to the records in a spatial table.
• Statistical tables are often derived from
  statistical analysis of the attributes associated
  with spatial features.

37
Tabular Data
GEOG 3150

• It is often necessary to link the records in one
  table to records in another table and this is
  possible if both tables contain fields with
  common values.
• For example, a table containing demographic data
  might be joined to a polygon attribute table
  (PAT) to link data in the demographic table to
  the features in the PAT.

38
Tabular Data
GEOG 3150

• The links between tables are one of two types
• Relates which temporarily link records in the
  two tables, and
• Joins which permanently joins items from a
  source table to the items in a destination table.

39
Tabular Data
GEOG 3150

• Before deciding whether to relate or join two
  tables, the relationship between the two table
  should be considered. These relationships
  include
• Identifying a key field or key fields,
• Identifying how the key values are related in
  the two tables.

40
Tabular Data
GEOG 3150

• A key field is any field that is contained in
  both tables. The item type (integer, real,
  character, etc.) must be the same in both tables,
  but the item names can be different.

41
Tabular Data
GEOG 3150

• Sometimes it is possible to associate two tables
  even when no key field exists.
• For example, if the first table contains a field
  containing names in the form last, first and
  the second table contains two fields containing
  the same names but in two fields, a new item can
  be created in the second table that contains both
  names in the required format. This becomes the
  key field.

42
Tabular Data
GEOG 3150

• It is not uncommon to need to concatenate values
  from several fields to create a useful key field.
• What constitutes a useful key field depends on
  your purpose and how the values in the tables are
  related to each other.

43
Tabular Data
GEOG 3150

• The key fields in two tables can be related in
  one of four ways
• A one-to-one relationship,
• A many-to-one relationship, or
• A one-to-many relationship,
• A many-to-many relationship.

44
Tabular Data
GEOG 3150

• In a one-to-one relationship, each record in the
  first table has one and only one related record
  in the second table.

45
Tabular Data
GEOG 3150

• In a many-to-one relationship, potentially more
  than one record in the first table has one
  related record in the second table.
• The second table here is commonly referred to as
  a lookup table.

46
Tabular Data
GEOG 3150

• Lookup tables are often used to reduce data
  storage requirements and reduce the possibility
  of data errors.
• Its easier to check a short lookup table for
  errors than to scan every record in a large
  dataset, and its easier to edit a single value
  in a lookup table than to edit all of the
  redundant values that might be found in a larger
  table.

47
Tabular Data
GEOG 3150

• In a one-to-many relationship, each record in the
  first table may have more than one related record
  in the second table.

48
Tabular Data
GEOG 3150

• In a many-to-many relationship, more than one
  record in the first table may be related to more
  than one record in the second table.
• For example, in a land ownership database, some
  parcels have more than one owner and some people
  own more than one piece of land.

49
Tabular Data
GEOG 3150

• Many-to-many relationships are often implemented
  with three tables. In this case a Parcel Owners
  table contains all the unique relationships
  between parcels and their owners.

50
Tabular Data
GEOG 3150

• A join can safely be used to associate tables
  containing one-to-one and many-to-one
  relationships.
• However, these tables can also be associated with
  a relate. The advantage of a join in this case is
  that the association is permanent within a map
  document (i.e. an ArcGIS project file).

51
Tabular Data
GEOG 3150

• In a join, the source table contains unique
  values in the key field and these are matched to
  the identical values in the destination table.
• The original tables are not physically joined to
  each other, but they appear to be physically and
  permanently joined within the map project where
  the join operation was performed.

52
Tabular Data
GEOG 3150

• This is not the case for joins in all other
  software packages.
• For example, a join performed in ArcInfo
  physically appends the items contained in the
  source table to the items contained in the
  destination table, and only the source table
  remains in its original form.

53
Tabular Data
GEOG 3150

• In the case of a many-to-one relationship, the
  destination table potentially contains
  non-unique values in the key field, but the
  source table has only unique values in that field.

54
Tabular Data
GEOG 3150

• Therefore, it is still possible to join the
  records in the source table to the records in the
  destination table without confusion.

55
Tabular Data
GEOG 3150

• If an attempt is made to join tables containing
  one-to-many and many-to-many relationships, the
  software cant know which one of the many records
  containing the same values in the source table
  should be joined to a particular record in the
  destination table.

56
Tabular Data
GEOG 3150

• A relate allows the software to associate
  multiple records in the source table with each
  record in the destination table and should always
  be used to associate table containing one-to-many
  and many-to-many relationships.
• In these cases, each record in the destination
  table is associated with every record containing
  a matching key in the source table.

57
Tabular Data
GEOG 3150

• Relates are temporary. They are not saved in the
  map document in ArcGIS.
• Again, though, this doesnt apply to all relates
  in all software packages. Named relates can be
  save in ArcInfo.
• Both join and relate are used differently in
  different programs.