Docstoc

Using SQL and Spatial Types with the Geodatabase - Download as PowerPoint

Document Sample
Using SQL and Spatial Types with the Geodatabase - Download as PowerPoint Powered By Docstoc
					      Technical Workshops



Using SQL and Spatial
       Types with the
        Geodatabase
         Shannon Shields
               Travis Val
Session Overview


  •   Overview of
       -   Geodatabase
       -   Spatial types
  •   Demonstrate the use of spatial type functions using
      Structured Query Language (SQL)
  •   Discuss and demonstrate SQL access to the
      geodatabase


  •   General discussion of all supported spatial types
Session Focus and Prerequisites


  •   Target audience
       -   Developers who need to access the geodatabase with
           SQL
       -   Anyone who wants to understand how to access the
           geodatabase with SQL


  •   Prerequisites
       -   Familiar with ArcGIS geodatabase
       -   Familiar with SQL
       -   Knowledge of database management system (DBMS)
           concepts
Supported Spatial Types

•   IBM DB2                     •   PostgreSQL
     -   ST_GEOMETRY                 - ST_GEOMETRY (ESRI)
         (IBM with ESRI)             - GEOMETRY (PostGIS by
                                       Refractions Research)
                                     - Version 8.3.8 & 8.4.1
•   IBM Informix
     -   ST_GEOMETRY
         (Informix with ESRI)   •   Microsoft SQL Server
                                     - GEOMETRY (MS)
•   Oracle                           - GEOGRAPHY (MS)

     - ST_GEOMETRY (ESRI)            - Version 2008

     - SDO_GEOMETRY (Oracle)
Agenda


 •   Overview
      -   Geodatabase
      -   Components and benefits of spatial types
 •   Accessing the geodatabase using SQL
 •   Development considerations
ArcGIS Software Architecture
                   ArcObjects




                    ArcGIS



                 Geodatabase


                                   SQL
               ArcSDE technology


                    DBMS
Geodatabase data management approach

  •   The geodatabase is built on a relational database
       -   Functionality consistent across each DBMS


  •   Database provides:
       -   Storage of geographic data in tables
       -   Extended functionality and data integrity


  •   Application logic provides:
       -   GIS integrity and behavior
       -   Business rules, topology, networks, etc
How geodatabases are stored in a DBMS

  •   There are two sets of tables:
       -   Geodatabase system tables
       -   User data tables
  •   Geodatabase system tables work with the user data
      tables to manage data
  •   The schema of the geodatabase system tables is
      controlled by ArcGIS


                System tables          XML

                                  SQL
                   User data      type
User-defined tables


  -   Stores the content of each dataset in the
      geodatabase
  •   Datasets are stored in one or more tables




             System tables        XML

                                SQL
                 User data
                                type
User-defined tables


  •   A feature class is stored as a simple DBMS table
  •   Each row represents a feature
  •   The fields in each row represent various
      characteristics or properties of the feature
  •   One of the fields holds the feature geometry which is
      stored as a spatial type
Geodatabase system tables

  •   System tables store definitions, rules, and behavior
      for datasets
  •   Tracks contents of a geodatabase
  •   4 main system tables
  •   Geodatabase schema is stored primarily within an
      XML field



             System tables        XML

                                SQL
                 User data      type
Geodatabase schema
Geodatabase schema
Geodatabase schema
Geodatabase schema
Geodatabase schema
Demo: GDB system queries
            Look at system tables
              List domain owners
              Get min & max of a
                   range domain
Geodatabase and Spatial Types


  •   Geodatabase provides a comprehensive GIS model


  •   Spatial types enhance the capabilities of the
      geodatabase
       -   SQL access to geometry
       -   Industry standard storage model and API
Agenda


 •   Overview
      -   Geodatabase
      -   Components and benefits of spatial types
 •   Accessing the geodatabase using SQL
 •   Development considerations
What is a spatial type?


  •   A data type for storing geometry
       -   Points, lines, polygons
       -   Spatial reference
  •   Spatial index
       -   Improves spatial searches
  •   Functions (operators, methods)
       - Create new geometry (constructor)
       - Return property of a geometry (accessor)
       - Perform spatial operations (relational)
       - Transform from one geometry to another (geometry)
Benefits of a spatial type

  •   With SQL and a spatial type you can
       -   Create tables with a spatial attribute
       -   Read and analyze spatial data
       -   Insert, update and delete simple features
  •   Eases integration
       - The geodatabase can create spatial type data
       - The geodatabase can consume spatial type data

  •   Enhances your efficiency
       -   Leverage processing power of the DBMS
  •   Adheres to standards
       - Standard functions
       - Well-known interchange formats
Agenda


 •   Overview
 •   Accessing the geodatabase using SQL
      -   Common workflows
 •   Development considerations
Accessing the Geodatabase using SQL


  •   Queries
  •   Accessing versioned data
  •   Editing geodatabase data
  •   Data creation
  •   Registration of non-Geodatabase tables
Accessing Geodatabase through SQL

  •   Use spatial SQL operators to
       -   Evaluate attributes and spatial relationships
       -   Perform spatial operations
       -   Return and set spatial properties
  •   Access schema and properties of existing datasets
       -   Use SQL SELECT statements and Xpath queries to
           query the Definition field in the GDB_ITEMS table
  •   Edit tables or feature classes
       -   Use multiversioned views for versioned data
  •   Create tables with SQL
       -   Non-spatial, spatial or raster attributes
Accessing Geodatabase through SQL

                                            ArcObjects

  •   With SQL accessing the
      data at the DBMS level
                                              ArcGIS
       -   Bypass behaviors and
           functionality enforced by
           the geodatabase or ArcGIS       Geodatabase
           clients
  •   Need to be aware of what
                                       ArcSDE technology
      you can and cannot edit
       -   Relationship classes                                 SQL
                                               DBMS
       -   Geometric networks
       -   Topology…

  See “What type of data can be edited using SQL” in the ArcGIS help
Queries


  •   Queries are done using standard SQL
       -   SQL query development is typically done with
           applications from database vendor
            - Oracle SQL*Plus, SQL Server Management Studio etc.
            - Please refer to DBMS documentation


  •   Spatial filter
       - Include spatial functions in the WHERE clause
       - Single feature class


  •   Spatial join
       -   WHERE clause contains spatial relationship operators
           with geometries from two or more feature classes
Demo: Queries
Location analysis - part I
Demo: Location analysis


  •   Project: Find a location for a new sports complex
  •   Input data: Parcels, City Facilities, Roads, Buildings
  •   Requirements
       -   Parcel must have area > 300,000 sq ft
       -   Parcel must be within 1000 ft of a major road
       -   Parcel cannot contain a city facility
       -   Parcel must be > 1000 ft from a school
Editing ArcGIS Feature Classes with SQL


  •   Non-versioned editing
       -   Use SQL to edit tables directly
       -   Implemented at the database level


  •   Versioned editing with multiversioned views
       -   Use SQL to edit a special versioned view of business
           table
       -   Implemented in the database
Editing versioned tables and feature classes


  •   All changes are written to delta tables
       -   Adds and Deletes tables
  •   Edits are assigned an identifier (state_id)
  •   A version references a lineage of state_ids
Editing versioned tables and feature classes

     6       7           Adds Table
     1       2           ObjectID    Perimeter   Bldg_Code   SDE_State_ID
                         6           10105.15    02          27505
  3      4   5
                         7           10105.15    02          27602

 Business Table                          Deletes Table
 ObjectID    Perimeter   Bldg_Code        SDE_State_ID   Deletes_Row_ID     Deleted_At
 1           10105.15    02               0              2                  27505
 2           10105.15    02
 3           11348.31    02
 4           10827.18    02
 5           11348.31    02
Editing versioned tables and feature classes

     6                   Adds Table
     1                   ObjectID    Perimeter   Bldg_Code    SDE_State_ID
                         6           10105.15    02           27505
  3      4   5
                         7           10105.15    02           27602

 Business Table                          Deletes Table
 ObjectID    Perimeter   Bldg_Code        SDE_State_ID    Deletes_Row_ID      Deleted_At
 1           10105.15    02               0               2                   27505
 2           10105.15    02
 3           11348.31    02              Versions Table
 4           10827.18    02               Name        Version_ID   State_ID    …
 5           11348.31    02               DEFAULT     1            0
                                          A           2            27505
                                          B           3            27602
Multiversioned Views
•   Specialized DBMS view to work with versioned data
    using SQL
      -   Uses stored procedures, triggers and functions to access
          and edit versioned business tables
•   Result set is based on versioned query
•   Created on single versioned table, contains all columns
•   All stored procedures, functions etc. installed with
    ArcSDE
•   Created with sdetable command
          sdetable –o create_mv_view –T parcels_mv
             –t parcels –u gdb –p gdb –D ucdemo…

    More information in ArcGIS Desktop Help - search for 'multiversioned
    views'
SQL workflow for reading versioned data


  1.       Create a multiversioned view
       -    sdetable command


  2.       From SQL select a specific version to access
       -    Execute set_current_version DBMS procedure or
            function


  3.       Select from the multiversioned view, not the
           business table
Choose Version for Multiversioned View

  •   DBMS procedure for setting the version for the view
       -   set_current_version

//DB2
call setcurrentversion ('FRED.SUBDIVISION',?,?)
//Informix
 EXECUTE FUNCTION
 sde.set_current_verions('FRED.SUBDIVISION')
//Oracle
 exec sde.version_util.set_current_version
 ('FRED.SUBDIVISION');
//PostgreSQL
SELECT sde.sde_set_current_version('FRED.SUBDIVISION')
//SQL Server
 exec dbo.set_current_version 'FRED.SUBDIVISION'
Set_current_version


  •   Validates the version
       -   Does version exist?
       -   Checks permissions for private version
  •   Sets the corresponding state internally
       -   Locks the version to other sessions
  •   Failure to call set_current_version
       -   Multiversioned view will be based on DEFAULT version
Demo: Accessing
  versioned data
  Location analysis - part II
Demo: Location analysis


  •   Project: Find a location for a new sports complex
  •   Input data: Parcels, City Facilities, Roads, Buildings
  •   Requirements
       -   Parcel cannot contain Vacant or Occupied buildings
            -   Only Abandoned or Condemned
SQL workflow for editing versioned data

  1.    Create a multiversioned view
  2.    Create a version in which to do your editing
  3.    From SQL:
       a)   Select a specific version to access
       b)   Start an edit session
            -   Edit_version DBMS function/procedure
       c)   Perform edits on the multiversioned view (not the
            business table)
       d)   Stop the edit session
            -   Edit_version DBMS function/procedure

  4.    Use ArcMap or geoprocessing tools to reconcile
        and post changes
Start and stop multiversioned view edit session

  •   edit_version
       -   DBMS procedure / function for starting and stopping a
           versioned edit session
//Oracle
exec sde.version_user_ddl.edit_version ('fred.SUBDIVISION', 1);
UPDATE parcel_mv SET owner = 'Ethan Thomas'
  WHERE parcel_id = '322';
COMMIT;
exec sde.version_user_ddl.edit_version ('fred.SUBDIVISION', 2);
//SQL Server
                                                    Start edit session
exec sde.edit_version 'fred.SUBDIVISION', 1
INSERT INTO parcel_mv (parcel_id, shape) VALUES
 (323, geometry::STGeomFromText('POLYGON (3 3, 4 6, 5 3, 3 3)',
  4326));
                                                    Stop edit session
exec dbo.edit_version 'fred.SUBDIVISION', 2
Edit_version


  •   Opens (creates) and closes states
  •   Edit action 1 writes new state to sde_states table



  •   Edit action 2 closes the state



  •   All edits occuring between opening & closing of
      state are assigned that state_id
Multiversioned view transaction model


  1.       Set_current_version
       -   Locks version’s state
  2.       Edit_version edit action 1
       -   Commits any open transaction
       -   Begins transaction
  3.       User DML statements
            -   User controls transaction commit/rollback

  4.       Edit_version edit action 2
       -   Commit transaction
Editing workflow recommendations

  •   Give each editor their own version
       -   Exclusive lock on the version’s state blocks access
           from other sessions
  •   NEVER edit DEFAULT version from SQL
       -   Exclusive state lock will prevent geodatabase
           connections from ArcGIS
  •   Manage transactions in your workflow
       -   Always bundle edits to version within transactions
       -   You are responsible for determining transaction
           granularity
  •   ALWAYS close the state after editing
       -   Edit action = 2
Non-versioned editing workflow
  •   All geodatabase tables have a system-maintained
      unique identifier (ObjectID)
  •   SQL Inserts require a new ObjectID
       -   Obtain one from DBMS sequence or procedure
//Oracle example
SELECT registration_id FROM sde.table_registry
  WHERE owner = 'FRED' AND table_name = 'PARCELS';
SELECT sde.version_user_ddl.next_row_id('FRED', 114) FROM dual;
//SQL Server example
SELECT registration_id FROM sde.sde_table_registry
  WHERE owner = 'FRED' AND table_name = 'PARCELS'
DECLARE @id AS INTEGER
DECLARE @num_ids AS INTEGER
exec fred.i114_get_ids 2, 1, @id OUTPUT, @num_ids OUTPUT
Demo: Versioned edits
      Location analysis - part III
Demo: Location analysis


  •   Project: Find a location for a new sports complex
  •   Input data: Parcels, City Facilities, Roads, Buildings
  •   Final steps
       -   Delete abandoned/condemned building from chosen
           parcel
       -   Insert new building on chosen parcel
Data loading methods


  •   ArcGIS
       -   ArcCatalog, geoprocessing, etc.
       -   Dbtune configuration keyword controls geometry
           storage
       -   Data is automatically registered with the geodatabase


  •   Other
       -   SQL INSERT statements
            -   Geometry constructors with WKT or WKB
       -   3rd party tools
       -   Data is not registered with the geodatabase
Integrating non-geodatabase tables


  •   Register with both ArcSDE and the geodatabase to
      use the spatial data within ArcGIS

  •   sdelayer command: Register table with ArcSDE

      C:\>sdelayer –o register –l roads,shape –C ID,SDE
      –e l+nc –t GEOGRAPHY –G 4326 ...




  •   ArcGIS Desktop or geoprocessing tool
       - Register with Geodatabase
          - Geodatabase behavior is available
Agenda


 •   Overview
 •   Accessing the geodatabase using SQL
 •   Development considerations
Write efficient SQL

  •   Do not access more data than you need
       - Fetch only necessary columns
       - Join only necessary tables


  •   Define efficient where clauses
       -   Indexes on WHERE clause columns


  •   Avoid operators that might force full table scans
       - Operators: SUBSTR(), INSTR(), UPPER()
       - Implicit casting, such as from one string type to
         another


  •   Analyze execution plans during development
Guidelines for using SQL and the geodatabase

                            •   Understand the geodatabase
          Geodatabase           system tables and their
                                structure

      ArcSDE technology
                            •   Avoid changing data that affects
                                geodatabase software level
             DBMS               behavior



•   Geodatabase awareness
     •   You have it
     •   The database does not
Guidelines for using SQL and the geodatabase


  •   Do perform spatial operations


  •   Do query spatial and attribute information


  •   Do INSERT, UPDATE and DELETE geometries
       -   As long as you pay attention to behavior


  •   Do INSERT, UPDATE and DELETE attribute data
       -   As long as you pay attention to behavior
Guidelines for using SQL and the geodatabase
  •   Do not update the objectid (row_id) value

  •   Do not modify geometries for classes participating in
      topologies, geometric networks, network datasets,
      terrains, cadastral fabrics, geodatabase replication
      or feature linked annotation
       - Will not create dirty areas or be validated
       - Will not maintain connectivity in the logical network …


  •   Do not update attributes that define geodatabase
      behavior
       - Enabled/Disabled attributes
       - Ancillary attributes
       - Weight attributes…
Summary


 •   Overview of the geodatabase & Spatial types
 •   Demonstrate the use of spatial type functions using
     Structured Query Language (SQL)
 •   Discuss and Demonstrate SQL access to the
     Geodatabase
      -   Accessing geodatabase schema
      -   Editing geodatabase data
 •   Development considerations
Related Documentation Resources
  •   Geodatabase Resource Center
       -   http://resources.arcgis.com/content/geodatabases/10.0/about
  •   Inside the Geodatabase Blog
       -   http://blogs.esri.com/Dev/blogs/geodatabase/default.aspx
  •   ArcGIS Desktop Help topics
       - Using SQL with ArcSDE geodatabases
       - Using SQL to access geodatabase data
       - Using SQL to access the properties of datasets
       - Editing geodatabase data using SQL
Related User Conference Sessions


•   Demo Theatres
     -   Working with Spatial Type Data Using SQL (Wed 1 pm)
     -   Introduction to Query Layers (Wed 4 pm)
•   Technical Briefing
     -   Working with ESRI’s Spatial Data Type for Oracle (Wed 12 pm)
•   Technical Workshops
     -   Enterprise Geodatabase – Tips & Tricks (Thurs 8:30 am)
     -   Geodatabase Essentials Part 2 – An Introduction to ArcSDE
         Geodatabases (Thurs 8:30 am)
     -   Geodatabase Editing Workflows (Thurs 1:30 pm)
       Questions?


Please fill out the evaluation

  Thank you for attending

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:27
posted:10/4/2011
language:English
pages:58