Relational Database Management and Geodatabase by gox19993

VIEWS: 50 PAGES: 41

									Relational Database Management
               and
     Geodatabase Concepts

  Jack Horton – ESRI, Olympia WA




                                   1
Road map



 •   Why use RDBMS
 •   Define Geodatabase and its components
 •   Identify geodatabase components
 •   Use geodatabase behavior to maintain data integrity
 •   Understand Structured Query Language (SQL)
 •   Benefits of ArcSDE geodatabase
 •   Questions

 • Expected audience: beginner geodatabase users




                                                           2
Why use an RDBMS?



 • Data maintenance
 • Multi-user access
 • Data protection
   – Recovery
   – Concurrency
   – Security




                       3
Recovery from:



 •   User error
 •   Statement or process failure
 •   Instance failure
 •   Media failure
 •   Recovery must be predictable
      – All committed transactions
      – No uncommitted transactions
 • Transaction logging
      – Optional
      – Can recover up to point of failure




                                             4
Concurrency



 •   Simultaneous access by multiple clients to the same data
 •   Multiple users modify a table at the same time
 •   Read consistency
 •   Transactions are isolated from each other
 •   Implemented by locking




                                                                5
Security


                                               Wilson’s connection
 • Users are assigned privileges
    – Privileges on individual objects
    – Privileges to perform specific actions
    – Resource usage privileges



              Ralph’s connection




 • Auditing
    – Tracking the actions a user performs


                                                                     6
Data storage



 •   RDBMS totally hides physical storage from the user
 •   Only the DBA (DataBase Administrator) works with physical disks
 •   This allows huge tables to span multiple disks
 •   The DBA can rearrange disk storage without impacting the users
     or applications




                                                                       7
Road map



 •   Why use RDBMS
 •   Define Geodatabase and its components
 •   Use geodatabase behavior to maintain data integrity
 •   Understand Structured Query Language (SQL)
 •   Benefits of ArcSDE geodatabase
 •   Questions

 • Expected audience: beginner geodatabase user




                                                           8
Defining the geodatabase



 • Container of spatial & attribute data
    – Collection of geographic datasets
 • Native data structure for ArcGIS
 • Provides the ability to:                                         Surveys
    – Leverage data relationships                        Networks             Addresses


    – Enforce data integrity                   Vectors
                                                                                       Annotation
    – Create intelligent features

                                3D Objects
                                                                                               Attribute



                                    Topology
                                                                                               Dimensions

                                          Terrain
      Geodatabase                                                                         Cadastral


                                                                                 CAD
                                                    Cartography
                                                                     Images
                                                                                                           9
Geodatabase options at ArcGIS 9.3



 • MS Access-based personal GDB
 • File GDB
 • ArcSDE GDB - 3 editions:
    – Desktop, Workgroup, & Enterprise



                                              ArcGIS Server
             ArcGIS Desktop




                                                                   ArcSDE
       Personal
        for MS    File
                  File        Desktop     Workgroup   Enterprise
        Access




                                         Capacity
                                  Number of users
                                                                            10
Geodatabase Builds on RDBMS



    •   Stores spatial data in the RDBMS using ArcSDE
    •   Extends functionality and data integrity
    •   Geodatabases work the same regardless of which RDBMS
    •   Copy and paste data between geodatabases
         – Easy migration between RDBMS




                                Aplication
                                interpret
                                properties as
                                behavior



D                                                              11
Data Dictionary



 • Lists of tables, columns, users, etc.
 • Also called Catalog or Metadata
    – Not to be confused with GIS metadata
 • Metadata is stored in tables
    – Structure is managed the same way as user data
    – You can use these tables too
 • Geodatabases build on this concept by adding GIS metadata
   tables




                                                               12
Table



          Attributes
            Fields
          Columns




 Rows
Records




                       13
Spatial Column



 • Stores spatial data in the RDBMS
 • Many supported ways to store the spatial data
 • The word “Polygon” is just a placeholder

              Spatial
              Column




                                                   14
Raster Data Stored in Geodatabase

                       COL_NBR
  Tile         0   1   2 3 4     5   6   Multi-Resolution
           0                               Pyramids
           1
 ROW_NBR




           2

           3
           4

           5



                       ROW COL
                         0   0
                         0   1
                         0   2

                                                            15
Terrains in the Geodatabase




 Points and Breaklines




   Terrain Pyramids




                   Multi-resolution terrain dataset (TIN structure)

                                                                      16
Road map



 •   Why use RDBMS
 •   Define Geodatabase and its components
 •   Use geodatabase behavior to maintain data integrity
 •   Understand Structured Query Language (SQL)
 •   Benefits of ArcSDE geodatabase
 •   Questions

 • Expected audience: beginner geodatabase users




                                                           17
Geodatabase behavior



 • Specific to geodatabase
   – Subtypes
      • Categorize features into groups
   – Domains
      • Attribute validation on a field
   – Topology
      • Spatial validation rules
                                                        Create real-world
   – Relationship class                                 feature behavior
      • Determine proper associations between records    in geodatabase
   – Geometric networks connectivity
      • Determine proper connections between
        network features




                                                                            18
Enforcing integrity



  • RDBMS integrity rules are applied immediately
    – Constraints and rules
       • Specify valid attribute values
            – Lists, ranges, lookup tables, etc
    – Triggers
       • Procedures that execute when specific actions occur on a table
            – Update, insert, delete, etc
  • You decide when apply Geodatabase integrity rules
    – This allows creating complex data that is invalid until completed
       • Example: a system of pipes and fittings is not correct
         until it is all connected together
    – Examples
       •   Subtype and domain
       •   Relationship cardinality
       •   Geometric network connectivity
       •   Topology rules
                                                                          19
    Subtypes, Domains



     • Applications automatically present subtypes and domains to the
       user



     Feature class             Streets                  PowerPoles

     Subtypes
     based on        Primary        Secondary       Wood        Steel
     a field


     Domains          ST, RD,            Ln, Cir,   Height:    Height:
                     AV, BLVD             St, Pl    20–35      30–50


D                                                                       20
Geometric networks



 • Maintain network connectivity
 • Edge-junction rules: subtype cardinality
 • Used to solve tracing problems


                            Valve
                                              Feature
                            Meter
                                              classes
                           Lateral
      Geometric
      Network                Main


                           Service
                                                        21
Geodatabase Topology



 • You set up rules for relationships between features
 • Violations are detected automatically
 • You can:
   – Ignore the violation
   – Mark it as an exception
   – Fix it using automatic or semi-automatic tools




                  Adjacency              Coincidence

        Parcels                         Country                   Bus
                                                                  route
                       Soil types             Coastline   Roads


                                                                          22
Road map



 •   Why use RDBMS
 •   Define Geodatabase and its components
 •   Use geodatabase behavior to maintain data integrity
 •   Understand Structured Query Language (SQL)
 •   Benefits of ArcSDE geodatabase
 •   Questions

 • Expected audience: beginner geodatabase users




                                                           24
Structured Query Language (SQL)



 • Used to communicate with RDBMS
 • ANSI standard
    – In reality, there are major differences in SQL from different database
      vendors
 • Geodatabase does the SQL for you
    – It works the same regardless of which database
    – You can copy and paste between different databases
 • Three ways to work with Geodatabases
    – Point and click
    – Geoprocessing
    – Object-oriented customization




                                                                               25
SQL SELECT statement is used for query



   SELECT * FROM PARCELS


   SELECT PARCEL_NO, VALUE FROM PARCELS


   SELECT PARCEL_NO, VALUE FROM PARCELS WHERE VALUE > 360
   AND LUSE = ‘RES’




                                                            26
Keys



 • Primary key (PK)
   – Uniquely identifies rows
 • Foreign key (FK)
   – References primary key from another table
   – Not unique



                      FK              PK




                                                 27
Indexing



 •   Exhaustive searching on large tables is SLOW!
 •   Indexes store a sorted list of key values for speed
 •   An index is like a telephone directory
 •   Create using SQL or using geodatabase tools




                                                           28
Joining Tables Using SQL



 • This SQL join produces a cross cross product
    – Probably not what you want
                                              ID   LU_DESC       VALUE

 SELECT PARCELS.ID,                           12   COMMERCIAL    460000

 LANDUSE_CODES.LU_DESC,                       12   INDUSTRIAL    460000

 PARCELS.VALUE                                12   RESIDENTIAL   460000
 FROM PARCELS, LANDUSE_CODES                  78   COMMERCIAL    250000

                                              78   INDUSTRIAL    250000

        PARCELS          LANDUSE_CODES        78   RESIDENTIAL   250000

                                              42   COMMERCIAL    125000
   ID   VALUE     LUSE   LUSE   LU_DESC
                                              42   INDUSTRIAL    125000
   12   460000    COM    COM    COMMERCIAL
                                              42   RESIDENTIAL   125000
   78   250000    COM
                         IND    INDUSTRIAL
   42   125000    RES                         37   COMMERCIAL    370000
                         RES    RESIDENTIAL
   37   370000    RES                         37   INDUSTRIAL    370000

                                              37   RESIDENTIAL   370000

                                                                          29
Joining Tables Using SQL



 • Note how the WHERE clause selects only the matching rows from
   the cross product
  SELECT PARCELS.ID,
  LANDUSE_CODES.LU_DESC,
  PARCELS.VALUE
  FROM PARCELS, LANDUSE_CODES
  WHERE PARCELS.LUSE = LANDUSE_CODES.LUSE

                                             ID   LU_DESC       VALUE

       PARCELS          LANDUSE_CODES
                                             12   COMMERCIAL    460000

  ID   VALUE     LUSE   LUSE   LU_DESC       78   COMMERCIAL    250000

  12   460000    COM    COM    COMMERCIAL    42   RESIDENTIAL   125000

  78   250000    COM                         37   RESIDENTIAL   370000
                        IND    INDUSTRIAL
  42   125000    RES
                        RES    RESIDENTIAL
  37   370000    RES


                                                                         30
Joining Tables Using SQL


  • Here the WHERE clause selects only the joined rows from the cross
    product AND it selects only the desired rows
  SELECT PARCELS.ID,
  LANDUSE_CODES.LU_DESC,
  PARCELS.VALUE
  FROM PARCELS, LANDUSE_CODES
  WHERE PARCELS.LUSE = LANDUSE_CODES.LUSE
  AND PARCELS.VALUE > 360000


                                              ID   LU_DESC       VALUE

       PARCELS          LANDUSE_CODES
                                              12   COMMERCIAL    460000

  ID   VALUE     LUSE   LUSE   LU_DESC        37   RESIDENTIAL   370000

  12   460000    COM    COM    COMMERCIAL

  78   250000    COM
                        IND    INDUSTRIAL
  42   125000    RES
                        RES    RESIDENTIAL
  37   370000    RES

                                                                          31
SQL Views



 • A view looks like a table, but really it is just a stored query

    CREATE VIEW LANDUSE_VALUES AS
    SELECT PARCELS.ID,
    LANDUSE_CODES.LU_DESC,
    PARCELS.VALUE
    FROM PARCELS, LANDUSE_CODES
    WHERE PARCELS.LUSE = LANDUSE_CODES.LUSE
    AND PARCELS.VALUE > 360000

    SELECT * FROM LANDUSE_VALUES




                                                                     32
Joining Tables Using a Geodatabase



 •       Same concept, but simpler
     –    Just specify which fields match
 •       3 ways to join tables
     1. ArcMap Join
          •   Similar to an RDBMS view
          •   M:1
     2. ArcMap Relate
          •   Tables remain separate, but work together
          •   1:M M:N
     3. Relationship Class
          •   Used for editing multiple tables at the same time
          •   Maintains key values automatically (referential integrity)
          •   Can implement automatic behavior during editing
          •   1:M M:N


                                                                           33
Relationship classes



    • Applications automatically detect the relationships and present
      them to users
    • Messaging
      – Composite relationships
         • Deleting parent deletes the child
      – Custom behavior
         • e.g. Moving a parent moves the child
    • Cardinality
      – Rules at the subtype level for the number of rows that can be joined to
        each other




D                                                                                 34
Road map



 •   Why use RDBMS
 •   Define Geodatabase and its components
 •   Use geodatabase behavior to maintain data integrity
 •   Understand Structured Query Language (SQL)
 •   Benefits ArcSDE geodatabase
 •   Questions

 • Expected audience: beginner




                                                           35
    ArcSDE geodatabase benefits



     •   Centralized data storage
     •   Efficient data delivery
     •   DBMS security and reliability
     •   Multiuser editing
     •   Geodatabase replication
     •   Archiving

                                                           ArcGIS Server
                   Same functionality,
                   but does not offer      Desktop    Workgroup   Enterprise
                   multiuser editing.

                                                Capacity
                                         Number of users

A                                                                              36
    Versioning



     • Different versions show different sets of rows
     • Differences are stored in the “adds” and “deletes” tables
     • Used for many workflows
        – Long transactions




         Public         Analyst


        – Supervisory review prior to posting edits
        – Alternative design scenarios
        – Tracking a process through its steps


D                                                                  37
Geodatabase replication and versioning



 • Copies of data distributed among geodatabases
    – Users at different locations can edit the same data
    – Founded on versioning
 • Changes can be synchronized across geodatabases
                Federal




                                                USA




                                  CA                             MI
                State
                Regional




                           Napa        Fresno         Keweenaw        Huron




                                                                              38
Geodatabase Archiving


              Base table
                                                 Archive Table

                                                            F    T



              Delta Tables           Using SQL


                             Adds

                                    Deletes




   When versions are saved or posted to DEFAULT, the archive is updated


                                                                          39
Serving Maps and Applications


  • Database servers serve data to applications
  • Application servers serve complete applications
  • Web Services serve components that applications need
    – Examples:
       •   Map layers
       •   Point locations from street addresses, phone numbers, etc.
       •   Shortest routes
       •   Real time weather maps
  • Some web sites are “mash ups”, combining web services into new
    applications

  • Relevant ESRI products and services:
    – ArcGIS Server and ArcIMS
    – ArcWEB Services and ArcUser Services



                                                                        40
Summary



 • Database is a robust data storage solution
 • Geodatabase is built on RDBMS
 • Geodatabase provides mechanism for implementing and
   enforcing data integrity (non-spatial and spatial)
 • ArcSDE Geodatabase offers additional functionality




                                                         41
To learn more



 • There are numerous sessions on Geodatabases and SDE

 • See the online agenda:
    – http://events.esri.com/uc/2009/infoWeb/OnlineAgenda
    – Search on “geodatabase” or “SDE”



    Thank you for coming!
    Please remember to fill out the evaluation form




                                                            42

								
To top