Database management issues of interest to address databases l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

Database Management Issues of interest to Address Databases PowerPoint PPT Presentation


  • 154 Views
  • Uploaded on
  • Presentation posted in: General

Database Management Issues of interest to Address Databases. Database Overview Agenda. Database Components Example Data Types Table Indexes Domains Joins and Views Foreign and Primary Keys. Database Components. A database is the sum of all information you have obtained. Database.

Download Presentation

Database Management Issues of interest to Address Databases

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Database management issues of interest to address databases l.jpg

Database Management Issues of interest to Address Databases


Database overview agenda l.jpg

Database Overview Agenda

  • Database Components

  • Example Data Types

  • Table Indexes

  • Domains

  • Joins and Views

  • Foreign and Primary Keys


Database components l.jpg

Database Components

A database is the sum of all information you have obtained.

Database

Table 3

Table 1

Columns/

Fields

col1

col2

col3

col4

col5

Table 2

Table 3

Record/Row 1

Record/Row 2

Table 4

Record/Row3

Table 5

Record/Row4

Table 6

Table 7

Table 8


Sample column data types l.jpg

Sample Column Data Types

Character

Stores a maximum of 240 ASCII characters.

Integer

Stores an integer in a range -2,147,483,648 to 2,147,483,647

Smallint

Stores an integer value in the range -32,768 to 32,767

Double

Stores a real value in double precision floating point format

Real

Stores a real number value as a single precision floating point

Decimal

Stores a fixed point decimal number with a optional precision and scale

Timestamp

Stores a timestamp with ‘yyyy-mm-dd:hh:mm:ss’ format


Table indexes l.jpg

Table Indexes

  • A table index contains information from a specified table and column

  • The index allows you to sort information by column and place this information in a table

  • Indexes can be placed on columns that are frequently used in queries and have few repeating values

  • Indexes help to improve performance on queries

  • A unique index can be created on a column that will have unique values for each record


Domains and db integrity l.jpg

Domains and DB Integrity

  • A domain allows you to check the validity of an entry into a column in a database table against a corresponding set of allowable values for that column

  • Two types of domains exist

    • Range domain -- used with numeric data and consists of one or more inclusive minimum-maximum ranges

    • List domain -- used with character data and consist of a set of character strings

  • Domains are stored in a series of domain tables


Relational join l.jpg

Relational Join

  • A Join is a linkage between two tables in the database

  • Columns from each table with like data types are used to establish the join relationship

  • There must be one identical value in the joined columns in each table to complete the union

parcel_id

parcel

parcel_id

assessed_value

zone_class

school_district

land_use

mslink

mapid

parcel_no

county_name

area_sqft

owner


Database views l.jpg

Database Views

  • A view is a window that allows you to analyze selected columns of joined tables.

  • A view can be defined using either a single join or multiple join relationships (ie using several DB columns).

  • Views are used for Query, Analysis and reporting of Database Values.

  • Makes huge DB Tables more user-friendly.


Primary keys l.jpg

Primary Keys

General Guidelines

  • Should be numeric

  • Must be Unique

  • Do not change

  • The shorter the better

  • Automatically Generated is best...

Just Call them the “Record ID’s”

in a Database Table...

*

Column Names


Foreign keys l.jpg

Foreign Keys

  • Should/Must have a matching column in another table with, at least some, matching values.

  • Require extensive planning during Database Development Phase.

  • Should be unique and numeric, but don’t have to be….

Essentially the “Linkage Columns”

between Database Tables...


What s a cartographic feature l.jpg

What’s a Cartographic Feature

  • Something from the real world represented in your digital map: streets, streams, houses, trees, etc.

  • A graphic element that contains a pointer to a record in the Feature table

CAD Graphics Table

mslink

fname

fcode

ftype

table

category

fweight

flevel

fcolor

fstyle

digcmd

other...

1

road

rd1

line

22

3

2

29

0

0

Digital Map


Database linkages on cad graphics l.jpg

Database Linkages on CAD Graphics

“Old” DB Table ID

CAD File Graphics

record ID in “Old” Table

DMRS 8000 0004 0005 0000

DMRS 8000 0022 0014 0000

feature link

attribute link

The Database software will interpret the

“old linkage code” to determine what table the

graphic elements “points” to.


Relational databases example l.jpg

Relational Databases (example)

Feature Table

mslink

fname

fcode

ftype

table

category

fweight

flevel

fcolor

fstyle

digcmd

other...

1

road

rd1

line

22

3

2

29

0

0

Category Table

mslink

cname

indexname

indexlevel

3

trans

CAD File Graphics

Maps Table

mslink

mapname

category

12

road1.dgn

3

roads Table

mslink

mapid

rd_name

num_l

traffic

county

1

12

test

2

10100

morgan

MSCATALOG

Tablename

enitynum

nextocc

feature

4

7

roads

22

2


Joining tables dbms l.jpg

Street Name Table

Sides Table

Segment Table

Joining Tables (DBMS)

1:2

1: Many

Foreign Keys in each

table are used to complete

the Join Relationships from

Table to Table.


Joining tables example l.jpg

Segment Table in Graphics

Street Name Table

Segment Table

Joining Tables (example)

1:1

Master Address File


Address database design issues l.jpg

Address Database Design Issues

  • Determine your “Audience” and their needs.

  • What’s your Geographic Extent?

  • What Partnerships should be established?

  • Establish Standards Early!

  • Re-evaluate those Standards Regularly


Address tables l.jpg

Address Tables

  • Parse the entire Address Record

    • Always easier for “us average” DB users to “merge” columns, rather than “split” them.

  • Pay close attention to Primary Keys and Foreign Keys during the Design and Testing Phase.

  • Conduct a Pilot Study for the entire database structure before going “live”.


Address tables continued l.jpg

Address Tables (continued)

  • Use Domains to control user input at EVERY opportunity!

    • List Domain (valid Street Names)

    • Range Domains (valid numeric ranges)

  • If gathering new addresses from more than one source, collect them in “dummy” tables before the DB “gatekeeper” cleans them up and dumps them into the “master database”.


Address data entry l.jpg

Address Data Entry

Specific data entry recommendations include:

1) Zip code entry first, with automatic fill of State and (optionally) locality data.

2) Support on-line entry with help screens, pop-up valid values access, and immediate edits.

3) Secondary unit data entry separate from street address (optionally before street for emphasis).

4) Addresses entered with manual overrides of edits should be flagged for future review.

5) Allow search for Zip code given City and State (optional).


Recommended address edits l.jpg

Recommended Address Edits

Several types and levels of edits may be practical, depending on circumstances and business purpose.

1) Check entered data for valid abbreviations. (Abbreviation standards used by the USPS are included in Appendix B.)

2) Compare entered location(City) and State to Zipcode (based on GCS or equivalent table information).

3) Check Zipcode for validity (based on GCS or equivalent table information).

4) Compare entered address against valid addresses: Against an existing database containing addresses (within the enterprise)


Recommended address edits continued l.jpg

Recommended Address Edits(continued)

5) Verify and correct the standard use of state code, standard spelling for city; and presence of standard street type.

6) Inspect Street numbers that seem to represent ranges of addresses, such as street numbers in a range or the use of terms such as "scattered sites". (This only applies for those applications that receive addresses representing, for example, blocks of apartments).

7) Identify and correct building name substitutions for street addresses to the extent possible. Using COTS software modules, against a postal-service database of 140 million valid addresses.

8) If County Code is missing, generate County Code.


Recommended address edits continued22 l.jpg

Recommended Address Edits(continued)

9) Identify where range of latitude or longitude is more than 5 miles. Inspect and correct.

(This is a way to measure if the geocoding center is of a Zip code, rather than to a specific street address. This is unnecessary if the geocoding level is specified in a code, as is recommended).

10) Identify and delete official verbiage. For example: "Township of", "The Commonwealth of", "The Great State of".

11) Comma Check. The USPS recommends not using commas or other dividers within addresses, except the hyphen in Zip+4. The USPS further recommends all capital letters, to aid machine readability.


Recommended address edits continued23 l.jpg

Recommended Address Edits(continued)

12) Enforce Business Rules.

For example, it may be a rule that P.O. Box numbers (and equivalent) may not substitute for Street names (and equivalent) if the address is for a property in which the enterprise holds an interest (as opposed to the mailing address of an individual or organization).


Database loading tools useful for enhancing your gbf data l.jpg

Bulk Update

Attributes

Area Loader

Polygons

Length Loader

Lines

Point Loader

X, Y coords. from DB

Label Loader

from graphics to the database!

Database Loading Tools useful for enhancing your GBF data.


Third party database scrubbers l.jpg

Third-Party Database “Scrubbers”

  • Clean up un-parsed Address Databases.

  • Remove duplicate records or misspellings.

  • Can even Geocode database records for you.

  • Some provide CASS certified services for Address clean-up.


Web sites of interest l.jpg

Web Sites of Interest

  • http://www.nonprofitmailers.org/vendors/page6old.htm

  • http://www.census.gov/geo/www/tiger/vendors.html

  • http://www.nena.org/ads/prodvend.htm


  • Login