Designing Geodatabases

1. Designing Geodatabases Part I: Polygons and lines

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

3. 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., GeodatabaseDiagrammer: 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)

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

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

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

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

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

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

10. Add the Polygon feature class

11. Parcel feature class • Attributes (fields) of the Parcel feature class: • These fields should be designed in the Tables worksheet page

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

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

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

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

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

19. 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 table 1 Platted Property 2 Unplatted Property • Platt Pending • Pending means it is approved by the city but is waiting the filing data from the county

20. Adding the Subtypes • In the Tables worksheet, add the information about the subtype at the bottom:

21. Subtypes Worksheet Define the subtypes in the Subtypes worksheet

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

23. Add the Linear Feature Class

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

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

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

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

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

31. Relationship Worksheet Parcels 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 people Each person can own many parcels Therefore the relationship is M:M • Fill in the required information as in the next slide

32. PK of owner is FK in the Parcel; i.e., Parcel has owner FKPK of property is FK in the Owner;, i.e., Owner has property as FK

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

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