Chapter 9 - Creating and Maintaining Geographic Databases

Database

An integrated set of data on a particular subject

Dataset (files)

• Ideal for single-user performance

• More simple and much lower cost requiring no specialized software and maintenance

• Common file structures can be employed and customized

• More capable for complex data types requiring specialist indexes and access algorithms

Database (DBMS)

• Reduced redundancy for data with al data collected together

• Reduced maintenance costs for organization of large datasets

• Applications become data independent

  • Same data shared among a variety of different applications

• Data sharing between users

• Facilitates security and standards for data

• Better manage a large number of concurrent users

DBMS Capabilities

Data model, Data load capability, Indices (data structure that speeds up searching, Query language, Security, Controlled update, Backup and recovery, Database administration tools, Applications, APIs

1970 Ted Codd Rules

• Only one value per cell (row/column)

• All values in a column are about the same subject

• Each row is unique

• No significance to column sequence

• No significance to row sequence

SQL

Structured or standard query language

DDL

Data definition language

DML

Data manipulation language

3 parts of a DML SQL

SELECT, FROM, and WHERE

SELECT

Defines the columns to be displayed (TableName.ColumnName)

FROM

Used to identify and join the concerned tables

• Identify and join the tables with INNER JOIN requiring only matching records between multiple tables to be considered

WHERE

Used to select the rows from the columns using the constraints by identifying the required properties and using operators such as “AND”

(E.g., (((Tab10_3a.AssessedValue) >300000 AND ((Tab10_3b.ZoningType) = “Residential”)))

B-Tree

Very simplistic (1-D) and not ideal for geographic objects but a standard for DBMS

Grid Indices

Uses a regular grid mesh places over a layer of geographic objects

• Highest (coarsest) to lowest (finest)

• Three grid levels yield sufficient all-around performance

• One of the simplest and most robust methods as fast to create/update and can handle a range of types an densities of data

Quadtree Indices

Indices that recursively divide space

Point Quadtrees

Divide space into four (irregular) rectangles based on point locations

• Root is the region as a whole

• Successive points subdivide their quadrants with point being as origin of new quadrant

Region Quadtrees

Used to index lines, areas, and rasters

R-tree Indices

Group objects using a rectangular approximation of their group location using a minimum bounding rectangle (MBR) or minimum enclosing rectangle

• Grouping of points, lines, or area objects are indexed based on MBR

MBRs

Minimum bounding rectangles can be used to generalize a set of data by replacing the geometry of each of the objects in the box with two pairs of coordinates defining the box. A second use is for fast searching.

WYSIWYG

“What you see is what you get”, the data on your screen while your editing is the exactly how it will be displayed and stored.
When you trace a river, draw features, edit features, they will appear the same on the map
Without WYSIWYG e.g., you’d enter coordinates and wait to see the display

Transactions

A group of edits to a database that are treated atomistically as either completely committed or rolled back (not committed at all)

Locking (pessimistic) Editing Approach

Simple means for allowing multiuser transactions by preventing edits to affected database records during the transaction time under the assumption that other edits will conflict therefore must be avoided

• Appropriate for very short durations

• Geographic long transactions may extend for long durations such as hours, weeks, and months

Optimistic Editing Approach

Assumes the chance of concurrent edits actually conflicting is low and can be handled with software application if/when they occur