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: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: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: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: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.
Accessing and Administering Your Enterprise Geodatabase through SQL and Python
Brent Pierce and Russell Brennan discuss effective patterns for administering the geodatabase and working with its contents.
- Recorded: Mar 28th, 2012
- Runtime: 56:51
- Views: 1477
- Published: Apr 24th, 2012
- Night Mode (Off)Automatically dim the web site while the video is playing. A few seconds after you start watching the video and stop moving your mouse, your screen will dim. You can auto save this option if you login.
- HTML5 Video (Off) Play videos using HTML5 Video instead of flash. A modern web browser is required to view videos using HTML5.