Geospatial Database Concepts and GIS Queries

1
Geo-spatial Database Concepts and GIS Queries
2
A GIS can answer the question What is where?

• WHAT Characteristics of attributes or features.
• WHERE In geographic space.

Because

• Geo-referenced spatial objects and attribute data
  of these objects
• Links between spatial objects on a layer and
  records in an attribute table.

3
A GIS links attribute and spatial data

• Attribute Data
• Flat Files
• Relations (tables)

• Map Data
• Vector
• Point File
• Line File
• Area File
• Topology
• Raster
• Value File

4
Data Models

• A logical construct for the storage and retrieval
  of information.
• GIS data models
• spatial components vector or raster data models
• attribute components Database Management Systems
  (DBMS) data models (hierarchical, network, and
  relational data models)
• The origin of DBMS data models is in computer
  science.

5
Data Model
6
Flat Files
Attribute
Attribute
Attribute
7
Hierarchical data model
USA
California
Oregon
New York
Santa Barbara
Ventura
Contra Costa
8
Network Data Model
OU
AS
Geosciences
English
History
Geography
Meteorology
Faculty A
Faculty B
9
A DBMS contains

• Data definition language (DDL)
• Data manipulation language (DML)
• Data-entry module
• Data-update module
• Data dictionary (meta-data)
• Report generator
• Query language

10
Attribute data

• Logically can be thought of as in a flat file
• Table with rows and columns
• Attributes by records
• Entries called values.

11
Database elements
Attribute_labels ID , Feature, Name ,
Surface , Lanes, Traffic , per
hour 1, Road, US 11, tarmac, 3, 113
2, Road, I 81, concrete, 4, 432 3,
Road, Lisk Bridge Road, tarmac, 2, 12,
4

• Type of value
• Range
• Missing data
• Duplicate data
• Key

12
Most popular DBMS is by relational databases.

• Based on multiple flat files (tables) for
  records, with dissimilar attribute structures,
  connected by a common key attribute.
• Two basic integrity rules
• identity integrity (a primary key)
• referencing integrity (a foreign key)

13
Relational Data Bases
File
Patient Record
Key Check-in
Check Out
Room No.
42
2/1/96
2/4/96
N763
78
2/3/96
2/4/96
N712
Purchase Record
File
Item
Date
Price
Customer
Key
Skate Board
2/1/96
49.95
John Smith
42
Baseball Bat
2/1/96
17.99
James Brown
978
File
Accident Report
Date
Injury
Name
Key
Location
2/1/96
Broken Leg
John Smith
42
75 Elm Street
2/2/96
Concussion
Sylvia Jones
654
12 State Street
2/2/96
Cut on Ear
Robert Doe
123
2323 Broad Street
14
A relational DB

• Tuples (records)
• Attributes (fields)
• Relations (tables)
• Keys
• Primary keys
• Foreign keys
• Relationships (between tables)
• one to one
• one to many
• many to one
• many to many

15
Retrieval Operations

• Searches by attribute find and browse.
• Data reorganization select, renumber, and sort.
• Compute allows the creation of new attributes
  based on calculated values.

16
DBMS queries via the query language

• sorting
• renumbering
• subsetting
• searching

17
DBMS queries via SQL

• Basic syntax
• Select (a collection of data records)
• From (a table)
• Where (logical expressions)
• Basic operations
• sorting
• renumbering (recoding/reclassifying)
• subsetting
• searching

18
Command line attribute query
compute in states population_density population
/ area lt50 records in resultgt restrict in
states where population_density gt 1000 lt20
records selected in resultgt recode
population_density 3 lt20 values recoded in
resultgt join result with states replace lt20
records changed in stategt restrict in states
where population_density gt 100 lt12 records in
resultgt recode population_density 2 lt12
values changed in resultgt join result with
states replace lt12 records changedgt compute in
states where population_density ! 3 or 2
population_density 1 lt 18 records changedgt
19
The Retrieval User Interface

• GIS query is usually by command line, batch, or
  macro.
• Most GIS packages use the GUI of the computers
  operating system to support both a menu-type
  query interface and a macro or programming
  language.
• SQL is a standard interface to relational
  databases and is supported by many GISs.

20
Spatial Retrieval Operations(GIS spatial
objects plus attribute data)

• Attribute queries are not very useful for
  geographic search.
• In a map database the records are features.
• The spatial equivalent of a find is locate,
  the GIS highlights the result.
• Spatial equivalents of the DBMS queries result in
  locating sets of features or building new GIS
  layers.

21
Identify attributes of a spatial object
22
GIS Queries

• Submit a query give a set of criteria
• Attribute queries ask for a list of records that
  fulfill given criteria (logical expressions)
  based on attribute values.
• Spatial queries ask for a list of spatial
  objects that fulfill given criteria or within a
  certain geographic area

23
The Retrieval/Query User Interface

• GIS query is usually by command line, batch, or
  macro.
• Most GIS packages use the GUI of the computers
  operating system to support both a menu-type
  query interface and a macro or programming
  language.
• SQL is a standard interface to relational
  databases and is supported by many GISs.

24
Query GUI
25
The Role of Error

• Enforcement for map data is usually by using
  topology.
• Map and attribute data errors are the data
  producer's responsibility, but the GIS user must
  understand error.
• Accuracy and precision of map and attribute data
  in a GIS affect all other operations, especially
  when maps are compared across scales.