Elizabeth Sayed

1
Project 2 Presentation
Spatial Databases GIS Case Studies
Elizabeth Sayed Elizabeth Stoltzfus December 4,
2002
UC Berkeley IEOR 215
2
Agenda

• Spatial Database Basics
• Geographic Information Systems (GIS) Basics
• Case Studies

3
Spatial Database Basics

• Common applications

4
Spatial Databases Background

• Spatial databases provide structures for storage
  and analysis of spatial data
• Spatial data is comprised of objects in
  multi-dimensional space
• Storing spatial data in a standard database would
  require excessive amounts of space
• Queries to retrieve and analyze spatial data from
  a standard database would be long and cumbersome
  leaving a lot of room for error
• Spatial databases provide much more efficient
  storage, retrieval, and analysis of spatial data

5
Types of Data Stored in Spatial Databases

• Two-dimensional data examples
• Geographical
• Cartesian coordinates (2-D)
• Networks
• Direction
• Three-dimensional data examples
• Weather
• Cartesian coordinates (3-D)
• Topological
• Satellite images

6
Spatial Databases Uses and Users

• Three types of uses
• Manage spatial data
• Analyze spatial data
• High level utilization
• A few examples of users
• Transportation agency tracking projects
• Insurance risk manager considering location risk
  profiles
• Doctor comparing Magnetic Resonance Images (MRIs)
• Emergency response determining quickest route to
  victim
• Mobile phone companies tracking phone usage

7
Spatial Databases Uses and Users

• Three types of uses
• Manage spatial data
• Analyze spatial data
• High level utilization
• A few examples of users
• Transportation agency tracking projects
• Insurance risk manager considering location risk
  profiles
• Doctor comparing Magnetic Resonance Images (MRIs)
• Emergency response determining quickest route to
  victim
• Mobile phone user determining current relative
  location of businesses

8
Spatial Database Management System

• Spatial Database Management System (SDBMS)
  provides the capabilities of a traditional
  database management system (DBMS) while allowing
  special storage and handling of spatial data.
• SDBMS
• Works with an underlying DBMS
• Allows spatial data models and types
• Supports querying language specific to spatial
  data types
• Provides handling of spatial data and operations

9
SDBMS Three-layer Structure

• SDBMS works with a spatial application at the
  front end and a DBMS at the back end
• SDBMS has three layers
• Interface to spatial application
• Core spatial functionality
• Interface to DBMS

DBMS
10
Spatial Query Language

• Number of specialized adaptations of SQL
• Spatial query language
• Temporal query language (TSQL2)
• Object query language (OQL)
• Object oriented structured query language (O2SQL)
• Spatial query language provides tools and
  structures specifically for working with spatial
  data
• SQL3 provides 2D geospatial types and functions

11
Spatial Query Language Operations

• Three types of queries
• Basic operations on all data types (e.g. IsEmpty,
  Envelope, Boundary)
• Topological/set operators (e.g. Disjoint, Touch,
  Contains)
• Spatial analysis (e.g. Distance, Intersection,
  SymmDiff)

12
Spatial Data Entity Creation

• Form an entity to hold county names, states,
  populations, and geographies
• CREATE TABLE County(
• Name varchar(30),
• State varchar(30),
• Pop Integer,
• Shape Polygon)
• Form an entity to hold river names, sources,
  lengths, and geographies
• CREATE TABLE River(
• Name varchar(30),
• Source varchar(30),
• Distance Integer,
• Shape LineString)

13
Example Spatial Query

• Find all the counties that border on Contra Costa
  county
• SELECT C1.Name
• FROM County C1, County C2
• WHERE Touch(C1.Shape, C2.Shape) 1 AND C2.Name
  Contra Costa
• Find all the counties through which the Merced
  river runs
• SELECT C.Name, R.Name
• FROM County C, River R
• WHERE Intersect(C.Shape, R.Shape) 1 AND R.Name
  Merced

CREATE TABLE County( Name varchar(30),
State varchar(30), Pop Integer,
Shape Polygon)
CREATE TABLE River( Name varchar(30),
Source varchar(30), Distance Integer,
Shape LineString)
14
Geographic Information System (GIS) Basics

• Common applications

15
GIS Applications

• 1. Cartographic
• Irrigation
• Land evaluation
• Crop Analysis
• Air Quality
• Traffic patterns
• Planning and facilities management
• 2. Digital Terrain Modeling
• Earth science resources
• Civil Engineering Military Evaluation
• Soil Surveys
• Pollution Studies
• Flood Control
• 3. Geographic objects
• Car navigation systems
• Utility distribution and consumption
• Consumer product and services

16
GIS Data Format

• Modeling
• Vector geometric objects such as points, lines
  and polygons
• Raster array of points
• Analysis
• Geomorphometric slope values, gradients,
  aspects, convexity
• Aggregation and expansion
• Querying
• Integration
• Relationship and conversion among vector and
  raster data

17
GIS Data Modeling using Objects Fields
(0,4)
Pine Pine
Fir Oak
(0,2)
(0,0)
(2,0)
(4,0)
Object Viewpoint
Field Viewpoint
Pine 0ltxlt4 2ltylt4
Name Shape
Pine (0,2), (4,2), (4,4), (0,4)
Fir (0,0), (2,0), (2,2), (0,2)
Oak (2,0), (4,0), (4,2), (2,2)
Fir 0ltxlt2 0ltylt2
Oak 2ltxlt4 0ltylt2
Source Spatial Pictogram Enhanced Data Models
pg 79
18
Conceptual Data Modeling

• Relational Databases ER diagram
• Limitations for ER with respect to Spatial
  databases
• Can not capture semantics
• No notion of key attributes and unique OIDs in a
  field model
• ER Relationship between entities derived from
  application under consideration
• Spatial Relationships are inherent between
  objects
• Solution Pictograms for Spatial Conceptual
  Data-Modeling

19
Pictograms - Shapes

• Types Basic Shapes, Multi-Shapes, Derived
  Shapes, Alternate Shapes, Any possible Shape,
  User-Defined Shapes

Basic Shapes Alternate Shapes
Multi-Shapes Any Possible Shape
Derived Shapes User Defined Shape



N
0, N
!
20
Extending the ER Diagram with Spatial Pictograms
State Park Example
Standard ER Diagram
Spatial ER Diagram
LineID
RName
RName
River
Supplies_to
River
Supplies_to
PolygonID
FoName
FoName
FacName
Touches
FacName
Facility
Forest
Forest
Facility
Belongs_to
Belongs_to
PointID
Within
Monitors
Monitors
Fire Station
Fire Station
FiName
FiName
PointID
21
Case Studies

• Specific applications of spatial databases

22
Case Study Wetlands

• Objective To predict the spatial distribution
  of the location of bird nests in the wetlands
• Location Darr and Stubble on the shores of lake
  Erie in Ohio
• Focus
• Vegetation Durability
• Distance to Open Water
• Water Depth
• Assumptions with Classical Data mining
• Data is independently generated no
  autocorrelation
• Local vs. global trends
• Spatial accuracy
• Predictions vs. actual
• Impact

Location of Nests

A
A
A
Actual Pixel Locations
P
P P A

A A
Case 1 Possible Prediction

P A
P P
A A
Case 2 Possible Prediction
Source Whats Spatial About Spatial Data Mining
pg 490
23
Case Study Green House Gas Emission Estimations

• Objective
• To assess the impact of land-use and land cover
  changes on ground carbon stock and soil surface
  flux of CO2, N2O and CH4 in Jambi Province,
  Indonesia
• Methodology
• Initiated by development of land-use/land cover
  maps and followed by field measurements
• Spatial database construction development based
  on 1986 and 1992 land-use/land cover maps that
  developed from Landsat MSSR and SPOT
• Weight of sample components of the tree and
  streams, branches, twigs, etc were estimated from
  equations and literature
• Emission rates were developed by plotting and
  analyzing collected air samples
• Field data measurements and GIS spatial data were
  combined using a Look Up Table of Arc/Info.

Source Spatial Database Development for green
house gas emission Estimation using remote
sensing and GIS
24
Case Study Green House Gas Emission Estimations
(cont)

• Results
• Able to quantitatively compare emission changes
  between 1986 to 1992
• Determined that there was a loss of 8.3 million
  tons of Carbon
• Proportion of primary forest decreased from 19.3
  to 12.5
• Showed 24 of primary forest was converted into
  logged forest, shrub, cash crops
• Greenhouse gas emission varied depending on the
  site condition and season.
• Process gave impacts of greenhouse gas on the
  soil surface

25
Case Study Pantanal Area, Brazil

• Objective To assess the drastic land use
  changes in the Pantanal region since 1985
• Data Source
• 3 Landsat TM images of the Pantal study area from
  1985, 1990, 1996
• A land-use survey from 1997
• Assessment Methodology
• Normalized Difference Vegetation Index (NDVI) was
  computed for each year
• NDVI maps of the three years combined and
  submitted to multi-dimensional image segmentation
• Classified vegetation
• Produced a color composite by year that
  identified the density of vegetation

Source Integrated Spatial Databases pg 116
26
Conclusion

• Many varied applications of spatial databases
• Stores spatial data in various formats specific
  to use
• Captures spatial data more concisely
• Enables more thorough understanding of data
• Retrieves and manipulates spatial data more
  efficiently and effectively

27
(No Transcript)
28
Problem 1 Solution

• a)  Find all cities that are located within
  Marin County.
• SELECT C2.Name
• FROM County C1, City C2
• WHERE Within(C1.Shape, C2.Shape) 1 AND C1.Name
  Marin
• b) Find any rivers that borders on Mendocino
  County.
• SELECT R.Name
• FROM County C, River R
• WHERE Touch(C.Shape, R.Shape) 1 AND C.Name
  Mendocino
• c) Find the counties that do not touch on Orange
  County.
• SELECT C1.Name
• FROM County C1, County C2
• WHERE Disjoint(C1.Shape, C2.Shape) 1 AND
  C2.Name Orange

29
Problem 2 Solution
ClosetID
Length
Type
Closet
Hallway
RoomID
Accesses
HallID
Belongs_To
Room
Belongs_To
FurnID
Belongs_To
Furniture
Name