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

4
System Architecture
ArcSDE Spatial Database Engine processes
requests on geometry
Response via network
Server (RDBMS)
Client (you)
Enterprise System typical
Requests via network
ArcIMS Internet Map Server processes web
requests
PC (you)
Personal Geodatabase typical
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 usedfor
  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

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

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

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
SELECT win99UP., win99DNMHUnmatch. INTO
win99ALL FROM win99UP LEFT JOIN win99DNMHUnmatch
ON win99DNMHUnmatch.DNwin99UP.DNMH SELECT
win99Unmatched., win99UPMHUnmatch. INTO
win99UP FROM win99Unmatched LEFT JOIN
win99UPMHUnmatch ON win99UPMHUnmatch.UPwin99Unmat
ched.UPMH
Example querys verifying matched DN / Upstream
Manholes with pipe UP/DNMH
16
Manhole Database
SELECT MHReport1.MHNumber, MHReport1.QSIndex,
MHReport1.Debris, MHReport1.Bench,
tblHydraulic.PipeCondition, MHReport1.Remarks
INTO MHReport2 FROM MHReport1 LEFT JOIN
tblHydraulic ON tblHydraulic.MHNumberMHReport1.MH
Number WHERE tblHydraulic.ProjectNumber"WS9016000
24_90500106"
Example query selecting manhole inspection data
for project report
17
Wincam Database
SELECT PEC_ID, PipeID, UPMH, DNMH, UPMHDNMH
AS UPDN, Street, AllRemarks, VideoIndex,
CCTVDirection, PreClean, ContinuousDefect,
DefectLocation, ClockFrom, ClockTo, Intrusion,
PipeUse, PipeDiameter, PipeShape, PipeMaterial,
Lining, CCTVFootage, DefectCode,
DefectDescription, Severity AS SeverityCode,
SeverityRating, SeverityDescription, ImageFile,
Image INTO win99GIS FROM win99GIS_1
Example query selecting CCTV table data for
import into GIS 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

19
Project Reach
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 -gt Clay
• 1 -gt Iron
• 2 -gt 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

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

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
32
Project Tab

• SQL
• Insert
• Select
• w/WHERE clause
• Update
• Delete

33
Project Pipe Data Display / Entry Dialog
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

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