[MUSIC] Hello everyone and welcome back. In this lecture I'm going to show you a preview of the features available in SQLite geodatabases. These are a new feature in ArcGIS 10.2 that they sort of quietly disabled in 10.3, but that are also available in ArcGIS Pro. As we seems to be signaling that personal geodatabases are going away and SQLite geodatabases appear to be coming in. And we'll see where that goes but I want to give you a sense for the features of SQLite geodatabases as they compare to the existing formats that we have. So first, what's a SQLite database? Well, SQLite is a free format that is accessible for many different programming languages and accessible for many different environments. So it makes a nice data container because, just like Microsoft Access, it's just one file. So right here we have a personal true database, Microsoft Access database and we have a SQLite database. And both of them are just a single file except the SQLite format is is modern and maintained. It lacks some of the special features that the Microsoft access format has like reporting and forms. But we got some other nice benefits from it. So, in ArcGIS it shows up just as a geodatabase the same as any other, and inside I can store tables, views, and future classes. Now, we'll notice, if I go to the new options, that there are no rasters, there are no tool boxes, anything like that. All we get are future classes, tables, and views. And I should explain what views are. Views are queries against tables in your database, and they automatically update based upon the contents of the underlying table. So I can create joins in my queries that become effectively new tables in the database live. I don't have to save out new copies of the dataset. It's that it's always the most up to date version of that join or that query. I'll show you one in action in just a moment. So, we have kind of a lightweight container here, right? We can only store really two major types of data, we can store the tables and the feature classes in here. But it works pretty well for that and we don't have some of the same limits that we have on the Microsoft access databases. And Esri seems to be evolving their version of the format, too. As I said we can't create these from within ArcMap right now though. If I go to New I only get file and personal geodatabase or connections to geodatabase servers. Right now you create these from a command line prompt in ArcGIS Pro. In the future they'll likely add a way to create it in the user interface. Now, let's add some data to our map here. So, I'll drag and drop it in and first we'll notice that it's calculating the input extent. That's a little different than we get when we bring in layers from other sources here. And I meant to bring in the county so let's just go with that. And I'll remove the boundary here. And great, it's feature data in our map. And I can open the attribute table and view that as well, wonderful. Now, the first sign that something was weird was when it was calculating that extent, but the second sign that things are a little weird. Or if I go to the properties, and under the Source tab, I get a data type of query feature class, which I don't get if I bring things in from other geodatabases. So let's bring in, let's bring in a layer from this geodatabase here and if I go to properties on that new layer I gotta file geodatabase feature class. So let's cancel out, this is not a SQLite geodatabase feature class this is a query feature class. And what that means is that it's actually executing a query against the database just like we do with select by attributes. It's actually getting a query that gives it the results of the layer. So it runs a select all of these field names from that object name of the database and that returns all the features because the shape field is returned as well. So this makes an interesting mechanism and a lot of potential options for what you could do with this. The flip side of it is that as of right now, it means that you can't edit these in Arc map. If I used editor, I can't make changes to the features as I can with other data types. So, if I do start editing it says, no editable layers. The workspace containing this data cannot be edited. So that's no good there. I'm told that you can copy and paste features that have been edited in for other places but that doesnt work as well. So if editing's part of your work flow, this isn't the data container for you yet. And again it's still kind of up and coming, so be careful with it if you decide it is for you. The big reason I like to show it is because it interfaces really well with other environments. So SQLite can be read from so many different places and as this implementation they have their own format for the geometry and the database. But, it can also use a standard format called spatial light, which you can look up online if you want more info. But, this database here is using the spatial light geometry. And what that means is I can read and right these features from R. Now, for those of you not familiar, R is an open source programming language. It's a statistical package, and it's really powerful for data crunching. I tend to program in Python which you've heard me talk about before but lots and lots of people like R. There some great courses Coursera about it and other places on the Internet. And with just a few line of code I can connect to this database here and I can read in a table from the database the species table and view it. So if I move that over to the side here. And then take this here and I load in that same table main.species. It asks me for some prompts in ArcGIS specifically what the unique identifier field is, the object ID field. So I'll click okay there. And it loads it up, and I can open that data table. And we're looking at the same data from within R over here, and with an ArcGIS. And that's pretty cool. And there's a whole environment building around this. So since this uses an open geometry format, I can use the spatial tools in R, or in QGIS to access this data with no problem. Or at least no problem if you like writing code. So for those of you who don't ever want to get into coding, again, this might not be the format for you but it's super versatile for those who are interested in going that route. Still if Esry does end up getting rid of the personal geo-database, I think that this can take over a lot of functionality of being a self-contained data format. There's a little tool called SQLiteStudio that's also free and available online that we can use to access the SQLite databases just the same way that we access the personal geodatabases. So I'll show that here and bring that onto the screen. And let's get rid of the SQL queries here. But if I close it out, I can view my data tables and I can just double-click them to open up. So here's the counties' feature class. And I can view the structure but I can also take a look at the data and the shape field is gibberish to it. So, yeah don't mess with that. That's binary data that's trying to interpret as text here. But if we scroll over to the right we can see all the attributes of our counties layer again. Similarly we can view that species table we were just looking at if we come down here. I can double click that and open it and go to the data tab, and I can sort and make changes and do all kinds of things in here just like I can within Microsoft Access. It also provides us a form view, just like Microsoft Access does, so that we can do some data entry or set others up to do the data entry. So that's pretty nice too. So SQLiteStudio is a nice free tool for editing these types of databases. The takeaway of all that again is that you can work with these types of geodatabases in many environments. You're not tied to ArcGIS which is really great because I can send it over to somebody else and care a whole lot less about what type of a GIS they are running. They can edit it even if they don't know GIS because they could bode it in SQLiteStudio instead or in R Studio or something like that. Okay, one other thing I want to show you is the concept of the view. So it looks like a table but if I come up here, there's a native fish view. And this goes back to our concept of data structures as we talked about earlier in this course and it says view properties. And it's a definition, it's a database if you view here. And we don't have to complete and understand this code but it's basically saying create this view with this name native fish as and then it's the rest of that query. Select all the fields from the species table and from these other two tables. So we're doing an implicit join here where we're actually joining in the contents of these other two tables, by specifying criteria like we do and it's like by attributes. So one of them were saying this value equals natives and then we're doing joints and saying these values in, or the species group ID field use the same equals the def species groups dot ID fields. So, this is the same sort of thing that we do in the join dialogue, except in code. And, what it gives us is kind of a permanent view on our data that is up to date when we edit the underlying tables. So the view is, it's sort of like what we do with symbology, where the underlying data doesn't change but we can change its representation. The views let us change the representation of the underlying data and wherever the underlying data changes, our symbology data changes or our view changes in those cases. And I can open this table here, and I get a subset of that species table. Under in 33 here, where this piece of table has 338. So, you probably getting the sense that these are require quite a bit more technical proficiency and they do right now. But I'm not advocating that you use these right now, I just want to make you more aware of them as a possibility for how you could potentially store your data in the future. I would imagine that ESRI would continue to add capabilities to using SQLite geodatabases this way. And, in the future, you might use them just the same way that you use a personal geodatabase or a file geodatabase now. Again for now just know that these are on their way in while personal geodatabases are kind of on their way out. In that these can bridge many different systems. Okay, so that's it for this lecture. In this lecture I showed you the SQLite geodatabase. It's a limited as of right now geodatabase but it has a couple of powerful features where it can be used to cross GIS systems and we can write queries as of use that subset our data and display it live in our documents. It's still work in progress but expect it to be coming into more use in the In the future. And lucky for all of us, this is the end of our series on storage formats. So in the next lecture, we're going to do a workflow. So, we'll get back to really using GIS in depth. See you there.
