designing geodatabases part i: polygons and lines
TRANSCRIPT
Designing Geodatabases
Part I: Polygons and lines
Design of spatial databases
• As for any kind of database, we model the reality by exploring it in detail, within our scope
• In spatial databases, all objects fall into three categories:– Point, line, polygon
• Given this fact, we need to define attributes of spatial objects in the context of these three types
– That is, we need to identify the features that are of these three types in our domain
Modeling of Geodatabases• Requires us to consider:
– how data in the database are going to be used– who will handle the data– what types of queries will be executed against the data
• Use special tools to diagram geodatabases– e.g., Geodatabase Diagrammer: http://arcscripts.esri.com/– Available on texbook’s DVD (Note: it requires software and
license!)
– Instead, in this part, we will use MS Excel to put the design on special worksheets (also available on the DVD)
Need to Identify Data Types• Just like modeling of databases dealing with non-
spatial objects, modeling of geodatabases requires identifying– datatypes (point, line, polygon) and– relationships among the data types
• We also need to define domains, subtypes, and relationships for the tables
• Have to take care of data integrity, ease of editing and updating, and expansion of the database
Geodatabase• Feature classes and feature datasets allow us to
organize objects and their relationship in a spatial database
• Related feature classes are grouped together in a feature dataset
• The design of both of these depends on their behavior and relationships among objects in reality
• If few feature classes work together to create geometric network, terrain, topology, etc., they need to be put in the same feature dataset
City of Oleander• In this, and next, section, we will design a spatial database to
store and manage data related to – parcels (polygons)– sewer pipe system (lines), and – manholes and cleanouts (points)
• The DB will deal with the properties of these objects in the city of Oleander. It will deal with:– people who own properties (parcel lots)– address of the parcels (lots)– Type of pipes, etc.
• We will first design the database using design worksheets, and later develop it with ArcCatalog
Part I: Design Polygons• In this project, lots (properties, i.e., land parcels) are modeled as
polygons– Each parcel will have certain legal attributes defined by the city of
Oleander, such as:• Subdivision name, block designation, lot designation, street address, land-use
code
– Our database will include many components, e.g.,:• feature classes/tables• feature datasets• domains• relationship classes
– We will use the geodatabase (GDB) design forms which are on the textbook’s DVD
Use the DVD in the book• Insert the GIS Tutorial 3 Student Resources DVD in the optical drive of
your computer:> set up // accept defaults and download to:C:\ESRIPress
Navigate to C:\ESRIPress\GIST3\Data\GDB Design Forms• Open the Excel version of the design forms• Save (and/or print) them in a directory of your choice
– You are going to fill in the worksheets in this exercise!
• Name the worksheet: GDB Design Forms_tutorial_1_1
• Note: There are 6 pages in the Excel worksheet:– GDB feature classes, tables, domains, domains2, subtypes,
relationships
GDB feature class form• Fill the following data in the GDB form:– Geodatabase name: LandRecords– Feature dataset name: for now, leave it blank
• Let’s first work on the polygon data types– Write: Parcels under the feature class name column– Write: POLY for the type of the feature class label– Write: Property Ownership for the Alias column
– Note: Alias is a label to be used in the table of content and legend, and should be clear and descriptive
Add the Polygon feature class
Geodatabase design forms
Geodatabase name LandRecords
Feature dataset name
Feature classes:
Type Feature class name Alias
POLY Parcels Property Ownership
Parcel feature class• Attributes (fields) of the Parcel feature class:
• These fields should be designed in the Tables worksheet page
Tables worksheet
Feature class or Nulls Default Domain name or
table name Field nameField type Alias (Y/N)value
subtype field (S) or (D)
Parcels SubName Text Subdivision Name No Blk Text Block Designation No LotNo Text Lot Number No
Tables Worksheet• We learned in the design of databases not to put more than one atomic
type in each table
• So, for the address field, we should not put the whole address in one field
• For data integrity, we will use special format for address (see the worksheet)
• These will allow us to query and find parcels in certain subdivision, those that front a certain street, and use the house number to put address labels on the map
• Land use code: 2-9 letter alphanumeric code indicating how the land is being used– We need to provide a file (subtype) for these codes– A georeference will be added to point to this file
Design for Data Integrity• Make sure to have control over user data; ensure that:
– actually enter data, or– their entered data is of correct type
• add data integrity rules for values if they need to be entered
• We do this in the Table worksheet, under the Nulls, Y/N column
– Let’s allow Null only on Pre_Type, Pre_Dir, and Suffix_Dir, because people may not know these prefix or direction of streets!
Tables worksheet
Feature class or Nulls Default Domain name or
table name Field nameField type Alias (Y/N) value
subtype field (S) or (D)
Parcels SubName Text Subdivision Name No Blk Text Block Designation No LotNo Text Lot Number No Pre-Type Text Prefix Type Yes Pre-Dir Text Prefix Direction Yes House_Num Text House Number No Street_Name Text Street Name No Street_Type Text Street Type No (D) StTypeAbbrv Suffix-Dir Text Suffix Direction Yes ZIPCODE LI ZIP Code No UseCode Text Land Use Code No (D) ParcelUseCodes Georeference Text Georeference No
Use Domain for data integrity• Domain sets the list of values for text or range of values
for numbers– User data will be matched to the domain for validity
• Prevents inadvertent typos or inventive abbreviations e.g., for vacant lot, which could be Vac, V, Vcnt, Vacent, etc.
– The solution is to type acceptable values for such code (e.g., Use Code) in the Worksheet
– We do this by declaring it in the Domain column of the Table Worksheet, by adding (D) ParcelUseCode in front of Land Use Code
– Now we need to define the code in the Domain worksheet
Domains worksheet Coded values / Range
Domain name Description Field type Domain typeCode (Min) Desc (Max)
ParcelUseCodes Use Codes for Parcels Text Coded values A1 Single Family Detached A2 Mobile Homes A3 Condominium A4 TownHouses A5 Single Family Limited B1 Multifamily B2 Duplex B3 Triplex B4 Quadruplex CITY Developed City Property CITYV Vacant City Property CITYW Water Utility Property CRH Church ESMT Easement F1 Commercial F2 Industrial GOV Government POS Public Open Space PRK Park PROW Private right-of-way ROW Right-of-way SCH School UTIL Utility VAC Vacant
Set the domain for Street type• Street types also need code• U.S. Postal Service has a list of street types
– We can use a domain with the list of street type abbreviations– This is too long for us to type them in as domain– We can put them in a file, and then read the file as a domain
• Declare StTypeAbbrv in the domain name for the Street_Type and add a (D) before its name
• In the Domain worksheet, define the StTypeAbbrv under the name, Street Type Abbreviations under description, Text for field type, and Coded value for domain type
• Under Code, put the path for the file: Data\Suffix.txt– This file, which is going be added to the domain, was created from the U.S.
Parcel Service Website
Subtype• Dividing the data in the same feature class into different logical categories
with their own data integrity rules
• If datatypes are in the same geodatabase, each can be edited and managed in a single feature class
• Subtypes can be used to:– to set default values– establish unique attribute domains– set connectivity rules– establish relationship rules for each subcategory
• We want to subdivide the property type in the database into two types of lot:– Platted (divided into lots with utilities)– Unplatted (raw agricultural land) lots
Subtype …• Each subtype needs to be given subtype code and description
• The code is put in an integer field which needs to be added to the table1 Platted Property2 Unplatted Property3 Platt Pending• Pending means it is approved by the city but is waiting
the filing data from the county
Adding the Subtypes• In the Tables worksheet, add the information about the subtype at the
bottom:Tables worksheet
Feature class or Nulls Default Domain name or
table name Field nameField type Alias (Y/N) value
subtype field (S) or (D)
Parcels SubName Text Subdivision Name No Blk Text Block Designation No LotNo Text Lot Number No Pre-Type Text Prefix Type Yes Pre-Dir Text Prefix Direction Yes House_Num Text House Number No Street_Name Text Street Name No Street_Type Text Street Type No (D) StTypeAbbrv Suffix-Dir Text Suffix Direction Yes ZIPCODE LI ZIP Code No UseCode Text Land Use Code No (D) ParcelUseCodes Georeference Text Georeference No PlatStatus SI Plat Status No 1(S) PlatSubtype
Subtypes Worksheet
Define the subtypes in the Subtypes worksheet
Subtypes worksheet PRESET DEFAULTS
Subtype name CodeDescription Field Domain name Default valuePlatSubtype 1Platted Property 2Unplatted Property 3Plat Pending
Part 2: Design Linear Features• We may want the boundaries of the polygons to be
different depending on their location, e.g.:
– Thick line: for edges on the street front– Thin line: boundary between two properties– Dashed line: boundary between two adjacent
properties owned by one person
• To make this possible, we create a set of lines by adding a linear feature class, and list it under the polygon feature
Add the Linear Feature ClassGeodatabase design forms
Geodatabase name LandRecords
Feature dataset name
Feature classes:
Type Feature class name Alias
POLY Parcels Property Ownership
L LotBoundaries Lot Boundaries
Feature Dataset• The relationship between the linear and polygon
features is topological, and requires defining a feature dataset to include the feature classes
– Dataset is a way to aggregate several feature classes– It allows a feature class to handle topology, network
database, geometric network, relationships, or terrains
– In our case, we have topology
Feature Dataset …• Datasets are set in the feature classes (GDB) worksheet
• Write the name of the dataset (PropertyData) in front of the Feature dataset name– This will relate to the linear LotBoundaries (Lot Boundaries)
feature class (table) name
– Now, go to the Tables worksheet, and add the LotBoundaries table at the bottom of the list under Parcel. • Define the LineCode of type Text field to the LotBoundaries table,
and don’t allow null.• Define a domain for it; call it ParcelLineCodes
– See next slide
Tables worksheet
Feature class or Nulls Default Domain name or
table name Field nameField type Alias (Y/N) value subtype field (S) or (D)
Parcels SubName Text Subdivision Name No Blk Text Block Designation No LotNo Text Lot Number No Pre-Type Text Prefix Type Yes Pre-Dir Text Prefix Direction Yes House_Num Text House Number No Street_Name Text Street Name No Street_Type Text Street Type No (D) StTypeAbbrv Suffix-Dir Text Suffix Direction Yes ZIPCODE LI ZIP Code No UseCode Text Land Use Code No (D) ParcelUseCodes Georeference Text Georeference No PlatStatus SI Plat Status No 1(S) PlatSubtypeLot Boundaries LineCode Text Line Code No (D) ParcelLineCodes
Define the Domain for LineCode• In the Domains worksheet, add a new ParcelLineCodes
domain, with alias Line Codes for Parcels, of type Text, and Coded values domain type
• Write the three domain values– ROW = Right-of-Way– LOT = Lot Line– SPLIT = Split Lot Line
Domains worksheet Coded values / Range
Domain name Description Field type Domain type Code (Min) Desc (Max)
ParcelUseCodes Use Codes for Parcels Text Coded values A1 Single Family Detached A2 Mobile Homes A3 Condominium A4 TownHouses A5 Single Family Limited B1 Multifamily B2 Duplex B3 Triplex B4 Quadruplex CITY Developed City Property CITYV Vacant City Property CITYW Water Utility Property CRH Church ESMT Easement F1 Commercial F2 Industrial GOV Government POS Public Open Space PRK Park PROW Private right-of-way ROW Right-of-way SCH School UTIL Utility VAC Vacant
StTypeAbbrvStreet Type Abbreviations Text Coded values Data\Suffix.txt
ParcelLineCodes Line Codes for Parcels Text Coded values ROW Right-of-WayLOT Lot LineSPLIT Split Lot Line
Design a Relationship Class/Table• We now need to design other tables that provide
information for ownership that are provided from the county
– These separate tables are updated/maintained continuously outside of our database by the county
– Any change (e.g., of ownership) done outside will be
updated through the rules in the relationship class• For example, if a property is removed for replatting, the
records in the appraisal table can be deleted automatically
Cardinality of the Relationship• Parcels are related to the appraisal table in different ways:
• If each parcel has one and only one match in the appraisal table, i.e., each record in the appraisal table is for one and only one parcel, the relationship is 1:1
• In the case when one lot is owned by several owners, the relationship is 1:M
• If several owners own several lots, then the relationship is M:M
• These are done in the Relationship worksheet
Relationship WorksheetParcels Ownership TaxRecords2010• The origin class is Parcels, the relationship name is Ownership, and the
destination table (an appraisal table) is TaxRecords2010 (managed by the county)– The appraisal table has the name of lot owners
• This allows adding owners to the lots based on external data• The relationships in both directions are:
– Parcel is owned by TaxRecords2010– Owner has ownership of Parcel
• Choose ‘Simple’ (peer to peer) type for the relationship– This prevents the relationship from deleting data (deletion must be done externally in
the county file)Each parcel can be owned by many peopleEach person can own many parcelsTherefore the relationship is M:M
• Fill in the required information as in the next slide
Relationship worksheet Parcel Ownership_Rel TaxRecords2010Property is owned by Owner
Name of the relationship class: Ownership
Origin table/feature class: Parcels
Destination table/feature class: TaxRecords2010
Relationship type: Simple (peer to peer) Composite
Labels:
Origin to destination: Parcel is owned by
Destination to origin: Owner has ownership of
Message propagation: Forward Backward Both None
Cardinality: 1-1 1-M M-N
Attributes: No Yes - Table name: Ownership_Rel Add to the tables worksheet
Primary key field Foreign key name
Origin table/feature class: georeference : owner
Destination table/feature class: georeference : property
PK of owner is FK in the Parcel; i.e., Parcel has owner FK
PK of property is FK in the Owner;, i.e., Owner has property as FK
Define the ‘Ownership_Rel’ Table
• Add a new table called Ownership_Rel• See next slide
• Now the design is complete• We can use the ArcCatalog application to
create the geodatabase and instantiate it
• Read Tutorial 2-1.
Tables worksheet
Feature class or Nulls Default Domain name or
table name Field nameField type Alias (Y/N) value subtype field (S) or (D)
Parcels SubName Text Subdivision Name No
Blk Text Block Designation No
LotNo Text Lot Number No
Pre-Type Text Prefix Type Yes
Pre-Dir Text Prefix Direction Yes
House_Num Text House Number No
Street_Name Text Street Name No
Street_Type Text Street Type No (D) StTypeAbbrv
Suffix-Dir Text Suffix Direction Yes
ZIPCODE LI ZIP Code No
UseCode Text Land Use Code No (D) ParcelUseCodes
Georeference Text Georeference No
PlatStatus SI Plat Status No 1(S) PlatSubtype
Lot Boundaries LineCode Text Line Code No (D) ParcelLineCodes
Ownership_Rel PercentOwn Float Percentage Owned Yes 100
Exercise • By now you have learned to design a geodatabase and its
feature classes and their associated tables, domains, and subtypes, and take care of data and referential integrity
• Exercise 1-1 (Page 16 textbook) asks you to use a new set of data for zoning of the city of Oleander.
• Design the required feature datasets and feature classes and their fields, domains, and subtypes
• Use blank sets of design forms to model the polygons and their boundaries