CE 250 - Introduction to Surveying and

Geographic Information Systems

Donald J. Leone, Ph.D., P.E.

eLearning Version

Lecture 3

Introduction

What are databases and database management systems (DBMS)?

What is a relational data base model?

How are databases linked with GIS? How do we get paper maps into the

computer? How can we edit and convert data?

Databases Spatial Data – “Where things are” Attribute Data – “What things are” Attribute Data – Stored in databases

Def: Database – “A set of structured data – usually in table form”, or

“ A Collection of related data”

Traditional Database Example

Traditional Databases Some Problems

Duplication of Data High Maintenance Costs Data Sharing Difficulties Lack of Security and

Standards

Computer Based Databases

Different data access methods will be available.

Are independent of application. Unnecessary duplication of data –

minimized. Access controlled and centralized. Maintaining and Updating

relatively easy. Can ask questions - “query”

Database Approach

Data

Application

Database Management System

Database

DBMS

Hotel

Booking

Travel

Arrangements

Ski School

Booking

Database Management Systems

Functions File Handling/management Adding/deleting/updating

records Extraction of data (sorting,

querying) Maintenance (security, backup) Application building

Data Base Table

PIN Owner Address

SaleDate

Acres

Zone Code

Zoning

P101

Wang 101 Oak St. 1-10-98 1.0 1 Res.

P101

Chang 200 Maple St.

1-10-98 1.0 1 Res.

P102

Smith 300 Spruce Rd.

10-6-68 3.0 2 Com.

P102

Jones 100 Ash St. 10-6-68 3.0 2 Com.

P103

Costello

206 Elm St. 3-7-97 2.5 2 Com.

P104

Smith 300 Spruce St.

7-30-78 1.0 1 Res.

Separate Data Tables – A Relational Database

PIN

PIN Owner name

Owner name

Zone Code

Zone Code

Creating a New Table

JOIN

Key Field

Joined Table

PIN Owner Address

P101

Wang 101 Oak St.

P101

Chang 200 Maple St.

P102

Smith 300 Spruce Rd.

P102

Jones 100 Ash St.

P103

Costello

206 Elm St.

P104

Smith 300 Spruce St.

Creating New Tables – “The Query”

Standard Query Language – SQL Generate New tables Advantages:

Completeness, Simplicity Style, Wide Application

Disadvantages: Slow, Difficult to implement Can’t Handle geographic concepts, i.e.

“near to”

Parcel Table

PIN SaleDate

Acres

Zone Code

P101

1-10-98 1.0 1

P102

10-6-68 3.0 2

P103

3-7-97 2.5 2

P104

7-30-78 1.0 1

“Which Parcels (PINS) have 2 or more acres?”

Create a New Table Using SQL

Query: “Acres” =>2.0

Result:PIN SaleDate

Acres

Zone

Code

P102 10-6-68

3.0 2

P103 3-7-97 2.5 2

Creating a Database

1. Data Investigation – Fact finding.

2. Data Modeling – Relationships between entities and attributes – Define Tables.Define Tables.

3. Database Design – Fit data modeling to software at hand.

4. Database Implementation – Filling in the actual data.

Data ModelingEntity Attribute Modeling

(EAM)

1. Identification of Entities

2. Identification of the Relationship between entities (1:1, 1:M, M:1, M:N)

3. Identification of the Attributes of the entities

4. Development of the Tables

EAM for Ski Resort

1. Entitles – Hotels, Travel Companies, Ski Schools, Visitors

2. Relationshipsa) Many visitors stay at One hotel (M:1)b) One Travel Co, organizes for Many visitors (1:M)c) One Ski School teaches Many visitors (1:M)d) Different Travel Co.s may use Different

Ski Schools (M:N)

EAM DiagramN

a) Many visitors stay at One hotel (M:1) b) One Travel Co, organizes for Many

visitors (1:M) c) One Ski School teaches Many

visitors (1:M) d) Different Travel Co.s may use

Different Ski Schools (M:N)

a

c

d

b

Attributes of the Entities

HOTEL (Hotel ID, Name, Other Attributes)TRAVEL CO. (Travel Co. ID, Travel Co.

Name, Other Attributes)SKI SCHOOL (Ski School ID, Ski School

Name, Other Attributes)VISITOR (Visitor ID, Visitor Name, Hotel

ID, Travel Co. ID, Ski School ID, Other Attributes)

LINK (Travel Co. ID, Ski School ID)

Ski

Reso

rt

Tab

les

Linking Spatial and Attribute Data

Database Applications

Single

User/PC

Large Corporate Databases

Large Computers

Control Access

Manage Data

Security

Different Sites

Simple Software

Little Grey Cells Quiz

SQL stands for standard question link. T or F

One of the functions of a DBMS is to

allow several applications access to the data. T or F

Give one advantage of a computer database over a “traditional” database.

Break!

Getting the Data Into The Computer

Data Input (Encoding) and Editing

ANALOG DIGITAL What is data encoding?

How are paper maps digitized? How are paper maps scanned?

Methods of Data Editing and Conversion

Data Capture

Editing/Cleaning

Re-Projection

Generalization

Edge Matching and Rubber Sheeting

Layering

Maps Satellite Data Digital Data Tabular Data Soft Ideas

Digitizing Scanning Data Transfer Key Coding

Integrated GIS Database

Data Editing

Data Encoding

Methods

Data Encoding Methods

Digitizing Tracing over a map with a cursor.

Mechanical Device with a Human Operator.

Most errors operator induced.

Produces A Vector Map.

Data Encoding Methods

Scanning

Some problems with scanning (automatic digitizing)

Distortion. Automatic scanning of unwanted

images. Produces a raster image- can be

vectorized with some problems. Amount of editing required to produce

suitable spatial data.

UT Hall

HJG Center

Sports Center

University of Hartford Aerial Photo

Ground Truthing Points

ScanningOriginal Paper

Map

Scanned Image

Data Encoding Methods

Electronic Data Transfer

What data exist? How much they cost? What format will it be in?

Pay off – Considerable time and effort saved!

Data Editing MethodsDetecting and Correcting

Errors

Missing or Duplicate Features Mislocated Features Missing or Duplicated Labels Unwanted Results of

Digitizing or Scanning Noise

Errors in Vector Data

Errors in

Raster Data

Noise

Original “noisy” data

3 x 3 Mean Filter

9 x 9 Mean Filter

Producing a Common Reference

Re-Projection

Transformation

Generalization

Producing a Common Reference

Re-ProjectionAll data needs to be referenced to

the same projection.

Trying to Overlay Different Projections

Cylindrical Conic

Producing a Common Reference

Re-ProjectionAll data needs to be referenced to the same projection.

TransformationAll data needs to have the same origin.

Producing a Common Reference

Re-ProjectionAll data needs to be referenced to the same projection.

TransformationAll data needs to have the same origin.

GeneralizationAll data needs to be set to the same scale.

Remaining Problems After Re-Projection, Transformation, and Generalization

Edge Matching

Remaining Problems After Re-Projection, Transformation, and Generalization

Rubber Sheeting

An Integrated DatabaseSki Resort Example

Layer Name Source Data Model

Infrastructure Scanned – 1:5000

Raster

Hotels Survey Data Vector

Ski Schools Survey Data Vector

Weather Stations

GPS Data Vector

An Integrated DatabaseSki Valley Example

Layer Name Source Data Model

Roads Digitized 1:25000

Vector

Ski Trails DigitizedAerial Photos

Vector

Ski Resort Boundary

Digitized 1:25000

Vector

Topography File Transfer1:25000 DTM

Vector/Raster

Land Use Satellite Image Raster

Summary

What are databases and data base management systems (DBMS)?

What is a relational data base model?

How are databases linked with GIS? How do we get paper maps into the

computer? How can we edit and convert data?

What’s Next

Up to now – Data Formation

Data Analysis – Decision Making