1 / 26

Database Management Issues of interest to Address Databases

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.

dakota
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Management Issues of interest to Address Databases

  2. Database Overview Agenda • Database Components • Example Data Types • Table Indexes • Domains • Joins and Views • Foreign and Primary Keys

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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.

  9. 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

  10. 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...

  11. 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

  12. 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.

  13. 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

  14. 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.

  15. Segment Table in Graphics Street Name Table Segment Table Joining Tables (example) 1:1 Master Address File

  16. 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

  17. 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”.

  18. 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”.

  19. 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).

  20. 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)

  21. 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.

  22. 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.

  23. 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).

  24. 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.

  25. 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.

  26. 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

More Related