00:01So to my left is my still illustrious colleague Mr. Russell Brennan who apparently was right on the time, I guess...

00:08...what the consensus is.

00:10Those are both our Twitter handles.

00:12So if you want to follow what we're Tweeting about, we can't promise we'll always be Tweeting about geodatabase...

00:17...or GIS stuff.

00:19Russell has a weird fascination with mountain biking, so most of his Tweets might be about two-wheeled adventures.

00:25If you guys are into Twitter and want to talk about the session, you can use the hashtag #sqlpy.

00:31So if you want to give us any feedback or just talk about our session, we'll be following that, that hashtag...

00:37...before, during, and after this session.

00:43Some basic assumptions we're going to go off and we had in mind when we created this slide deck.

00:49We assume that you have at least a little bit of knowledge of SQL and how SQL works in a relational database...

00:56...and maybe some experience with S-Q-L, with SQL.

01:00Also the same basic knowledge of Python and a working knowledge of relational databases...

01:05...would help to understand some of the concepts we're going to talk about in this session.

01:10Also a basic knowledge of the geodatabase.

01:12We won't be going into really in-depth concepts of the geodatabase itself.

01:16So we assume you have a little working knowledge of the geodatabase.

01:19Now, just a show of hands...who's currently using a geodatabase right now?

01:24Imagine most of the room.

01:25So you have a pretty good working knowledge of at least basic concepts in the geodatabase.

01:30If we could hold our questions till the end, that would be great.

01:33Me and Russell will both be up here until all the questions are answered.

01:37So if you have a question, if you could save it to the end of the session, if we still have some time on mike...

01:42...I could try to answer whatever questions you guys have to throw at us...

01:46...and then if we have to make room for the next presenters, we'll just stay around the front...

01:50...or we'll take it out in the hallway and we'll be sure to answer any questions that arise during the session.

01:58Okay, for starters, what is the geodatabase?

02:00Well, the geodatabase is really just a physical store of geographic data.

02:05At a very...at its very simplest, it's where you physically create and keep all your geodata, all your geographic information.

02:15It's a scalable storage model, and it's supported on many different DBMS and different platforms, really...

02:20...from enterprise geodatabases, supported on all the commercial DBMSs that we support...

02:26...as well as the file and personal geodatabase.

02:30It's also the core information model for the ArcGIS system.

02:33And it's really...we tried to make it a comprehensive model for modeling real-world entities and managing GIS data.

02:43It's implemented by a series of simple tables.

02:46We'll go into this a little bit in the following slides.

02:49And it's also a transactional model for managing GIS workflows, for updating and editing feature classes in GIS editing workflows.

03:01And since this is the Developer Summit, it's also a set of components for accessing the data...

03:08...and your geodata in those geographic data stores.

03:11So really, a geodatabase means a couple different things that I touched on in this slide.

03:20So the geodatabase is really based on core relational principles.

03:24So we've really extended what relational databases already have been doing well for a very long time...

03:31...which is, we're extending relational integrities with the GIS or geodatabase data model.

03:37And we also rely on the flexibility and scalability that are already built in to commercial RDBMS systems.

03:46We're using a very standard relational database schema...

03:49...and I'll explain a little bit about that schema as this presentation continues.

03:54And important to this presentation is we support the standard SQL-92, or a Structured Query Language specification.

04:08So leveraging some of the core DBMS principles...

04:13...data in the geodatabase is just organized into tables, and tables contain rows, and rows...

04:19...all rows in one table have the same attributes.

04:23Each attribute has a specific type in a DBMS, and relational integrity rules exist for tables...

04:31...for different tables that have some kind of meaningful relationship in the real world.

04:37Extending that a little further, a feature class or a spatial table in a geodatabase, it's just stored as a simple DBMS table.

04:46And the thing that makes it spatial is that it has a shape column or it has a spatial attribute as part of one of its attributes.

04:55So instead of it being a row in a table, we call it a feature.

04:58It's just a spatial row in a table.

05:01So a simple correlation between tables and what we call feature classes or spatial tables in a geodatabase.

05:11So what makes it spatial is this shape field, this geographic entity or this geographic type...

05:18...that's tacked on as one of the attributes to a simple table in the DBMS.

05:25So at 10.0, we changed the geodatabase schema pretty drastically, and we really subdivided the geodatabase schema...

05:32...into what we call the system tables and then the user data or just the user tables.

05:40There're really two sets.

05:42The user-defined tables are user data defined typically by the SQL type.

05:49So to store their spatial information in each one of those tables...

05:53...we store it using a ST_Geometry or a spatial type of some sort.

06:01What spatial types do is they enhance the capabilities of the geodatabase...

06:05...and they really provide SQL developers or provide SQL-level access to the properties of those geometries on each feature.

06:17Now the other sets of tables in the geodatabase are what we call the geodatabase schema, or the system tables.

06:22And they really support...store all the logic or all the behavior and rules of how objects in a geodatabase should act...

06:29...and should model themselves.

06:33These track all the contents of the geodatabase, and there's really just four primary tables.

06:39And the key thing to this session is that all the geodatabase schema information is stored in one key table...

06:46...and it's all accessible through what we call geodatabase XML, or a published XML definition of geodatabase schema.

06:53So here's a diagram of the four tables, and really the most interesting table is the GD_Items table.

07:01This is the key table that all the other...all the three other schema tables are related to.

07:07And this has a unique entry for each specific unique item in the geodatabase.

07:15Of particular note is the Definition attribute on this table.

07:18The definition is actually where the definition of that particular item lives.

07:23And that is stored in an XML type, so the XML type in SQL Server, depending on which DBMS the geodatabase is stored on...

07:30...the XML is stored in the Definition field.

07:36Now every item in the GDB_Items table has a type associated with it...

07:41...and that Type attribute can be joined to the ItemTypes table...

07:46...which is just a list of all the types that are possible within a geodatabase.

07:54Now one of the key things about the geodatabase schema is every item have different relationships to one another...

08:01...or relationships to other items in the geodatabase.

08:06So the types of relationships is stored in the relationship types table, and the relationship table actually models...

08:15...the relationships between two or many different items in the geodatabase.

08:23So as I mentioned before, user data is often stored, the spatial attribute, is stored in a spatial type.

08:28Now there are a number of different spatial types available to you as a developer and as a data modeler.

08:33The one that Esri provides is the ST_Geometry type.

08:37So what exactly is a spatial type?

08:38Well, at its simplest, the spatial type is just a database type, like an integer or a string, but it models geographic entities.

08:47It models geometry and stores that geometry in a table.

08:54Now since this is a database type, this type knows how to index itself.

08:58It knows if it's queried against, how to quickly return that query.

09:03So our ST_Geometry type and other spatial types, like SDO_GEOMETRY in Oracle, for instance...

09:09...have a way to index themselves correctly for efficient query, and the index can be used with efficient querying.

09:19Now when you are querying and using a spatial type, you query it through relational or geometry functions...

09:27...in order to do spatial and attribute queries of that particular type or values in that type, more specifically.

09:34The type also includes constructors and accessors so you can create new tables with these types, with these attributes in them...

09:44...or create new values and insert those values into the tables.

09:49So when you install the ST_Geometry type, or really any other type in a DBMS, you not only get the spatial index...

09:56...and the functions that go along with it, but you also get the constructors, you get the relational operators...

10:02...spatial relational operators, and other methods to gather information, access the geometry, and aspects of those geometries.

10:15So why would one want to actually use the spatial type versus, say, a binary type in SDE...

10:20...or other ways to store spatial information in a DBMS system?

10:25Well, efficiency is a really good reason.

10:27Spatial data and methods are stored directly in the database.

10:30It means that we can leverage stuff like optimizers in the database and optimize queries directly against the spatial type.

10:39So it's very efficient to store it in a spatial type in the database, and queries are much more efficient when you store it in a spatial type.

10:47Another big reason is that geometries, when they're stored in a spatial type, can then be accessed through common APIs...

10:55...and through SQL, through Structured Query Language, which means that you don't need ArcGIS Desktop...

11:01...you don't need ArcObjects, you really don't need any Esri DLLs at all to access your geometries.

11:07So it really opens all your geometry that might have been put there by ArcGIS Desktop or by ArcGIS Server.

11:13It opens those geometries up to any application that can read and write SQL.

11:18So it really opens that type up and opens up your geographic data so you don't have to have ArcGIS Desktop...

11:25...or another Esri product around just to get access to your geographic information.

11:32And in order to enforce this openness, we, when building the ST_Geometry type...

11:37...we took great pains to adhere to a bunch of different standards for SQL access.

11:45So we conform to a lot of the open standards for accessing SQL objects through a database...

11:51...just to make sure that our type was very open and accessible to a bunch of different developers...

11:56...writing on a bunch of different platforms.

12:02So this is a table holding a spatial type.

12:05And, really, the spatial type just looks like any other shape field.

12:09It has polygon objects, but in this case, those are all ST_Geometry objects, which represent different features in a spatial table...

12:17...in a geodatabase.

12:20So using SQL against a table with a spatial type in it, you can create tables with other spatial attributes.

12:27So if I wanted to call CreateTable through SQL, I could insert a new row with an ST_Geometry type...

12:33...so create a new feature class essentially all through SQL.

12:37And then I can read and analyze spatial data directly at the SQL level, and I'll show a bunch of demos of this...

12:42...or examples of this in the demos I'll show after these slides.

12:46You could also insert, update, delete, do all the editing that you normally would to nonspatial tables directly through SQL.

12:53So that means editing geometry attributes, changing the geometry, inserting new geometries...

13:00...as well as updating nonspatial attributes like you always could through SQL.

13:09So accessing the geodatabase through SQL is also very open, because at 10.0...

13:14...we exposed all the properties of the geodatabase through XML and that Definition attribute in the GDB_Items table.

13:23So we did that intentionally.

13:24So it wasn't locked away in BLOBs that you needed some Esri DLLs just to decode this BLOB...

13:31...to understand information about your geodatabase schema.

13:34It's all there in XML.

13:36So that means through SQL, you could access that XML and do a couple joins between those four tables...

13:42...to determine the relationships that are in your geodatabase, and you don't need any Esri technology...

13:47...anywhere close to the solutions you're developing.

13:50You can do it all through SQL.

13:52This means editing tables and features classes can be done through SQL.

13:56This is both for nonversioned and versioned feature classes, and I'll show example of each in my demos.

14:10So this is a little bit of a layer cake of where SQL sits in the stack, really at the top.

14:16And what Russell'll be talking about in the second half of the presentation is accessing your geodatabase through Python.

14:22And Python goes through the rest of the ArcGIS system, which means it goes essentially through the ArcObjects stack.

14:29So it knows about all the geodatabase behavior and all the geodatabase objects that exist on top of the database.

14:37Now SQL is a little different because it goes directly at the database.

14:41So you're going right to the database and accessing the database directly through the Structured Query Language.

14:48So why I mention this in this slide and why I think it's important for you to understand as a developer...

14:53...is that you're really bypassing a lot of the geodatabase behavior.

14:57Now, we've provided a lot of functions at 10.0 and at 10.1 for developers to call...

15:03...to make sure they're doing things in a way that doesn't affect the rest of the users in their organization...

15:09...that might be accessing the geodatabase through ArcMap, through ArcGIS Server.

15:13So there's functions you can call as a developer, but you've just got to be wary of what you're doing...

15:18...how I'm editing, is what I'm editing, does it have complex geodatabase behavior...

15:23...and if it does, you might not be able to edit it.

15:25So there's some questions you can ask and some stored procedures and functions you can call...

15:29...in the logic that you execute through SQL.

15:40So you can use SQL to insert and update tables, and if you create a new table in the geodatabase...

15:48...keep in mind that you need to register that table with the geodatabase schema...

15:55...essentially we call it registering it with the geodatabase, if you want that table you create through SQL...

16:00...to participate in any geodatabase functionality.

16:04So say if you want to create a bunch of spatial tables and you want them to be part of a geometric network...

16:09...you have to first register those feature classes with the geodatabase.

16:13And what that does is just put an entry in the GDB_Items table to tell the geodatabase...

16:17...here's a couple more tables that might participate in behavior, so put some new rows in the GDB_Items table.

16:24Now if you just want to use those tables as simple spatial tables to edit or to display some information...

16:30...maybe publish it to Server, you don't need to register those with the geodatabase.

16:35It's really only if you want to use these tables in higher level geodatabase functionality that you need to register these objects.

16:47So editing feature classes with SQL.

16:51So you can edit points, lines, polygon, both single and multipart...

16:55...so pretty much any geographic editing that you can imagine is also available through SQL...

17:02...through the constructors of the ST_Geometry types.

17:06And you could also modify geometry.

17:09So if you have an existing polygon or point, you need to change that geometry...

17:13...it's all done through WKT representations of the geometry or WKB representations of those geometries through SQL.

17:23And you can use SQL statements to edit tables and feature classes and directly edit these tables...

17:29...whether they're versioned or nonversioned.

17:35Editing versioned tables requires version views.

17:39So you'd have to actually edit through what we call a version view.

17:42And the version view knows about the A and B tables involved in versioning...

17:47...and will put the right edits in the right A and B tables.

17:50So if I was to edit a versioned feature class through SQL and edit that versioned view...

17:55...when Russell goes and logs in to his web service or his instance of ArcMap and accesses the same versioned feature class...

18:04...in a specific version, he'll see that version's representation.

18:07So as long as I, as a developer, write an application that updates that versioned feature class...

18:13...through the versioned view associated with that feature class, Russell will see all of my edits...

18:17...like I was just editing it through ArcMap.

18:28So one thing to remember when you're editing nonversioned feature classes is that it can leverage all DBMS functionality...

18:36...that you might have built into that table.

18:39So think about check constraints.

18:41Think about unique indexes on fields, referential integrity that you've built into your DBMS, all these triggers...

18:49...all these things will be fired if you, as a developer, are directly editing through SQL.

18:54So that's one advantage to accessing something through SQL versus, say, a feature service or through ArcMap.

19:02When you are editing a feature class, a nonversioned feature class, it requires you, as a developer, to insert the unique identifier.

19:11And we have a stored procedure or function you can call to get the next object ID value for your insert statements.

19:18But the responsibility is yours as a developer to determine what the next object ID is so that that row...

19:25...that record, that feature that you're creating has an up-to-date object ID value.

19:36So a little bit more on editing versioned tables through SQL.

19:41You have to use versioned views.

19:42Now we made this a little bit easier for you as a developer.

19:45At 10.1, anytime you register a feature class as versioned in ArcGIS...

19:51...we'll just automatically go ahead and create the versioned view.

19:54Prior to 10.1...alright, some people like that. So we automatically create that versioned view...

20:01...so you don't need to worry about it as a developer.

20:02If it's versioned at 10.1, it will have a versioned view.

20:06And we actually have functions that you can call through SQL to get the versioned view name.

20:10Typically, it's just a class name with an underscore VW past it, but it's always to get robust to call the procedure...

20:18...and get the latest versioned view name.

20:20Prior to 10.1, you just had to use an sde command line function which created the versioned view.

20:29I have a code example here of how to perform edits.

20:33So one thing you have to remember is when you're editing a version, you have to tell that specific version...

20:38...that you're going to be editing to create a new state for my edits.

20:43So that's this sde_edit_version call.

20:47You can see that I'm passing in the name of the version.

20:50Here it's a work order, and I'm just passing in 1 for the second parameter.

20:56That second parameter is just telling it, I want to open a new state.

21:01So you'll open a new state.

21:02You'll make your edits, and then you'll just close that state on the WorkOrder version.

21:08It's essentially like in ArcMap starting an edit session on a version and then stopping an edit session.

21:14This is how you do it through SQL.

21:18So enough talking by me.

21:19I'll jump into a couple demos explaining some stuff that I've been touching on in the demo, or in the slides.

21:32So I'll start off in SQL Server Management Studio, and I have a SQL Server geodatabase here...

21:40...and I just want to do a simple select from the GDB_Items table.

21:45It's the first thing I'll do.

21:46I have the results up, but I'll refresh it, and the key thing here is this Definition field.

21:53Pretty much everything keys off the definition.

21:55That's where all the good stuff is.

21:57So if I select any of these XML values, it'll open the XML document.

22:03And this is an XML doc. It uses geodatabase XML, and it just shows me the values of a coded value domain.

22:10Now, I wouldn't expect you to be able to read this.

22:13But you can harvest the nodes of this XML doc to get information about this specific coded value.

22:18And I'll show you some examples of it now.

22:22First example is a simple problem.

22:25I want to find all the specific feature classes that are contained in a feature dataset called Landbase.

22:33So the first thing I do is I find all the datasets that are contained by querying the GDB_Items table specific to this container...

22:44...called the Landbase feature dataset.

22:48And then I use the UUID, which is the unique identifier of that dataset, to find any relationships to that container.

22:57So I do a join to the relationships table, and then I get the...I get all the dataset types as human-readable strings here.

23:07So if I was to execute this query, I'll just get a listing of all the different feature classes that are participating...

23:16...or are contained by the Landbase feature dataset.

23:20Now I could change that up to be, instead of a feature dataset, I could change that up to be a topology of a different name...

23:25...and then I could find all the feature classes that participate in a given topology.

23:32So next example I'd like to show is getting all the code value domains.

23:36So this was always a problem in the past, prior to 10.0, because coded values we always locked away in a BLOB...

23:44...sitting in the geodatabase schema.

23:46So this wasn't accessible until you had essentially ArcObjects to decode the coded values.

23:51But since this is in XML now, it's accessible.

23:54So I'm really just using the cross apply function in SQL Server to look for the specific nodes which define all the coded values.

24:04So if I was to run this SQL, I'll get all the coded values, their codes, and their values corresponding to those coded values.

24:14So if I scroll through this, you could see all the coded values in my geodatabase.

24:28But say you wanted to use those coded values to resolve a nice result set.

24:32Instead of showing me integer codes or whatever the range the main codes might be...

24:38...I just want to see the values in the result set.

24:40Maybe I return for a query for server, something like that.

24:43I can use this SQL to resolve all the values in a nice result set.

24:50So, again, I'm doing a join from the GDB_Items table to the relationships...

24:55...and harvesting the information in the XML definition.

24:58So here I have an employee coded value, which is an integer coded value...

25:04...and here I'm resolving it with the textual representation of that coded value.

25:09So instead of seeing integers, which no one really understands, which integer 42 replies to...

25:15...but you do understand the different inspectors.

25:19So I could return these result sets and maybe formulate queries through SQL to return this to an application I might be writing...

25:26...instead of showing the codes.

25:32[Inaudible audience question]

25:38The question was, How different are these queries with Oracle?

25:42Oracle is a little bit different, just because they don't have the cross apply function.

25:47So what we do for Oracle is we have a view where all this information is available through strings.

25:53So Oracle doesn't actually have an XML type, so we couldn't extend the XML type for the definition field.

25:59But we have a view that you can query that looks very similar in schema to the GDB_Items table...

26:05...and you can work with the definition field like it was a string looking like XML.

26:11So it'd be...the information is still there.

26:14You'd access it slightly differently, and we had to do that...

26:18...because the releases of Oracle we supported don't have a native XML type.

26:25So the last thing I want to...just sticking with the coded value domain example...

26:29...say I wanted to change a domain in my geodatabase.

26:33Well, the problem was always, I don't know which feature classes, which field...

26:37...and which feature classes are accessing that specific domain.

26:40So I want to find out all the feature classes that might have reference to that domain.

26:44So I could do the same thing by using an interjoin to the relationships table...

26:51...and finding all the feature classes that participate in that given employee domain that I was referencing before.

26:58So running this query, will go ahead and tell me all the feature classes that might reference the employee domain.

27:05So I know which features classes could be affected if I were to take away values from that domain...

27:10...or maybe add new values or drop the domain altogether.

27:14So this is all accessible through SQL.

27:17So let me jump over to pgAdmin.

27:20I have a Postgres geodatabase as well, and I'd like to show some examples of editing.

27:24So the previous examples are all how to access aspects of your geodatabase through SQL.

27:30But the first thing I'd like to show is just how to edit a nonversioned feature class.

27:34So in ArcMap, I have a simple map document.

27:38My buildings here are a nonversioned feature class.

27:42My parcel centroids are a versioned feature class.

27:46But I want to first edit the buildings, the nonversioned feature class, directly through SQL.

27:54So here I am referencing the building feature class.

27:58Now Russell just created this using Python.

28:01He created this in the geodatabase.

28:02I'm a SQL developer, so I want to insert some new geometries into that class through SQL.

28:09Now remember how I mentioned you have to come up with your own object ID?

28:12Well, we gave you a function called next_rowID that just draws from the same sequence that ArcMap editing does...

28:19...so if we're both editing through different applications, we're pulling from the same sequence, you just pass in the class...

28:25...and the class's owner, and it'll give you the next object ID.

28:29Another thing I'd like to point out is I'm using the st_geometry constructor to construct a new geometry object.

28:36And here I'm passing in the WKT representation of a polygon.

28:45If I was to run through this SQL and going back to my map document, refreshing my map document...

28:52...you can see the new polygon I just added.

28:54So I can do that directly through SQL.

28:56If Russell was accessing this through ArcMap, he would refresh his map document...

29:01...and it would look to him like I just added this through ArcMap.

29:03He would have no idea I was doing it directly through SQL.

29:06Yeah, we have a question off to the right.

29:07[Inaudible audience question]

29:12You mean PostGIS? Yeah, Post...

29:16So the question was, Is there a PGgeometry or a PostGIS type constructor analogous to this st_geometry constructor?

29:23And I believe there is. Yeah. There's a very analogous constructor for PostGIS, which is a spatial type for Postgres...

29:33...for those of you who don't know.

29:36So that was an example of editing a nonversioned feature class.

29:40But you could do something very similar to edit a versioned row.

29:44So my next example involves editing a versioned row, and I'm...

29:48...this is the part where I start the state on the WorkOrder version.

29:52So I tell the WorkOrder version, give me a new state to edit in, so I call that sde_edit_version, passing in 1.

29:59I begin the transaction after I've opened that state for editing.

30:03And then I'm just inserting again, using the st_geometry constructor.

30:07In this case, I'm creating a point instead of creating a polygon, and then I commit my transaction.

30:14And one key thing to remember is you want to close that state.

30:17You opened it to start editing.

30:18Once you've committed the transaction, you pass in 2 as the second parameter, it closes that specific state in the geodatabase.

30:36So I executed that SQL.

30:37If I was to go back to ArcMap, and here I have to refresh the actual version.

30:44You see now I have a new polygon centroid, and I edited that directly through SQL.

30:49So, again, if Russell was accessing that version and he refreshed his version, it would look to him like I was just version editing...

30:55...either through a feature service or through ArcMap or ArcGIS.

31:00Now the last thing I'd like to show is the ability to execute some complex SQL statements directly in ArcGIS, ArcGIS Desktop.

31:08So in SQL, I have a little placeholder, and here I'm trying to do a spatial intersection between primary overhead feature...

31:17...and the parcels.

31:18So I want to notify all the parcel owners that I'm going to be doing some work on that primary overhead electrical feature.

31:26So I have to do a join between the owners table to get the owner names, the parcels, and also the primary overhead.

31:34So the first thing I do is I use a spatial operator to do an ST buffer.

31:38So this is just like the Buffer GP tool.

31:41It just does it through the ST_Geometry type.

31:43It creates a buffered shape based on a given diameter, and then I'm going to take that shape and intersect it with any parcels.

31:52So I'm using two spatial operators, first a buffer, then I'm doing an intersect in the WHERE clause.

32:01So let me copy this SQL, and at 10.1, we've given you the ability to create views directly in databases and geodatabases.

32:11So opening the Catalog window here, I can just right-click on my geodatabase and just select New > View.

32:20And this allows me, as a developer, to persist what could be complex SQL statements as a view in the database...

32:28...and it will just show up like a feature class or a table, depending on if it has a spatial result.

32:33So I'll just call this demo_view, paste that SQL in, and this issues a CreateView call in the database.

32:42So if you have create view privileges, which I do, let me open the Catalog window again...

32:51...and you see my demoview just shows up as a feature class.

32:55So when I query it, it finds out that it's a polygon entity.

32:59I can go ahead and just drag and drop this into ArcMap.

33:03I'll select the parcel ID as the unique identifier.

33:07And this'll add this just like any other spatial feature class into ArcMap.

33:15[Inaudible audience question]

33:26Yeah. That's a very good question.

33:27So the question was, Is this layer essentially dynamic?

33:30And so this query, this SQL is actually stored with the layer.

33:34So it means that if the underlying feature classes change, underlying tables that comprise the view or comprise that SQL...

33:42...if they change in any way, when I refresh the map document, the results, that will change. Yeah.

33:49Something to keep in mind. This is actually using the query layer technology that we added at 10.0.

33:54But in 10.1, we just wanted to make it easier, so now you can connect directly to databases...

33:59...and just drag views or actually query layers directly...spatial tables directly in the map.

34:05But one thing to keep in mind, if you have some very complicated SQL...

34:08...you might not want to execute that every time you pan and zoom in map.

34:12So as a developer, there's ways to work around that, but it is dynamic, which is a very nice thing.

34:17So as things change, you get to see the latest and greatest.

34:21[Inaudible audience question]

34:32Yeah. So the question was, Could I actually just create the view in my SQL environment?

34:35Wouldn't it show up when I connect to the geodatabase or connect to a database?

34:39And the answer is yes.

34:41So I created it through ArcGIS Desktop, through ArcMap.

34:44But if I were to go to the command prompt or go to pgAdmin and said, create view, same SQL...

34:50...it would show up as soon as I refreshed my geodatabase.

34:52So all we're doing there is just going to the database, querying all the tables, giving a list of all the tables...

34:59...and if they're spatial, we determine the entity.

35:02Yeah. Yeah.

35:04[Inaudible audience question]

35:06Question is, Do you have to register it?

35:07No, you don't.

35:09If you just want to use it...if you don't want it to participate in any geodatabase functionality, you don't have to register it.

35:14If you just want to map it, if you want to publish it to Server, just drag it in the map and publish.

35:18You'd only register with the geodatabase if you wanted it to be in a topology or in a geometric network or, you know...

35:26...apply domains to different fields and things like that.

35:31Alright. One more question and then I've got to turn it over to Russell or else he's going to probably not buy me beers tonight.

35:37So one question off to the right.

35:40[Inaudible audience question]

35:45That's a very good question.

35:46So the question is, Why is the field name ESRI_OID?

35:50In this case, I'm using a string field as a unique identifier.

35:56So in that case, ArcGIS, the system has to use an integer as a unique identifier.

36:01So when the class is created in memory, it actually just generates an Esri OID from the string, a hash table of this string.

36:09So it's actually generating a unique identifier based on unique values in that string.

36:15So you don't need an object ID field or a unique integer field in order to just add a table from a database to ArcMap.

36:24As long as it's unique, we can generate those unique identifiers kind of behind the scenes...

36:28...and use those to uniquely identify the rows.

36:32Alright. So in this demo, I demonstrated how to access your geodatabase...

36:38...primarily through going through the GDB_Items table and working with the XML definition of items in that table...

36:45...and joining to the three other geodatabase schema tables.

36:50I also demonstrated how to both edit a nonversioned and versioned feature class...

36:56...and then execute complex SQL directly through ArcGIS Desktop by creating views...

37:01...and then just dragging those views in the map and working with them in ArcMap.

37:05So I'll turn it over now for a little bit of a change of pace and change of at least development platform to Russell...

37:13...to talk about Python and everything you can do for administration through Python.

37:19Alright. Thanks, Brent. Can you guys hear me? Yeah. Sound good in the back? Okay.

37:25So, yeah. So, thanks, Brent. I'm Russell Brennan, product engineer on the geodatabase team.

37:31The second half of this session, I'm going to be going through geodatabase administration using Python.

37:38So, just quickly, I'm going to talk about why you should use Python to administer your geodatabase...

37:43...some tips for how to use Python to access your geodatabase...

37:47...and then I'm going to go into a few demos on common administrative tasks.

37:54So many of you might be asking, why should I use my...why should I use Python to administer my geodatabase?

38:00Well, we've really spent a lot of time in the last few releases trying to make this really easy for you through geoprocessing...

38:08...and through some of the new tools and Python functions that we've implemented.

38:11At 10.1, especially, we've really focused on making geodatabase administration really easy.

38:18For those of you that are working cross-platforms, Python is a cross-platform language, so if you have your database on Linux...

38:25...you can run scripts directly on Linux using Python, and you can administer your geodatabase that way.

38:32Finally, for a lot of geodatabase administrators, you're going to need to schedule tasks to run on a regular basis...

38:38...and using your operating system task scheduling and Python scripts, it's really easy to do this...

38:43...and I'm going to get into a demo later showing how to do that.

38:50When you're accessing your geodatabase using Python, you're always going to be using connection files.

38:55There's a Create Database Connection File tool that allows you to create your connection files.

39:00You can also use files like you would through ArcGIS Desktop to connect to your geodatabase.

39:08Version access to your geodatabase is always defined in your connection file, so if you need to do things in different versions...

39:15...you want to make particular edits to a particular version, maybe using some of our geoprocessing editing tools...

39:20...or using some of our cursors, you need to define your version in the connection file...

39:25...when you're making that connection file.

39:28This is different from some of the other languages where you can actually get a workspace...

39:32...and then switch out the version on that workspace.

39:39When you want to make connections as different users, this is also defined in the connection file.

39:44So if you want to connect as user A, make some edits, and then you need to do something as user B...

39:50...you're going to need to make a connection file for the second user as well.

39:54So, really, what this comes down to is anytime you need to change any of your connection properties...

39:59...you're going to be needing to create new connection files.

40:02And this is something that often hangs people up when they're working with geodatabases is...

40:06...how do I get access using these different properties?

40:11So now I'm just going to go straight into my demos.

40:14The first thing I'm going to do is one of the common admin tasks is creating a geodatabase...

40:19...and getting it set up and ready for users to start working with.

40:22Question.

40:24[Inaudible audience question]

40:32The question was, Is it still required to have an ArcEdit license to create the connection file in 10.1?

40:38I believe the answer is yes. I can double-check, though, after the session.

40:43Yeah, licensing hasn't changed.

40:44Yeah, I don't...Brent tells me licensing hasn't changed.

40:52So in this demo, I'm really going to highlight some of the new functionality that we've implemented in 10.1.

40:58I've started this script already, and so what we're going to do in this script is we're going to create our geodatabase...

41:04...we're going to create users and roles as well as versions.

41:09So I'll get started.

41:11I've imported ArcPy.

41:12I've set my environments, and now I'm going to set a number of variables that I'm going to use in a number of the tools.

41:21The first tool that we're going to use, and this is a new tool, the Create Enterprise Geodatabase.

41:25So this new GP tool is new at 10.1, and it's going to replace the former ArcSDE postinstallation.

41:33You can now create your geodatabase schema using a geoprocessing tool.

41:37So we need to provide some key parameters to make our connection, our...the platform that we're using...

41:43...in this case, we're using SQL Server; the instance; and then our user name and password as well as an authorization file.

41:51And what this will do is this is actually going to create all of the geodatabase schema in your instance.

42:06So once we have our geodatabase created, we need to make a connection to actually access this geodatabase.

42:12So I'm using the Create Database Connection File tool, and you can see I'm persisting it onto my hard drive as a connection file.

42:20Once I have that connection, we can now use another new tool, the Create Role tool.

42:26In this case, we need to provide our administrative connection and the name of the role that we'd like to create.

42:31In this case, I'm going to create two roles, one for all of our viewers and one for all of our editors.

42:40So once I have the roles created, what we need to do is create all of the users that are going to be in our geodatabase.

42:46In this case, I've created a Python list that holds a list of all the viewers that we want to...all of the editors we want to create...

42:53...and all of the users we want to create.

42:56Once I have these lists, I'm actually going to loop through each of these lists and use the Create Database User tool...

43:01...which, again, is another new tool in 10.1 that allows you to create users in your geodatabase.

43:07And one of the key parameters here is I can actually, as I'm creating the user...

43:10...add them to the role that I want them to participate in.

43:17Finally, I want one last user.

43:19This user is my GDB user.

43:21In this case, this person is...this user is going to be the user that owns all of the data in my geodatabase.

43:27So I create this user, and then I'm going to create a connection as this user so I can connect to the database and load data.

43:41So keeping with the theme of new tools, we have Import XML Workspace Document.

43:46This is functionality that's been around for a while, but as a GP tool, this, again, is a new tool at 10.1.

43:54So I have an XML schema that defines all of the data in my geodatabase...

43:57...and I'm going to use this tool to import all of the data, feature classes, feature datasets...

44:02...domains, et cetera, into my geodatabase.

44:10I'll take one moment.

44:12So once the XML is imported into my geodatabase, I need to figure out exactly what I've done.

44:21What are all the datasets in my geodatabase?

44:25And the way you get at all of your datasets in Python is using these list functions.

44:30So in this case, I'm going to be applying permissions to all of this data...

44:34...so I know I have tables, feature classes, and datasets in my geodatabase...

44:40...and I'm going to use the listing functions to get a list of all of these datasets and tables.

44:45Once I have that list, I can then go into my Change Privileges tool, and using the roles that I created earlier...

44:53...I can grant view privileges to my viewers, and I can grant editing privileges to my editors.

45:09So once I've granted the privileges, the next thing I want to do, in my case, I'm going to register all of my data as versioned.

45:16So, again, I can iterate through the dataset list that I've created and register all of my data as versioned.

45:31Finally, once it's all registered as versioned, I'm going to create individual edit versions for each of my editors.

45:37So I have my editor list.

45:38I'm going to create a connection for each editor and then connect as that particular editor user...

45:44...and create a version for each user.

45:46In this case, I'm creating private versions, and I'm naming it after my user.

45:56So what we can see here when I'm done is that we have a fully functional geodatabase...

46:02...that has all of the data that we'd expect.

46:05We have our privileges have been set for our users.

46:09We can see here that we have, when it opens, we can see that our editor role has been granted...

46:17...select, insert, update, and delete privileges.

46:19Our viewers have select privileges.

46:22We can see all of our features in our feature dataset.

46:25We can see that all of our domains have been imported...

46:31...and this was all done when I was importing the XML workspace document.

46:35We can look in SQL Server, and we can see that our geodatabase, it was called devsummit1, was created.

46:45We can look and see that all of our users, as expected, were created.

46:49Our roles were created as well.

46:51Our editor and viewer role was created, and the users were actually added to these roles as well.

46:57We can see everybody was added.

47:02So what we saw here that these items here highlighted in orange is all new functionality at 10.1.

47:08We have create enterprise geodatabase.

47:10We've created database roles and users.

47:13We imported schema.

47:15We were able to apply privileges, register as versioned, and we were able to create these edit versions.

47:19So this is something that would be very common and very easily now...very easy to script for any geodatabase administrator.

47:27[Audience question] Does the software work in earlier versions of SDE?

47:32Does which?

47:33[Inaudible audience question]

47:37That's a good question. I think that everything other than the, well, yeah, I think all of those tools will work at previous releases.

47:46I was thinking about the Create Enterprise Geodatabase, but, obviously that's going to create a current geodatabase.

47:53[Inaudible audience question]

47:55Yes. Yeah. It's supported on Oracle, Postgres, and SQL Server.

48:00[Inaudible audience question]

48:06The question was, Do they have to be SQL users, database users, or can they be operating system authenticated users?

48:13And the answer is you can create both.

48:15So if you have an operating system user in your domain, you can specify that user name...

48:20...and it will create what's needed on the database side for you.

48:26[Inaudible audience question]

48:37Okay. One more question.

48:40[Inaudible audience question]

48:58I'm not sure about what...the current status of AIX.

49:04[Inaudible audience question]

49:09Okay. Yeah, if you want to come see me afterwards, I can get somebody else up here to answer the AIX-related questions.

49:16Alright.

49:17[Inaudible audience question]

49:21The question was, Where can you find my sample code?

49:24Come up...anybody with any more questions, come up and see me afterwards. We're running a little bit late on time.

49:31I will be posting it to the resource center afterwards.

49:34But, yeah, we're running a little bit late, so I'm going to just move on.

49:40So the next thing that we're going to want to do, another common task for geodatabase administrators...

49:44...is performing geodatabase maintenance.

49:46So this is reconciling and posting your versions, performing compresses...

49:50...updating your statistics and indexes on all of your data, and then I'm going to finally show keeping track of this...

49:57...sending an e-mail update to yourself as well as scheduling it to run.

50:11So you can see here, I'm importing the required modules that I need.

50:16I'm making an admin connection to my geodatabase.

50:19I'm setting up some of my environment variables.

50:24It's going to take a moment. It seems like today everything's running a little bit slow.

50:29Okay. So the first thing that we're going to do as part of our maintenance is...

50:33...we're going to block new connections to the geodatabase.

50:36So this is a new Python function at 10.1.

50:40This is not available as a GP tool, only as a function.

50:43And it allows you to accept or block connections to your geodatabase.

50:50The next thing we're going to do is, in this case, we're going to disconnect all of our users.

50:54Another new Python function not available as a tool, only through scripting or through the ArcGIS Desktop UI.

51:02Once we've disconnected users, we're going to use the ListVersions function...

51:06...and this is using the data access ListVersions function that's new at 10.1.

51:10This actually returns version objects which you can then get more information from.

51:1610.0 and previous list versions would just return a string.

51:20So in this case, we're going to get all of the versions that are children of the default version.

51:27Next, we're going to reconcile all of these versions with the default version.

51:33We're going to...and the one new thing with this tool is that the Reconcile Versions tool, which is, again, a new tool at 10.1.

51:40We've replaced the Reconcile Version tool, which would do an individual version one at a time.

51:46This tool allows you to pass in a list of versions to reconcile with a particular target version.

51:52This tool...we now provide a log file that keeps track of whether the reconcile or post is successful...

51:57...and it just writes out to a text file.

52:01So part of what I'm doing in this script, I'm also going to be keeping track of all the success or failure of any of the tools.

52:08And I'm going to use this to send myself a summary e-mail when the script completes.

52:15So I reconcile my versions.

52:19I keep track of all the messages.

52:22I'm actually opening up the log file here and reading the contents of the log file so that I can send that to myself later on.

52:28I then run a compress and, again, keep track of messages.

52:33Now that the reconcile, post, and compress is completed, I'm going to allow connections to my geodatabase again.

52:38So I'm going to use the AcceptConnections function.

52:43I then need to update the statistics and indexes on all my data.

52:47So I need to connect as a data owner and update the statistics on all of my data.

52:52In this case, I'm passing in a connection file, and I'm describing this connection file to get the connection properties...

52:59...and the user name, which I'm then going to use to construct my data list specifying a wildcard on all of the listing functions.

53:09So I'm going to grab all my tables, feature classes, and rasters.

53:13I'm then going to get all of the feature classes inside of any of the feature datasets in my geodatabase.

53:21Once I have this list, I can then pass it in to the Rebuild Indexes and Analyze Datasets tools, and these tools are...

53:28...the Rebuild Indexes will update the spatial and attribute indexes.

53:32If I connect it as my system administrator, I could update all of the system table statistics and indexes as well.

53:39So I will run these tools to update my indexes and statistics.

53:45Once this completes, I'm going to set a flag to indicate that the script ran successfully...

53:50...which I'm then going to use to determine the messages that I'm going to write in my e-mail.

53:57So I've written a function that will actually send an e-mail, and based on the success or failure of all of my tools...

54:05...I will send a success message or a failure message.

54:11So I'll run that, and I should get an e-mail in my in-box letting me know the reconcile and post was executed successfully.

54:22I have a printout of my reconcile log indicating the versions were successfully reconciled and posted.

54:28I then have indications that all...everything else was successful.

54:33So if you have a script like this, this is something that we've always recommended you want to run on a regular basis.

54:38And the next logical thing to do is to schedule this on your operating system to run.

54:43So you could do this on any operating system.

54:46In this case, I'm going to obviously show Windows.

54:49And it's really simple.

54:51I've opened up the task scheduler.

54:53I'm going to create a basic task.

55:01I'm going to give it a name.

55:07I'm going to choose when I want it to run.

55:09So in this case, I'm going to run it Monday, Wednesday, and Friday, and I'm going to do it at two a.m. starting today.

55:24I'm going to start a program with this, and the program that I'm going to start is actually just the Python executable.

55:30So this is in my Python27\ArcGIS10.1 directory.

55:33I browse for the Python .exe, provide that, and then I just need to provide the argument...

55:40...which is actually the script that I want to run using that command.

55:45So I provide that. Next. Finish.

55:49And now I have a script that's going to run three times a week at two a.m. to keep my geodatabase performing well.

56:00So what we saw here were some new Python functions for blocking and accepting connections, for disconnecting users.

56:07We saw the new...the batch reconcile capabilities of the Reconcile Versions GP tool.

56:13We saw compressing your geodatabase, updating statistics and indexes using the Rebuild Indexes and Analyze Datasets tools.

56:20And then we saw how to keep track of that, send e-mail notifications, and then how to schedule that to run.

56:28So that's it. Thanks for attending.

56:31I have some surveys up here if anybody would like to fill out some surveys.

56:35We also have our slide deck available. If you have a QR code reader...

56:41Our slides are all available here at this URL at the bottom...

56:44...or if you have a QR reader, you can access it using this.

56:47So, yeah, I'll open it up to questions; I think we still have a few minutes.