GIS Concepts

‣ What is a table?

‣ Queries on tables

‣ Joining and relating tables

‣ Summary statistics

‣ Database storage concepts

Table terminology

Title

Field

Records

Each field is specifically defined and established before any data can be

entered.

Field definitions control the type of data that can be

stored in a field.

Types of tables

‣ Attribute table

• Stores attributes of map features

• Associated with a spatial data layer

• Has special fields for spatial information

‣ Standalone table

• Stores any tabular data

• Not associated with spatial data

• OID instead of FID

Database Management Systems (DBMS)‣ Dedicated systems for managing many data

tables

‣ Serve data for large organizations

• E.g., agencies, universities, companies, etc.

‣ Handles multi-user environments

‣ Provides enhanced security

‣ Often provide data management and analysis tools

• E.g., querying, reporting, graphing, etc.

‣ Examples: Oracle, SQL Server, etc.

Flat file DBMS

‣ Flat file

• Stores data as rows of information in files

• Simple and robust

• Inefficient for search and query

Service callsCustomers Electric

usageService

personnel

Hierarchical DBMS‣ Stores data in multiple

tables

‣ Tables have defined parent-child relationships

‣ Pre-set hierarchy of table relationships designed for specific queries

‣ Very efficient for specific queries

‣ Range of queries limited by structure

Service calls

Customers

Electricusage

Servicepersonnel

Billinghistory

Customer ID Customer ID

Employee ID Customer IDCustomer ID

Relational DBMS‣ Stores data in multiple

tables

‣ Table relationships are defined as needed

‣ Very flexible

‣ Ideal for open-ended applications when queries not known beforehand

‣ Most common type used in GIS applications

Customers

Electricusage

Service calls

Servicepersonnel

Billinghistory

Customer ID

Employee ID

Table Querying

‣ Queries select or extract records from a table based on specified conditions

• Students with GPA > 3.5?

• States with population > 1 million?

• Counties with greater population in 1990 than in 2000?

• Customers with last names beginning with “Mac” or “Mc”?

SQL: Structured Query Language

‣ Common query language

‣ Used in many DBMS environments

‣ Queries can be saved and reused

‣ Notes

• Nearly always case-sensitive

• Easy to learn

SQL Query Examples

Some Valid Queries

SELECT *FROM cities WHERE "POP1990" >= 500000

SELECT *FROM counties WHERE “BEEFCOW_92” < “BEEFCOW_87”

SELECT *FROM parcels WHERE “LU-CODE” = 42 AND “VALUE” > 50000

SELECT *FROM rentals WHERE “RENT” > 700 AND “RENT” < 1500Programs may have an interface to

help users build SQL expressionsIn most databases, SQL expressions are

case-sensitive “Smith” ≠ “SMITH”

Multiple Criteria Queries

‣ Multiple criteria queries using AND or OR

• “STATE” = ‘Alabama’ OR “STATE” = ‘Texas’

• [Value] > 5000 AND [Value] < 10000

• Note that the field name must be repeated for each condition

Searching for partial matches

‣ Sometimes you need to find one string within another rather than an exact match

• Find all customer names beginning with “Mac” or “Mc”

• Find all zip codes beginning with 0

‣ Typically uses a “wildcard” character

• *Mac* or *Mc*

• 0*

The Like Operator (Depends on data file type)

‣ “NAME” LIKE ‘%(D)%’ • Finds all of the (D) Democrats

‣ % is wildcard

‣ Ignores Don or Danforth

‣ “NAME” LIKE ‘%New %’ • Would find New Hampshire and

New York, but not Newcastle or Kennewick

Query results‣ A set of selected records that meet the

criteria

‣ Subsequent queries may use only selected records and ignore others

‣ One might…

• Export the selected records to a new table

• Calculate statistics on the selected records

• Calculate new values for the selected records

• Produce a report based on the selected records

Joining tables

Destination table Source tableCommon field

Join facts

‣ Tables must share a common field (key)

‣ Treats the two tables as a single table

‣ Temporary relationships between tables

• Original stored data is not affected

• Can be removed when no longer needed

One-to-one joins

When each record in the destination table matches exactly one record in the source table, we call it a cardinality of one-to-one.

Destination table (always imagine on the left)

Source table(always imagine on the right)

Types of Cardinality

‣ One-to-one

• States to Governors

• Husbands to wives

‣ One-to-many

• States to cities

• Districts to schools

• Many to one• Cities to states• Schools to districts

• Many-to-many• Students to classes• Stores to

customersIn evaluating cardinality, always put the destination first.

(Destination on the left)

Rule of Joining

Each record in the destination table must match one and only one record in the source table.

One to one

Destination table Source table

Many to one

One to many (When a Relate is better)

Destination table Source table

?

Violates the Rule of Joining

Record to which to join to destination is ambiguous

Must use a relate instead

Relates

‣ Similar to a join except that

• The tables remain separate

• Items selected in one table may be highlighted in the related table

Related tables

Select the New England states

Find reps of New England states