Project 2 Presentation Spatial Databases GIS Case Studies Elizabeth

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

Agenda Spatial Database Basics Geographic Information Systems (GIS) Basics Case Studies UC Berkeley: IEOR 215 2

Spatial Database Basics Common applications UC Berkeley: IEOR 215 3

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 UC Berkeley: IEOR 215 4

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 UC Berkeley: IEOR 215 5

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 UC Berkeley: IEOR 215 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 user determining current relative location of businesses UC Berkeley: IEOR 215 7

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 UC Berkeley: IEOR 215 8

Interface to spatial application – Core spatial functionality – Interface to DBMS Taxonomy Data types Operations Query language Algorithms DBMS – Core Spatial Functionality Interface to DBMS SDBMS has three layers: Spatial application SDBMS works with a spatial application at the front end and a DBMS at the back end Interface to spatial application SDBMS Three-layer Structure Access methods UC Berkeley: IEOR 215 9

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 UC Berkeley: IEOR 215 10

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) UC Berkeley: IEOR 215 11

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 Shape Integer, 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); UC Berkeley: IEOR 215 12

Example Spatial Query Find all the counties that border on Contra Costa county SELECT C1.Name FROM WHERE County C1, County C2 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 WHERE County C, River R Intersect(C.Shape, R.Shape) 1 AND R.Name ‘Merced’; CREATE TABLE County( CREATE TABLE River( Name varchar(30), Name varchar(30), State varchar(30), Source varchar(30), Pop Integer, Distance Integer, Shape Polygon); Shape LineString); UC Berkeley: IEOR 215 13

Geographic Information System (GIS) Basics Common applications UC Berkeley: IEOR 215 14

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 UC Berkeley: IEOR 215 15

GIS Data Format Modeling 1. Vector – geometric objects such as points, lines and polygons 2. Raster – array of points Analysis 1. Geomorphometric –slope values, gradients, aspects, convexity 2. Aggregation and expansion 3. Querying Integration 1. Relationship and conversion among vector and raster data UC Berkeley: IEOR 215 16

GIS – Data Modeling using Objects & Fields (0,4) Pine (0,2) Fir (0,0) Object Viewpoint 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) Oak (2,0) (4,0) Field Viewpoint Pine: 0 x 4; 2 y 4 Fir: 0 x 2; 0 y 2 Oak: 2 x 4; 0 y 2 Source: “Spatial Pictogram Enhanced Data Models pg 79 UC Berkeley: IEOR 215 17

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 OID’s 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 UC Berkeley: IEOR 215 18

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 N Derived Shapes 0, N * User Defined Shape ! UC Berkeley: IEOR 215 19

Extending the ER Diagram with Spatial Pictograms: State Park Example Standard ER Diagram Spatial ER Diagram LineID RName Supplies to RName River PolygonID FoName Touches FacName River Supplies to FoName FacName Facility Facility Belongs to PointID Forest Forest Belongs to Within Monitors Fire Station FiName Fire Station Monitors FiName PointID UC Berkeley: IEOR 215 20

Case Studies Specific applications of spatial databases UC Berkeley: IEOR 215 21

Case Study: Wetlands Objective: To predict the spatial distribution of the location of bird nests in the wetlands Location of Nests Location: Darr and Stubble on the shores of lake Erie in Ohio A Focus 1. Vegetation Durability 2. Distance to Open Water 3. Water Depth A A Actual Pixel Locations P Assumptions with Classical Data mining 1. Data is independently generated – no autocorrelation 2. Local vs. global trends P P A Case 1: A A Possible Prediction Spatial accuracy 1. Predictions vs. actual 2. Impact P P A A P Case 2: A Possible Prediction Source: What’s Spatial About Spatial Data Mining pg 490 UC Berkeley: IEOR 215 22

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” UC Berkeley: IEOR 215 23

Case Study: Green House Gas Emission Estimations (cont) Results: – Able to quantitatively compare emission changes between 1986 to 1992: o Determined that there was a loss of 8.3 million tons of Carbon o Proportion of primary forest decreased from 19.3% to 12.5% o 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 UC Berkeley: IEOR 215 24

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 UC Berkeley: IEOR 215 25

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 UC Berkeley: IEOR 215 26

UC Berkeley: IEOR 215 27

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’; UC Berkeley: IEOR 215 28

Problem 2 Solution ClosetID Length Type Hallway Closet RoomID Accesses HallI D Belongs T o Room Belongs T FurnID o Belongs To Furniture Name UC Berkeley: IEOR 215 29