Working with the Geodatabase Using SQLAn ESRI Technical Paper February 2004This technical paper is aimed primarily at GIS managers and data administrators who are responsible for theinstallation, design, and day-to-day management of a geodatabase.ESRI 380 New York St., Redlands, CA 92373-8100, USA TEL 909-793-2853 FAX 909-793-5953 E-MAIL [email protected] WEB

Copyright 2003 ESRICopyright 2004 ESRIAll rights reserved.Printed in the United States of America.The information contained in this document is the exclusive property of ESRI. Thiswork is protected under United States copyright law and other international copyrighttreaties and conventions. No part of this work may be reproduced or transmitted in anyform or by any means, electronic or mechanical, including photocopying and recording,or by any information storage or retrieval system, except as expressly permitted inwriting by ESRI. All requests should be sent to Attention: Contracts Manager, ESRI,380 New York Street, Redlands, CA 92373-8100, USA.The information contained in this document is subject to change without notice.U.S. GOVERNMENT RESTRICTED/LIMITED RIGHTSAny software, documentation, and/or data delivered hereunder is subject to the terms ofthe License Agreement. In no event shall the U.S. Government acquire greater thanRESTRICTED/LIMITED RIGHTS. At a minimum, use, duplication, or disclosure bythe U.S. Government is subject to restrictions as set forth in FAR §52.227-14 AlternatesI, II, and III (JUN 1987); FAR §52.227-19 (JUN 1987) and/or FAR §12.211/12.212(Commercial Technical Data/Computer Software); and DFARS §252.227-7015 (NOV1995) (Technical Data) and/or DFARS §227.7202 (Computer Software), as applicable.Contractor/Manufacturer is ESRI, 380 New York Street, Redlands, CA 92373-8100,USA.ESRI, the ESRI globe logo, ArcGIS, ArcObjects, ArcSDE, ArcView, MapObjects,ArcMap,, and are trademarks, registered trademarks, orservice marks of ESRI in the United States, the European Community, or certain otherjurisdictions. Other companies and products mentioned herein are trademarks orregistered trademarks of their respective trademark owners.

Working with the GeodatabaseUsing SQLAn ESRI Technical PaperContentsSQL and the geodatabase 1Querying and editing a geodatabase using SQL 3Querying a geodatabase using SQL 3Editing a geodatabase using SQL 6Versioned tables and multiversion views 8Querying a geodatabase using multiversioned views 8Editing a geodatabase using multiversioned views 10Multiversioned views and version reconcile 13Scenario #1 13Scenario #2 15Scenario #3 16ESRI Technical Paperi

Working with the Geodatabase Using SQLFebruary 2004ii

Working with the GeodatabaseUsing SQLFeature classes and tables in a geodatabase can be queried and modifiedusing SQL. This technical paper discusses SQL and the geodatabase, howto query and edit a geodatabase using SQL, multiversioned views, andversion reconcile.SQL and thegeodatabaseWorking with ArcGIS client applications, end users and ArcObjects developers alikecan interact with the geodatabase to create and manage spatial data. The geodatabaseapplication programming interface (API) and ArcGIS application user interfaces shieldend users from the internal architecture of the geodatabase—often complex geometriesand their relationships with adjacent features are viewed and manipulated as intuitivereal-world objects rather than the underlying table rows and columns.This technical paper will focus on an alternative approach to working with a geodatabasethat will support those who may have no specific knowledge of the geodatabase, itsinternal structure, and API, but who are required to work with the information stored inthe underlying database management system (DBMS) and managed by the geodatabaseand ArcSDE . As geodatabases are implemented in a relational DBMS using DBMS datatypes and table formats, the DBMS’s own Structured Query Language or SQL, adatabase language that supports data definition and data manipulation commands, may beused to work with the information stored in the database.ESRI Technical Paper1

Working with the Geodatabase Using SQLAlthough ArcSDE does not specifically provide an additional SQL API, there are SQLfunctions available to users of DBMS platforms that support spatial data types such asOracle Spatial, IBM DB2 Spatial Extender, and Informix Spatial DataBlade .Accessing the information in a geodatabase via SQL extends support for: Customizations to the ArcGIS application framework, written in a non-ArcObjectsenvironment, such as Microsoft ActiveX Data Objects (ADO), that will work withdata managed by the underlying database.Third party applications such as nongeographic database applications that alsorequire access to the tabular data managed by the geodatabase but have norequirement to display the data or perform any spatial analysis.Applications developed with ESRI products, such as ArcView 3.x and earlierreleases of MapObjects , which do not support the geodatabase API but requireaccess to the information in a geodatabase.Understanding how geodatabase objects are stored in the DBMS and how the SQL workswith these objects as DBMS tables is an important prerequisite to working with thegeodatabase using SQL. The following section will briefly review the internal storagestructure of a versioned feature class in a geodatabase that supports the ArcSDECompressed Binary format implementation.February 20042

Working with the Geodatabase Using SQLThe base table is an existing DBMS table with a shape column, added by ArcSDE, forspatial data. The feature ID in this column provides the link between the base table withthe associated ArcSDE-managed feature and spatial index tables.The feature table stores the geometries for each feature, and the spatial index table storesreferences to shapes based on a simple, regular grid. The spatial index table contains anentry for each shape and grid cell combination to support spatial queries. The Adds andDeletes tables record the changes made to versioned tables in an enterprise geodatabase.Querying andediting ageodatabase usingSQLQuerying ageodatabase usingSQLThe SQL 92 standard is supported by both personal (Access) and enterprise (ArcSDE)geodatabases, although each geodatabase implementation supports a different version ofSQL—ArcSDE geodatabases support ANSI SQL and personal geodatabases support JETSQL. Although these two versions of SQL are very similar, there are slight differences asto what each data source will support with respect to SQL syntax. Some of thedifferences to be aware of are: In both personal and ArcSDE geodatabases, the where clause is not case sensitivewith regard to field names and keywords.“STATE NAME LIKE ” is the same as “state name like ”.However, although the Access database is not case sensitive with regard to the actualfield value that is being queried, DBMS platforms that support ArcSDE are casesensitive.For example, querying a feature class of U.S. states with the following SQLstatement:"Select * from US States where State name 'Florida' "ESRI Technical Paper3

Working with the Geodatabase Using SQLwill return one record in both ArcSDE and personal geodatabases (assuming theUS states feature class contains a feature for Florida). However,"Select * from US States where State name 'florida' "will return one record in a personal geodatabase but no records in an ArcSDEgeodatabase. The Like predicate is again slightly different for personal and ArcSDE geodatabases.In a personal geodatabase, an asterisk symbol (*) is used to match any number ofcharacters, and a question mark (?) is used to match any single character. Forexample,"Select * from US States where State name like 'M*' ""Select * from US States where State name like 'Ma?ne' "will return all States that begin with ‘M’ in a personal geodatabase.With ArcSDE, the percent (%) and ( ) symbols are used. The equivalent ArcSDEgeodatabase syntax for a WhereClause would be:"Select * from US States where State name like 'M%' ""Select * from US States where State name like 'Ma ne' "The following two examples illustrate how to use SQL to query and modify informationmaintained in a geodatabase.Attribute queriesThe following Visual Basic code uses ADO and an Oracle OLE DB provider to connectto an Oracle database. As the Oracle database also supports an enterprise geodatabase, ithosts a mixture of spatial and nonspatial data. The Visual Basic code could be used bynon-ArcGIS clients who require access to some of the unversioned spatial information inthe geodatabase—to perform attribute-only queries, attribute updates, report generation,and so on.For further information on working with ADO and OLE DB providers, refer to 20044

Working with the Geodatabase Using SQLSpatial queriesFor DBMSs capable of storing and managing spatial data in spatial geometry data types(Informix, IBM DB2, and Oracle), additional SQL functions are provided to access thesedata types and work with the spatial data they contain. These DBMS platforms supportspatial queries and analysis via SQL, which extends the capabilities of non-ArcGISDBMS applications that have no specific requirement to display the results but still needto work with the spatial data.In the following example, based on the DB2 Spatial Extender SQL implementation, aSQL select statement is used to perform a spatial join between two feature classes(tables).The city engineer needs a list of all buildings that are within one foot of any lot line. Thebuilding id column of the BUILDINGFOOTPRINTS table uniquely identifies eachbuilding. The lot id column in turn identifies the lot to which each building belongs. Thecolumn MULTIPOLYGON stores the geometry of each building’s footprint.create table BUILDINGFOOTPRINTS(building id integer,lot idinteger,footprintmultipolygon);The LOTS table contains a lot id column that uniquely identifies each lot and a lotmultipolygon column that contains the lot line geometry.create table LOTS(lot id integer,lotmultipolygon);ESRI Technical Paper5

Working with the Geodatabase Using SQLThe query below identifies the buildings that are within one foot of their lot lines—thedistance function performs a spatial join between the building footprints and theboundary of the lot multipolygons and the equijoin between the BF.lot id andLOTS.lot id columns ensures that only the multipolygons belonging to the same lot arecompared by the distance BF.building id fromBUILDINGFOOTPRINTS BF, LOTSwhere BF.lot id LOTS.lot id ANDdistance(BF.footprint,boundary(LOTS.lot)) 1.0;Editing a geodatabaseusing SQLAs well as querying the geodatabase, it is also possible to edit the data in a geodatabaseusing SQL. However, before doing so it is important to understand the geodatabase datamodel that has been implemented—are there any advanced geodatabase objects present,such as geometric networks or topologies, and if yes which feature classes are involved?When working with a geodatabase using ArcGIS applications, the behavior andvalidation rules that the geodatabase enforces will automatically manage the relationshipsbetween those feature classes. This implicit geodatabase behavior does not occur if SQLis used to directly modify the data.For example, when a composite relationship is established between two objects, as in thecase of feature-linked annotation, any modifications made to those objects using ArcGISapplications and the geodatabase API will ensure the relationship between the featuresand the annotation is always maintained. This relationship, and the integrity of the data, isguaranteed by the following behavior:When a new feature is created, a new annotation element is automatically created. If a feature is moved, the annotation for that feature automatically moves with it.If a feature attribute on which the annotation text is based is changed, the annotationtext changes automatically.If a feature is deleted, the annotation is also deleted, again automatically.However, for performance reasons there may be occasions when modifying the data in ageodatabase is best done using SQL, instead of the geodatabase API, as in the case ofbulk attribute updates or data loading operations. If the target geodatabase table attributesplay no part in geodatabase behavioral constraints or data validation rules, then updatinggeodatabase tables using SQL can be an efficient approach to modifying the data.However, as any edit operation undertaken using SQL will bypass these geodatabaseconstraints and data integrity rules and will not respect the relationships between featureclasses, the potential impact of such an operation should be considered carefully prior tomaking any changes. It is important to ensure that modifying certain table attributes willnot affect other objects in the geodatabase.As an example, if SQL was used to modify a feature or row attribute from which text wasderived for feature-linked annotation, the related annotation would not be alerted byinternal object messaging to update automatically—the annotation would continue toreflect the pre-update text value resulting in the annotation and feature becoming out ofsync. Similar behavior would result if the geometry of a feature was modified via SQL—February 20046

Working with the Geodatabase Using SQLthis would bypass internal geodatabase triggers to automatically update related annotationor topologically related features, leaving objects such as networks and topologies in aninconsistent and possibly corrupt state.For these reasons, it is recommended that the use of SQL to modify geometries beconfined to modifying simple geodatabase objects, such as standalone feature classes.This type of operation is only supported for Oracle Spatial and the SQL implementationssupported on the DB2 and Informix DBMS platforms. It is not supported against theArcSDE binary schema implementation.The following guidelines apply to the use of SQL for modifying information in ageodatabase: Avoid updating records using SQL after the data has been versioned, unless SQL isused in conjunction with an ArcSDE multiversioned view. Multiversioned views willbe discussed in greater detail later in this paper. This applies to ArcSDEgeodatabases only—personal geodatabases do not support versioning.Always remember to issue a commit or rollback statement after the SQL statementhas been executed in the database to ensure the changes are either made permanentor undone as required. Failing to do so can cause problems for the DBMS. Forexample, a database compress operation would wait for uncommitted DBMS DMLstatements to be committed before executing successfully.When updating unversioned data using SQL, avoid modifying any attributes that,through geodatabase behavior, affect other objects in the database—as in the case offeature-linked annotation and relationship classes.ESRI Technical Paper7

Working with the Geodatabase Using SQL Avoid using SQL to modify the geometries of feature class that participate in anyadvanced geodatabase objects or functionality such as geometric networks,topologies, and relationships. Modifications to feature classes participating innetworks, topologies, or relationship classes should be restricted to ArcGIS desktopapplications and the geodatabase API. Third party SQL-based applications can readthe data, but any modifications they make to the data will not be reflected in thenetwork, topology, or relationship.In particular, never update the geodatabase specific Enabled or AncillaryRole fieldfor a network feature class using SQL. When this field is updated through ArcGISdesktop applications and the geodatabase API, any updates automatically triggerchanges to the internal geometric network topology tables. Editing these fields viaSQL will bypass this behavior and leave the network in an inconsistent state.Never update the Row ID (ObjectID) field with SQL—this field is allocated andmanaged by the geodatabase and should not be altered.Versioned tables andmultiversion viewsQuerying ageodatabase usingmultiversioned viewsSimultaneous multiuser read–write access to the geodatabase is supported throughversioning, with the changes made to versioned datasets being recorded in delta tables.Client applications that do not support versioning and the geodatabase have no specificknowledge of these delta tables. Without an appreciation of the internal architecture of aversioned geodatabase or a mechanism to interrogate these delta tables, only theinformation in the base table can be queried.To support client applications that must use SQL, access to spatial and nonspatialversioned tables in a geodatabase is provided with ArcSDE through multiversionedviews—a combination of DBMS views, stored procedures, and triggers on the underlyingversioned table schema (the business, feature, spatial index, and delta tables). Amultiversioned view presents a view of a versioned table for a particular version in thegeodatabase—all the records from the base table are selected and unioned with recordsfrom the delta tables that have been modified in the current version state lineage.Although the internal implementations of these multiversioned views vary acrossdatabase platforms, the same general data access model is provided for Oracle,Microsoft’s SQL Server , IBM DB2, and Informix databases.Multiversioned views provide a solution to some of the interoperability issues that arisewhen supporting multivendor or legacy systems. In land use management, for example, itis not untypical for one department to manage and maintain the attribute data in a centralDBMS, while another department, tasked with the collection and maintenance of theassociated spatial data, implements a versioned geodatabase in the same DBMS. The twodepartments rely on the native APIs provided by the DBMS and ArcGIS to manage theirrespective data holdings, with DBMS forms-based applications developed to query andupdate the attribute data and ArcGIS display/edit/print applications to work with thespatial data.To preserve the consistency and currency of the data, there will inevitably be times whenchanges made to one data source must be reflected in the other—for example, if a row isFebruary 20048

Working with the Geodatabase Using SQLdeleted in the attribute table, the corresponding spatial record must also be deleted. Thiscan be achieved using a multiversioned view—a SQL editor can make the necessarychange to the versioned feature class via a multiversioned view. When the ArcGISapplication refreshes their view of the version, the feature class will reflect the recentchange made by the non-ArcGIS application using the multiversioned view.These multiversioned views are created in one of two ways:1.Using the ArcSDE SDETABLE administration command:sdetable -o create mv view -Tworld mv view -t world -u av -p moThis creates a multiversioned view ‘world mv view’ from the feature class ‘world’.2.Through the SDE C or Java API.For further information on using the SDETABLE command and the C and Java APIsand sample code for working with multiversioned views, refer to the ArcSDEDeveloper Help, the multiversioned view has been created, the client application will work with theview as it would work with any other DBMS view. When an application connects to theDBMS, access to specific versions is supported by stored procedures created whenArcSDE is installed in the DBMS. The required version is set by the calling applicationusing the ArcSDE stored procedure, set current version (‘ required version ’). For theESRI Technical Paper9

Working with the Geodatabase Using SQLcomplete command syntax for each supported DBMS platform database, refer to currentArcSDE documentation at procedure validates the supplied version name and sets the corresponding databasestate internally; if no version name is supplied, the DEFAULT version is used. Thisprocedure may also be called again to change to other versions as required, and it iscalled each time the workspace is refreshed to return the current state of the versionedtable to the calling application.With the connection to the required version established via the multiversioned view, SQLqueries can be executed against the database. The view guarantees that all the rows, fromthe base and delta tables, that represent a feature or features in a particular version of thegeodatabase will be returned transparently to the end user.Editing a geodatabaseusing multiversionedviewsEditing versioned feature classes or tables is also supported using multiversionedviews—the multiversioned view ensures that all the changes are logged in the appropriatedelta tables, Row Ids for any new records are assigned automatically, and associatedindexes are updated.Note: Read–Write multiversioned views are supported on ArcSDE for Oracle, SQLServer, and DB2 platforms. ArcSDE for Informix supports read-only multiversionedviews.February 200410

Working with the Geodatabase Using SQLBefore editing can occur, the target version is again identified with theset current version procedure and opened for editing with another SQL storedprocedure, edit version(‘ required version ’, edit action ). The state currentlyreferenced by the version is opened and closed for editing by setting the appropriate editaction parameter—1 open, 2 closed.In the illustration below, a multiversioned view is used by a non-ArcGIS application toadd a new record and update an existing record in a versioned feature class. TheArcSDE/DBMS platform in this case is Microsoft SQL Server. An equivalent operationin ArcMap is included for comparison.As with editing unversioned geodatabase tables using SQL, editing versioned tables usingmultiversioned views does not trigger internal geodatabase behavior for related tables.Care should be taken when updating any attribute fields that participate in a relationshipwith other geodatabase objects. Multiversioned views should not be used for modifyingthe geometries of feature classes that participate in networks, topologies, and otherobjects with specific geodatabase behavior.The following example illustrates this behavior with respect to a feature class, an attributetable, and a relationship class that defines the composite relationship between the two. Amultiversioned view is created on the feature class, and this view is subsequently editedESRI Technical Paper11

Working with the Geodatabase Using SQLusing SQL. If a feature were deleted via the view, the corresponding row in a relationshiptable and the related attribute table would not be deleted.Multiversioned views and SQL could also be used to modify some spatial data. In thenext example, one of the geometries of a versioned feature class is modified—in thiscase, the ArcSDE/DBMS platform is Oracle and the geometry is stored in Oracle Spatialformat. Again, an equivalent operation in ArcMap is included for comparison.February 200412

Working with the Geodatabase Using SQLMultiversionedviews and versionreconcileUnlike editing versioned data in an ArcMap edit session, although each multiversionedview edit will create a new database state, no internal version reconcile is undertaken.Consequently, it is important to be aware of the possibility of other editors working inArcMap , editing the same version at the same time. For this reason, it is stronglyrecommended that multiversioned views and the ArcSDE stored procedures are not usedto edit the DEFAULT version, or any other version, that will be subject to other edit andreconcile operations at the same time.The following examples illustrate some of the problems that could be encountered. In allcases, the target geodatabase is an ArcSDE/SQL Server geodatabase.Scenario #11. An editor working with a multiversioned view and SQL starts editing the DEFAULTversion (owned by the SDE user):execute sde.sde.edit version(‘SDE.DEFAULT’,1);This action creates a new child state based on the state currently referenced by theDEFAULT version (state 0). The DEFAULT version is then relabeled to referencethe new state.ESRI Technical Paper13

Working with the Geodatabase Using SQLAny ArcGIS client application currently connected to DEFAULT will now referencestate one when they refresh their workspace connection. Any ArcGIS client applicationthat tries to connect to the DEFAULT version now will receive the following message:The multiversioned view edit has acquired an exclusive lock on the state currentlyreferenced by DEFAULT—this prevents other users from connecting to DEFAULT.2.At this point, an ArcMap editor starts another edit session on the DEFAULT version,which is currently referencing state one. Starting a new edit session does not create anew database state—it is any subsequent edit operations that create the new states. Inthis respect the data access model supported by multiversioned views differs fromthe ArcGIS application data access model.The ArcMap editor attempts to add a new feature but when completing the editoperation, the following message is displayed:To create a new edit state from state one, the application has attempted to acquire ashared state lock on state one—this operation fails because of the existing exclusive statelock. Only when this exclusive state lock has been released, by issuing the followingcommand:February 200414

Working with the Geodatabase Using SQLexecute sde.sde.edit version(‘SDE.DEFAULT’,2);will the ArcMap editor be able to continue.Scenario #21. An ArcMap editor starts an edit session on the DEFAULT version and adds a newfeature. This edit operation successfully creates a new child state from the statecurrently referenced by the DEFAULT version.2. The SQL editor opens the same version for editing and adds a new feature but doesnot close the version after making the change.Execute sde.sde.set current version‘SDE.DEFAULT’;Execute sde.sde.edit version‘SDE.DEFAULT’, 1;INSERT into sde.gdb.myfeatclass mvv(MyField) values (‘Some text’);Commit;ESRI Technical Paper15

Working with the Geodatabase Using SQL3. When the ArcMap editor then tries to save their changes and reconcile theirtemporary internal version with the DEFAULT version, the following message isdisplayed:The edit session started by the SQL edit has moved the DEFAULT version to a new, andcurrently open, state—state two.The ArcMap editor is unable to save their changes and complete the automatic reconcilewith the DEFAULT version. Any changes made during the course of their edit sessionare now lost and must be repeated.Scenario #31. An ArcMap editor starts an edit session on the DEFAULT version and adds a newfeature.2. The SQL editor opens the same version for editing, adds a new feature, and this timecloses the state once all the changes have been made.Execute sde.sde.set current version(‘SDE.DEFAULT’);Execute sde.sde.edit version(‘SDE.DEFAULT’, 1);INSERT into sde.gdb.MyFeatClass MVV(MyField) values (‘Some text’);February 200416

Working with the Geodatabase Using SQLCommit;Execute sde.sde.edit version(‘SDE.DEFAULT’, 2);3. The ArcMap editor then tries to save their changes and reconcile their temporaryversion with the DEFAULT version. The following message is displayed:The SQL edit has moved the DEFAULT version to a new, and now closed, state. TheArcMap editor is advised that the target of the internal reconcile has changed since theybegan their edit session, and they must save again to acknowledge the changes. When theeditor saves a second time, the changes are successfully applied.The easiest way to avoid any contention between the different data access models is tocreate a new version for those editors working with multiversioned views and SQL. Ifthis is not possible and the DEFAULT version has to be modified, other editors alsoworking with the DEFAULT version need to be aware that they will be unable to modifythat version until the SQL session has executed the following procedure:execute sde.sde.edit version ‘SDE.DEFAULT’, 2;This procedure closes the current state referenced by DEFAULT and makes it availableagain to other editors.SQL and multiversioned views provide an alternative development option for thosewishing to work with the information managed in a geodatabase but have no requirementESRI Technical Paper17

Working with the Geodatabase Using SQLto work with advanced geodatabase objects, such as networks and topologies, or displaythe data. Through multiversioned views, non-ArcGIS client applications have access tothe same versioned data management framework as ArcGIS applications and can bothquery and update individual named versions in the geodatabase.February 200418

Using SQL Feature classes and tables in a geodatabase can be queried and modified using SQL. This technical paper discusses SQL and the geodatabase, how to query and edit a geodatabase using SQL, multiversioned views, and version reconcile. SQL and the geodatabase Working with ArcGIS clie