Transcript No Slide Title

Creating and Maintaining
Geographic Databases
Outline
•
•
•
•
•
•
Definitions
Characteristics of DBMS
Types of database
Relational model
SQL
Spatial databases
Definitions
• Database – an integrated set of data
(attributes) on a particular subject
• Geographic (=spatial) database database containing geographic data of a
particular subject for a particular area
• Database Management System (DBMS) –
software to create, maintain and access
databases
A GIS can answer the question:
What is where?
• WHAT: Characteristics of attributes or
features.
• WHERE: In geographic space.
A GIS links attributes to
spatial data
Attribute Data
Flat File
Relations
Map Data
Point File
Line File
Area File
Topology
Theme
Flat File
Attribute
Attribute
Attribute
Record
Value
Value
Value
Record
Value
Value
Value
Record
Value
Value
Value
Advantages of Databases
over Files
• Avoids redundancy and duplication
• Reduces data maintenance costs
• Applications are separated from the data
– Applications persist over time
– Support multiple concurrent applications
• Better data sharing
• Security and standards can be defined and
enforced
Disadvantages of Databases
over Files
• Expense
• Complexity
• Performance – especially complex data
types
• Integration with other systems can be
difficult
Types of DBMS Model
•
•
•
•
•
Hierarchical
Network
Relational - RDBMS
Object-oriented - OODBMS
Object-relational - ORDBMS
Historically, databases were
structured hierarchically in
flat files...
Relational Databases rule now
Characteristics of DBMS
• Data model support for multiple data
types
– e.g MS Access: Text, Memo, Number,
Date/Time, Currency, AutoNumber, Yes/No, OLE
Object, Hyperlink, Lookup Wizard
• Load data from files, databases and other
applications
• Index for rapid retrieval
Characteristics of DBMS
• Query language – SQL
• Security – controlled access to data
– Multi-level groups
• Controlled update using a transaction
manager
• Backup and recovery
Characteristics of DBMS
• Applications
– Forms builder
– Reportwriter
– Internet Application Server
– CASE tools
• Programmable API
Role of DBMS
System
Task
Geographic
Information
System
•
•
•
•
•
Data load
Editing
Visualization
Mapping
Analysis
Database
Management
System
•
•
•
•
Storage
Indexing
Security
Query
Data
Relational DBMS
• Data stored as tuples (tup-el),
conceptualized as tables
• Table – data about a class of objects
– Two-dimensional list (array)
– Rows = objects
– Columns = object states (properties, attributes)
Table
Column = attribute
Row = object
Vector feature
Relational DBMS
• Most popular type of DBMS
– Over 95% of data in DBMS is in RDBMS
• Commercial systems
–
–
–
–
–
–
IBM DB2
Informix
Microsoft Access
Microsoft SQL Server
Oracle
Sybase
Relational Join
• Fundamental query operation
• Occurs because
– Data created/maintained by different users, but
integration needed for queries
• Table joins use common keys (column
values)
• Table (attribute) join concept has been
extended to geographic case
Relational Databases
SQL
• Structured (Standard) Query Language –
(pronounced SEQUEL)
• Developed by IBM in 1970s
• Now de facto and de jure standard for
accessing relational databases
• Three types of usage
– Stand alone queries
– High level programming
– Embedded in other applications
Types of SQL Statements
• Data Definition Language (DDL)
– Create, alter and delete data
– CREATE TABLE, CREATE INDEX
• Data Manipulation Language (DML)
– Retrieve and manipulate data
– SELECT, UPDATE, DELETE, INSERT
• Data Control Languages (DCL)
– Control security of data
– GRANT, CREATE USER, DROP USER
Spatial Search:
Gateway to Spatial Analysis
• Overlay is a spatial retrieval operation
that is equivalent to an attribute join.
• Buffering is a spatial retrieval around
points, lines, or areas based on distance.
Overlay
Image courtesy of K. Foote/M. Lynch, UT-Austin
Overlay like an attribute join
Types of overlay operations
• Union
• Intersect
• Identity
• Max
• Min
Etc.
Union
• computes the geometric intersection of two
polygon coverages. All polygons from both
coverages will be split at their intersections
and preserved in the output coverage.
Intersect
• computes the geometric intersection of
two coverages. Only those features in the
area common to both coverages will be
preserved in the output coverage.
Identity
• computes the geometric intersection of two
coverages. All features of the input coverage,
as well as those features of the identity
coverage that overlap the input coverage, are
preserved in the output coverage.
Buffer
Complex Retrieval: Map Algebra
• Combinations of spatial and attribute
queries can build some complex and
powerful GIS operations, such as
weighting.
• Weighted overlay analysis really just
complex
• retrieval.
Map Algebra
Map Algebra
Combined
with