Creating the Spatial Database

1. Creating the Spatial Database ESRI Geodatabase Functionality

2. Objectives Review ESRI geodatabase types Understand the components of the geodatabase & their relationship to one another Signficance of data entry operations

3. Kinds of Geodatabases Supported by ArcGIS Personal geodatabase MS Access 2 GB size limit, but effective size is 250 to 500 MB per geodatabase Windows only Supports a single editor and a few readers No versioning support File geodatabase Stored in a file folder Up to 1 TB per dataset Any platform Supports a single editor and a few readers No versioning support. ArcSDE geodatabase Stored in a RDBMS (Oracle, SQL Server, DB2, Informix) Supports many editors and many readers Uses ArcSDE Provides versioning and multiuser support Although we’ll be working within a personal geodatabase the concepts are applicable to all types of spatial databases.Although we’ll be working within a personal geodatabase the concepts are applicable to all types of spatial databases.

4. System Architecture Consider that in larger, enterprise-type spatial databases, there may be 1000 tables.Consider that in larger, enterprise-type spatial databases, there may be 1000 tables.

5. Eleven Steps to Geodatabase Design (1-2) 1.Identify the information products that will be created and managed with the GIS Design should reflect the work of the organization Consider: map products, analytical models, Web mapping applications, data flows, database reports, key responsibilities, 3D views, and other requirements of the organization List data sources 2. Identify the key data themes based on information requirements Define more completely some of the key aspects of each data theme Determine how each dataset will be used—for editing, for GIS modeling and analysis, Represent business workflows, mapping and 3D display Specify the map use, the data sources, the spatial representations for each specified map scale Data accuracy and collection guidelines for each map view and 3D view How the theme is displayed, its symbology, text labels, and annotation Consider how each map layer will be displayed in an integrated fashion with other key layers For modeling and analysis, consider how information will be used with other datasets (for example, how they are combined and integrated) This will help you to identify some key spatial relationships and data integrity rules Ensure that these display and analysis properties are considered as part of your database design Obviously, the in-class project will be less comprehensive. We’ll focus primarily on integrating tabular data.Obviously, the in-class project will be less comprehensive. We’ll focus primarily on integrating tabular data.

6. Eleven Steps to Geodatabase Design (3-6) 3.Specify the scale ranges and spatial representations of each data theme at each scale Associate geographic representation for each map scale Geographic representation will often change between map scales (for example, from polygon to line or point) 4. Decompose each representation into one or more geographic datasets Discrete features are modeled as feature classes of points, lines, and polygons Consider advanced data types such as topologies, networks, and terrains to model the relationships between elements in a layer as well as across datasets Raster datasets, mosaics and catalog collections are options for managing very large collections Surfaces can be modeled using features, such as contours, as well as using rasters and terrains 5. Define the tabular database structure and behavior for descriptive attributes Identify attribute fields and column types Tables also might include attribute domains, relationships, and subtypes Define any valid values, attribute ranges, and classifications (for use as domains) Use subtypes to control behaviors Identify tabular relationships and associations for relationship classes

7. Eleven Steps to Geodatabase Design (6 & 7) 6.Define the spatial behavior and integrity rules for your datasets For features, add behavior and capabilities for a number of purposes using topologies, address locators, networks, and terrains, etc. For example, use topologies to model the spatial relationships of shared geometry and to enforce integrity rules Use address locators to support geocoding For rasters, you can decide if you need a raster dataset or a raster catalog 7. Propose a geodatabase design Define the set of geodatabase elements to include in the design for each data theme Study existing designs for ideas and approaches that work

8. Eleven Steps to Geodatabase Design (8 & 9) 8. Design editing workflows and map display properties Define the editing procedures and integrity rules For example, all streets are split where they intersect other streets and street segments connect at endpoints Design editing workflows that support these integrity rules for the data Define display properties for maps and 3D views These will be used to define map layers 9. Assign responsibilities for building and maintaining each data layer Determine who will be assigned the data maintenance work within the organization or assigned to other organizations Understanding these roles is important Design how data conversion and transformation is used to import and export data from partner organizations

9. Eleven Steps to Geodatabase Design (10 & 11) 10. Build a working prototype and review and refine your design Test a prototype design Build a sample geodatabase copy of the proposed design using a file, personal, or ArcSDE Personal geodatabase Build maps, run key applications, and perform editing operations to test the design's utility Based on the prototype test results, revise and refine the design Once you have a working schema, load a larger set of data (such as loading it into an ArcSDE geodatabase) to check out production, performance, scalability, and data management workflows This is an important step: Settle on your design before you begin to populate your geodatabase 11. Document your geodatabase design Various methods can be used to describe the database design and decisions Use drawings, map layer examples, schema diagrams, simple reports, and metadata documents Some users like using UML UML is not sufficient on its own Cannot represent all the geographic properties and decisions to be made Does not convey the key GIS design concepts such as thematic organization, topology rules, and network connectivity UML provides no spatial insight into your design Many users like using Visio to create a graphic representation of their geodatabase schema such as those published in the ESRI data models

10. In This Class . . . Focus on data modeling efforts Understand & identify significant components of a spatial database Identify & develop Entities Relationships Domains Subtypes

11. Regardless of SizePlanning IS Important Primary Components of the Geodatabase Database Feature Dataset Feature Class Tables Fields Rows Primary & Foreign Key Domains Subtypes Relationship Class

12. City of PhoenixGravity Sewer copSewer.mdb Feature datasets Feature classes Annotation classes Relationship Classes Tables mhInspections.mdb Tables Wincam.mdb CCTV inspection database Tables The next several slides present an actual gravity sewer project for the City of Phoenix.The next several slides present an actual gravity sewer project for the City of Phoenix.

13. Project Description Integrate CCTV inspection data with City of Phoenix Gravity Sewer GIS Large-, lined-pipe 167 miles CCTV: Closed Circuit TV Inspection of pipe by robot Images and descriptions of defects are recorded to an MS Access Database Measure value of defect is recorded Generate point data representing defects based on measurement values Network is necessary Continuous defects have start/end measure along pipe Visual Inspection of Manholes Manual form completed on site Completed form to serve as source for data entry into manhole database Verify manhole and pipe data in GIS with in-field inspection data Update tables as necessary Generate maps displaying Entire project reach Inspected pipe Uninspected pipe Pipe condition assessment Severity of damage symbolized with color Based on engineer analysis Import Excel spreadsheet into database Join on pipe segment Show up/downstream manholes Show missing manholes based on visual inspection The primary goal of the project was to integrate CCTV data for large-lined sewer pipe into the corresponding, subset of the city of Phoenix sewer model.The primary goal of the project was to integrate CCTV data for large-lined sewer pipe into the corresponding, subset of the city of Phoenix sewer model.

14. City of Phoenix Sewer Database Geodatabase Subset of enterprise system Updated copy to be uploaded (replace) to server database SQL used to search, review, and edit data Relationship class on manhole table Manhole table imported from mhInspections.mdb

15. City of Phoenix Sewer Database SQL expressions were used to manipulate data from within Access for the geodatabase as well as the manhole inspections databae and the Wincam databases; up to ten Wincam databases delivered per week.SQL expressions were used to manipulate data from within Access for the geodatabase as well as the manhole inspections databae and the Wincam databases; up to ten Wincam databases delivered per week.

16. Manhole Database

17. Wincam Database

18. Issues Wincam collects/assigns data in binary format ArcGIS does not recognize binary format; the field must be converted to a recognized data type prior to import otherwise an error message will be generated Wincam stores data as different type from COP database; use SQL to convert to appropriate type Types must be the same before appending to COP database The manual data entry of the manhole inspection data is prone to error Consider program to minimize input errors (ensure data integrity) The PK for the COP sewer is the combination of the Upstream and DOWNstream manhole number Flow is not readily apparent thus, flow direction must be assigned to all pipe segments Several issues had to be addressed in order to utilize database functionality to validate, verify, and update the city of phoenix databases. Of course, the results of analysis had to be presented as well.Several issues had to be addressed in order to utilize database functionality to validate, verify, and update the city of phoenix databases. Of course, the results of analysis had to be presented as well.

19. Project Reach The project reach stretched practically the entire length and width of the city of Phoenix. Codes had to be developed to easily identify which pipe segments/manholes were included on which maps and the status of the each pipe segment. E.g., the inspection schedule (complete, date for inspection, etc.)The project reach stretched practically the entire length and width of the city of Phoenix. Codes had to be developed to easily identify which pipe segments/manholes were included on which maps and the status of the each pipe segment. E.g., the inspection schedule (complete, date for inspection, etc.)

20. Domains A domain is a fixed set of values Implemented at the database level Available to all fields A domain provides a form of constraint Constraints help retain data integrity Domains may be integer or text Numeric A range or fixed set of values E.g., pipe diameters are: 12”, 24”, & 36” Text Coded value: Code with a description E.g., State Abbreviation & description Coded value: AZ Description: Arizona

21. Domain Properties Dialog

22. Subtypes Subtypes are implemented at the feature class or attribute table level Records grouped together based on a field Implemented by creating coded values and must be associated short or long integer Integer values represent a feature in the subtype E.g., Codes in a subtype named pipe material 0 -> Clay 1 -> Iron 2 -> Other Each subtype can have default values E.g., Clay could have a default value for diameter of 10”; whereas iron could have a default diameter of 25” Each subtype could have its own range or coded attribute domain; a form of constraint

23. Subtype TabFeature Class Properties Dialog

24. Attribute Table Descriptive information Primary key – Foreign key enables joins on other tables Attribute table structure: schematic of the table Column Name Data Type Length Default Value Allow Nulls Domain

25. Attribute Table StructureArcGIS & Access

26. Joins Parent-child relationship between tables Parent table is the table being referenced (referenced to) Child table is the table referencing (referenced from) Joins are created on a common field (with common values) in the tables participating in the join Common fields MUST be the same data type The parent table has the primary key which is the unique identifier in that table The child table has the foreign key which may or may not be unique but is the field the join is based on

27. Relationship Classes Relationship class defines how objects in the origin (referencing table) relate to objects in the destination (referenced table) May be simple or composite Simple: Related objects can exist independently of each other Composite: Destination objects cannot exist independently of origin objects Both types maintain referential integrity If an object in the destination is deleted so is the origin A composite relationship means that all objects participating in the composition will be deleted E.g., a controlled intersection must have a traffic control device; the composite relationship between controlled intersection and traffic control device means all objects will be deleted Kinds of relationships One-to-one One-to-many Many-to-Many Use subtypes Joins extend the analytical and display capabilities of the features in the geodatabase.Joins extend the analytical and display capabilities of the features in the geodatabase.

28. New Relationship Class Dialog

29. Editing a Relationship Class Values for selected pipe feature (object geometry being edited in the map) Values for related manhole number A fundamental benefit of the relationship is the ability to update/review common values during editing sessions.A fundamental benefit of the relationship is the ability to update/review common values during editing sessions.

30. Data Entry Operations Data entry to mhInspections database are done manually Reduce the number of data entry errors by creating a GUI for these operations C# application

31. Manhole InspectionsData Entry Interface Opportunities to be involved (or create new opportunities) in all aspects of the project will most likely present themselves. Developing a system view of the GIS will give you a better understanding of the role you and the GIS plays in the organization.Opportunities to be involved (or create new opportunities) in all aspects of the project will most likely present themselves. Developing a system view of the GIS will give you a better understanding of the role you and the GIS plays in the organization.

32. Project Tab SQL Insert Select w/WHERE clause Update Delete

33. Project & Pipe Data Display / Entry Dialog Notice how the input windows basically provide interface to the tables in the mhInspections database that contain that data.Notice how the input windows basically provide interface to the tables in the mhInspections database that contain that data.

34. Structural & Hydraulic Data Entry / Display Dialog

35. The GeodatabasePart of a System Typically working within a larger system Data modeling helps you understand your role within that larger system Typically IT / IT-GIS are organization-wide GIS & IT personnel work very closely Know the relationships among departments and data Know the business processes and GIS / spatial data role within the context of those business processes Remember, you are part of an overall system.Remember, you are part of an overall system.

36. Coming Up Next Week: Identify attributes for entities Develop attribute table structures Consider mapping applications Week After Next: The role of SQL Introduce UML Introduce Visio