ITM-513

Spatial Database
Harry Imantho, M.Sc

Bogor, Sept 22, 2016

Thursday, September 22, 16

 Text Books
Spatial Database Systems
By Albert K.W. Yeung and G. Brent Hall
Spatial Databases : A Tour
By Shashi Shekhar and Sanjay Chawla
Spatial Databases: with application to GIS
By Philippe Rigaux, Michel Scholl,
and Agnes Voisard

Thursday, September 22, 16

 Is it GIS Data?

Thursday, September 22, 16

 Is it GIS Data?
Yes, if......?

Thursday, September 22, 16

 Is it GIS Data?
Yes, if......?

No, if......?

Thursday, September 22, 16

 Is it GIS Data?
Yes, if......?
it should has:
Geo-reference
Information on its objects
contained
Can be identified/classified into
two datatypes, either Raster or
Vector

No, if......?

Thursday, September 22, 16

 Is it GIS Data?
Yes, if......?
it should has:
Geo-reference
Information on its objects
contained
Can be identified/classified into
two datatypes, either Raster or
Vector

No, if......?
It just shown us the appearance of
its objects contained
-> Simple way - Its just a picture
Thursday, September 22, 16
 Is it GIS Data?

Thursday, September 22, 16

 Is it GIS Data?

GIS Data is a collection of

spatially referenced data
that acts as a model of
reality.
Thursday, September 22, 16
 Whats Database?

Thursday, September 22, 16

 Whats Database?
Methods To organize
Techniques Data

Thursday, September 22, 16

 Whats Database?
Methods To organize
Techniques Data

Database an integrated set of data on a particular subject. Can

include spatial and non-spatial and temporal data.
Database design stages: Conceptual, Logical and Physical

Thursday, September 22, 16

 Whats Database?
Methods To organize
Techniques Data

Database an integrated set of data on a particular subject. Can

include spatial and non-spatial and temporal data.
Database design stages: Conceptual, Logical and Physical

Can you distinguish between database

and files?
Thursday, September 22, 16
 Example of Applications

Small and Medium Enterprises Kota Bogor

SEAMEO Database on Education
Spatial Information System and Monitoring PTPN 4 Medan
Spatial Dynamic Model for Regional Development

Thursday, September 22, 16

 Your Interest in Spatial Databases
Spatial databases
provided the essential logic and structure
for a host interesting and creative applications, to support
planning, management and decision making. (e.g. natural
resources accounting, development planning, emergency
services routing, hospital placement, spatial modeling, game
environments).
work with many other technologies (e.g. Internet ,wireless networks,
and GPS.)
--> Great source of ideas for your final projects (or master's thesis).

Thursday, September 22, 16

 Your Interest in Spatial Databases
Spatial Databases could
integrate with other applications
and data

HTML Viewer
Java Viewer GIS Desktop
(Internet) Applications

Wireless
Mobile
Devices
Network
Custom
Applications

Map Renderer
Spatial Server Side
DB Applications

Thursday, September 22, 16

 What is a Spatial Database?
A spatial database is a database that is optimized to store and query data
related to objects in space; including points, lines and polygons.
Typical (ordinary/common) databases can understand various numeric and
character types of data only,
Spatial Database needs additional functionality to process spatial data types.
These are typically called geometry or feature (Geometry data type).
Geographic objects have two components
Description non-spatial attributes
Spatial component spatial attributes
Geometric attributes such as location and shape
Topological attributes such as adjacency (relation among features)

Thursday, September 22, 16

 Why Spatial Databases?
Common goals of a DBMS is a way of storing information to:
enforces consistency (avoids redundancy),
facilitates access; authorization, concurrency access
Allows users to relate data from multiple tables
Enable Query Processing
Queries to databases are posed in high level declarative manner (high
level programming) --> using SQL (Structured Query Language).
SQL is the lingua-franca in the commercial database world.
SQL3/OGIS1 supports several spatial data types and operations. We will
study the Open Geospatial Consortium (OGC) Standards.

Thursday, September 22, 16

 Why Spatial Databases?

Thursday, September 22, 16

 Types of Data Stored in Spatial
Databases
Three-dimensional examples
Weather
Cartesian coordinates (3-D)
Satellite images

Two-dimensional examples
Cartesian coordinates (2-D)
Networks
Direction

Thursday, September 22, 16

 Spatial Database Management
System
Spatial Database Management System (SDBMS) is a system that
provides the capabilities of a traditional database management system
(DBMS) while allowing special storage and handling of spatial data.
A SDBMS is a software module that
can work with an underlying DBMS
supports spatial data models, spatial abstract data types (ADTs)
Supports querying language specific to spatial data types
supports spatial indexing, efficient algorithms for processing spatial operations, and
domain specific rules for query optimization (provides handling of spatial data and
operations)

Thursday, September 22, 16

 Concept of Spatial DBMS

Added

Enhanced

Added

Thursday, September 22, 16

 Spatial DBMS Example
Consider a spatial dataset with:
County boundary,
Census block - name, area, population, boundary
Storage in database would be:
create table census_blocks ( name string, area float, population number,
boundary polygon );
(0,1) (1,1)
cencus_blocks
Name Area Population Boundary
1050 1 1800 MultiPolygon((0,0),(0,1),(1,1),(1,0))
1050

(0,0) (1,0)
INSERT INTO boston_buildings(name, area, population, boundary) VALUES('some name', 'some area', 'some
pop', ST_GeomFromText('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326) )
Thursday, September 22, 16
 Value of SDBMS
Non-spatial database management systems provide:
Persistence across failures
Allows concurrent access to data
Scalability to search queries on very large datasets which do not fit inside main
memories of computers
Efficient for non-spatial queries, but not for spatial queries, i.e:
Non-spatial queries:
List the names of all bookstore with more than ten thousand titles.
List the names of ten customers, in terms of sales, in the year 2001.
Spatial Queries:
List the names of all bookstores with ten miles of Minneapolis
List all customers who live in Tennessee and its adjoining states

Thursday, September 22, 16

 Value of SDBMS Users,
Application Domains
Many important application domains have spatial data and queries. Some
Examples follow:
Mobile phone users: Find the nearest gas station, ATM, hotels.
Insurance Risk Manager: Which homes are most likely to be affected in the next
great flood?
Climatologist: To verify their global warming model.
Supply manager: To find the best places to build distribution warehouses.
Farmer: To develop precision agriculture.
Urban specialist: To develop new urban/urban planning developement.

Thursday, September 22, 16

 How is a SDBMS different from a GIS?
GIS is a software to visualize and analyze spatial data using spatial analysis
functions such as
Search Thematic search, search by region, (re-)classification
Location analysis Buffer, corridor, overlay
Terrain analysis Slope/aspect, catchment, drainage network
Flow analysis Connectivity, shortest path
Distribution Change detection, proximity, nearest neighbor
Spatial analysis/Statistics Pattern, centrality, autocorrelation, indices of similarity,
topology: hole description
Measurements Distance, perimeter, shape, adjacency, direction
GIS uses SDBMS

to store, search, query, share large spatial data sets

Thursday, September 22, 16
 How is a SDBMS different from a GIS?
SDBMS focuses on
storage and management of all types of data including geographic
data,
Efficient querying,/retrieving, sharing of large spatial datasets
Provides simpler set based query operations
Example operations: search by region, overlay, nearest neighbor,
distance, adjacency, perimeter etc.
Uses spatial indices and query optimization to speedup queries over
large spatial datasets.
SDBMS may be used by applications other than GIS
Astronomy, Genomics, Multimedia information systems, ...
Thursday, September 22, 16
 How is a SDBMS different from a GIS?

SDBMS,
do not have spatial analytic and visualization tools common
to GIS,

do not have graphical data,

do not have visualization capability.

Thursday, September 22, 16

 SDBMS Component:
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

Thursday, September 22, 16

 SDBMS Three-layer Structure
Spatial application GIS Software, CAD

Interface to spatial application

Functionality
Core Spatial
Access methods
Algorithms
Query language
Operations
Data types
Taxonomy

Interface to DBMS

DBMS

Thursday, September 22, 16

 Spatial Taxonomy
Taxonomy is a system for describing and representing similarity of
properties, behaviors, relationship and constraint within a particular
domain (group).
Rules to identify identifiable objects and properties of space, and
topology model relationship.
Object model help manage identifiable things;
Specify structure or schema of a data set
Document description of data
Facilitates early analysis of some properties e.g., querying ability, redundancy,
consistency, storage space requirements, etc.

Thursday, September 22, 16

 Object model concepts
Objects: distinct identifiable things relevant to an application
Objects have attributes and operations
Attribute: a simple (e.g. numeric, string) property of an object
Operations: function maps object attributes to other objects

Example from a roadmap

Objects: roads, landmarks, ...
Attributes of road objects:
spatial: location, e.g. polygon boundary of land-parcel
non-spatial: name (e.g. Route 66), type (e.g. interstate, residential street),
number of lanes, speed limit,
Operations on road objects: determine center line, determine length, determine
intersection with other roads, ...

Thursday, September 22, 16

 Classifying Spatial objects
Spatial objets are spatial attributes of general objects
Spatial objects are of many types
Simple
0- dimensional (points), 1 dimensional (curves), 2 dimensional (surfaces)

Collections
Polygon collection (e.g. administrative boundary)

Thursday, September 22, 16

 An example...

Thursday, September 22, 16

 Spatial Types:
Open Geospatial Consortium (OGC)
Simple Features for SQL

Thursday, September 22, 16

 Spatial Types OGC Simple
Features for SQL Composed

Sub Type
Geometry SpatialReferenceSystem
Relationship

Point Curve Surface GeometryCollection

LineString Polygon MultiSurface MultiCurve MultiPoint

Line LinearRing
MultiPolygon MultiLineString

Thursday, September 22, 16

 Data Types
Data types are a way to limit the kind of data that can be used
by a particular program or stored in a database table. Types
restrict the data to a certain set of values (e.g. 1,2,3,..for
Integers).
Data types also are restricted to certain operations on the
type (e.g. addition for Integers). ORDBMS, i.e: PostgreSQL can
be extended to have additional types e.g. spatial data types.
SQL comes with a range of standard data types that can be
used to represent strings, integers, etc,

Thursday, September 22, 16

 Operations OGC Simple Feature Types

Thursday, September 22, 16

 Topological Operator

0 0 1 1 1 1 1 0 0 1 0 0
0 0 1 0 0 1 1 0 0 0 1 0
1 1 1 0 0 1 1 1 1 0 0 1
disjoint contains inside equal

0 0 1 1 1 1 1 0 0 1 1 1
0 1 1 0 1 1 1 1 0 1 1 1
1 1 1 0 0 1 1 1 1 1 1 1
meet covers coveredBy overlap

Thursday, September 22, 16

 Topological Operator:
Contains Relation
Does the base geometry (small circles) contain the comparison geometry (big circles)?

Thursday, September 22, 16

 Topological Operator:
Touches Relation
Does the base geometry (small circles) touch the comparison geometry (big circles) ?

Two geometries touch when their boundaries intersect.

Thursday, September 22, 16

 Spatial Analysis Operator

Distance shortest distance

Buffer geometric buffer
Intersection points common to two geometries
Union all points in geometries
Difference points different between two geometries
SymDifference points in either, but not both of input geometries

Thursday, September 22, 16

 Within Relation
SELECT a.id, a.name, a.geom as point INTO vw_location
FROM building a, region b
WHERE st_within(a.geom, b.geom) AND b.name ='KwaZulu';

Thursday, September 22, 16

 Try this...
SELECT b.name FROM region a, region b
WHERE ST_TOUCHES(a.geom, b.geom)
AND a.name = Hokkaido;

SELECT b.gid, b.name, b.the_geom FROM region a, region b

WHERE ST_DISTANCE (a.geom, b.geom) < 100
AND a.name = Hokkaido AND b.name != Hokkaido;

SELECT gid, ST_BUFFER(geom, 100) as geom INTO vw_buffer FROM region

WHERE name = 'Hokkaido';

Thursday, September 22, 16

 Operations on themes

Find the countries of western Europe with population greater than 50 million. This is a projection on the
attribute population.

Thursday, September 22, 16

 Theme Overlay

The lower map represents the overlay of European countries and languages.

Thursday, September 22, 16

 Indexing
Indexing is used to speed up queries and locate rows
quickly
Traditional RDBMS use 1-D indexing (such as: B-tree)
Spatial DBMS need 2-D, hierarchical indexing;
R-tree
PostGIS uses R-Tree index implemented on top of GiST to
index GIS data

Thursday, September 22, 16

 R-Tree Motivation
y axis
10 m
g h
8 l
k
e f
6
i j
Boston
d
4
b a
2 c

x axis
0 2 4 6 8 10
Range query: find the objects in a given range.
E.g. find all hotels in Boston.

No index: scan through all objects. NOT EFFICIENT!

Thursday, September 22, 16
 R-Tree Example

Thursday, September 22, 16

 R-Tree:
Clustering by Proximity
E2
E1 E7

E5
E6
E4

Thursday, September 22, 16

 R-Tree
E2
E1 E7

E6

Thursday, September 22, 16

 R-Tree

Thursday, September 22, 16

 Database Architecture for GIS

Loosely Coupled
Some commercial software such as ArcInfo use this approach.
Uses a RDBMS to store 'attribute' or descriptive information e.g. the name of a
road not its geometry.
A specific module for spatial data management.
Drawbacks:
The coexistence of heterogeneous data models, which implies difficulties in
modeling use and integration.
A partial loss of DBMS techniques e.g. recovery, querying, optimization.

Thursday, September 22, 16

 Loosely Coupled
Application Programs

Relational DBMS Geometric Processing

(GIS)

Database Files

Thursday, September 22, 16

 Database Architecture for GIS

Integrated (store spatial and attribute in one DBMS)

Most commercial databases that offer facilities to handle spatial data (PostgreSQL,
Oracle, DB2) take this approach. The basic idea is to add new types and
operations to the RM as follows:
The query language is extended to manipulate spatial data as well as descriptive
data. New spatial types (point, line, and polygon) are handled as basic types by
the DBMS.
Many other DBMS functions such as query optimization, are adapted in order to
handle geo-spatial data efficiently.
Drawback: Does not provide full GIS functionality (cartography). We must use
additional software such as Geoserver to make (or render) an attractive map from
the raw vectors stored in the DBMS.

Thursday, September 22, 16

 Integrated
Spa$al&Applica$on&
(Desktop,&Web,&Mobile)&

Spa$al&Object&and&A0ribute&
stored&in&one&DBMS&

Thursday, September 22, 16

 HOW Spatial Databases Fit into
GIS
LAN
Internet
Editing
GIS
Loading Mapping
Database Web
Analysis Features Client
GIS Other

Thursday, September 22, 16

 Thank you...

Question/discussion.....

Thursday, September 22, 16

 What can PostGIS do?

PostGIS supports a geometry type which is compliant with the OGC standard
for Simple Features.
POINT( 50 100 )
LINESTRING ( 10 10, 20 20 )
POLYGON ( ( 0 0, 5 5, 5 0, 0 0 ) )
MULTIPOINT ( ( 1 1 ), ( 0 0 ) )
MULTILINESTRING ( )
MULTIPOLYGON ( )

Thursday, September 22, 16

 Discussion Questions
What makes the concept of a spatial database unique relative to
other types of databases?
Distinguish the construct of an entity from a spatial object.
Why are organizational mandates important in database design?
Give examples using (a) natural resource data and (b) socio-
economic data.
What is a database model, and why is it important for designing a
database?
Why would a database designer use a chain object rather than a
string object for representation of linear features?

Thursday, September 22, 16