 Hi, I'm Chawla Jain, as he said. I'm from Charlotte, North Carolina, and I'm a GIS DBA, so I think I'm one of the few ones here, or rather I haven't met anyone really doing what I do here. So GIS DBA basically deals with spatial and non-spatial data. And another peculiar thing which I found was that we use PostgreSQL for most of our production databases, but the actual spatial element which we use is not post-GIS. We use a proprietary database there or extension which is called Spatial Database Engine, which is made by Environmental Systems Research Institute known as ESRI. There are a few people here from ESRI. And these GIS software we use is ArcGIS, which is like the most well-known proprietary GIS software in the world. Mecklenburg County, and again I'm one of the few people here from public sector too. Mecklenburg County is located in the southwestern region of North Carolina. It's bordered South Carolina and North Carolina, as you can see. And Charlotte is one of the largest cities in this county. The present population is about a million, and it's also as many of you may be knowing that it's the second largest banking center in the US, home to Bank of America. So the GIS department is about 40 people, and we managed both the city of Charlotte databases and also the Mecklenburg County, which is where city of Charlotte is located. There are many business units which use this data, some of the most known to people are 9-1-1 services. Then our land records department, where you get all the property information and maps. The local department of transportation and schools and so on. We are a huge group. We are about 40 to 50 people. 20 of us are the editors who do the actual editing of the data, the land records and the addressing data. About 10 or so do the actual mapping and analysis, and the remaining are programmers and DBAs, and I belong to that group. So what is a GIS data source? I don't know. Is anyone familiar with GIS other than post-GIS? Okay. The GIS data sources are generally SD feature classes. In our case, since we use SD with PostgreSQL, so SD feature classes, or you could be calling it post-GIS feature classes if you are using post-GIS. Then there are personal geodatabases because most people will have to pull these databases from the enterprise database server onto their desktop, and very often they have to manipulate it for analysis and personal use, so that's why they have something called personal geodatabases, and that's mainly in access format. Then the shape files were the first GIS files which were introduced way back in the early 90s, and they use the DBAs database format, and they can be easily transported from, you know, even via emails. Then there is text files because we get a lot of our data in lat-long form, and that's always in a text file which can be converted to a spatial format. Then the drawing files from AutoCAD and other similar software is also converted to, you know, geo-referenced data, either in polygon or point or line, whichever the case may be. A lot of the data is in raster files. Now raster files are generally stored on a separate database server. They're never stored with vector data, but raster files are a big source of GIS data, and then JPEG files, which are generally, they don't have spatial data, but they are sometimes linked, you know, like a little bit of point. Now the role of the GIS DBA, as I said, is, you know, you are actually in charge of both the spatial and non-spatial data, and, you know, this is just a general definition which I picked up. Most of our data is stored on data servers, which are either in SQL server or PostgreSQL. We started way back in the mid-90s with SQL server and STE, which is from ESRI, the early versions of STE, and we still have like three production servers. Two are in SQL server 2008, and they're on their way to 2014, and we have one major production server in PostgreSQL. This is thanks to very open-minded directors and other people in our local government, which have allowed us to move to PostgreSQL. So we have our major production database, which serves all our web applications in PostgreSQL. PostgreSQL 9.3, and ArcGIS, which is the client software, is also 10.3. We're always two versions lower because for all the testing, which needs to take place. And we also have like shape files, JPEG files, as I said, LiDAR data, all this is stored on file servers. So this is a chart of our production servers. We have, as I said, two in SQL server and one in PostgreSQL. And then we have a script server, which hosts both SQL server and PostgreSQL server. And that server runs all our major ETL packages. And it's really complicated the way we run this because we have all these different formats of data. And that's true for most GIS units in the country. So, I mean, you can't get away from it. Then these are, of course, the same servers. We have development servers. And then we have a testing platform on the other end, where we try out all the latest versions of Postgre and ArcGIS and SQL server. And half the time, most of the latest and the cool versions of Postgre don't seem to work with our ArcGIS software. But it gets there after two versions. Other data we support is a big file server, which that serves a lot of our other websites and business units. Then we also have a SQL server 2005 server for our air quality data. This basically collects all JSON feeds from different air quality monitors all over the state. And those are converted to spatially referenced data. Data maintenance and creation is done by our land record staff who edit directly on our SQL server, ST server through ArcMap, which is a part of ArcGIS. ArcGIS is a desktop software. It has two parts. One is ArcMap and one is our catalog. ArcMap is the actual tool to do analysis and build models and also to make maps and edits. The other part is called our catalog from where you could load data, view data, but it doesn't do much beyond that. Then we have a mapping group which makes maps and does the analysis like if somebody wants to set up a new business in Charlotte and they may come to them to find the best locations. And that's a big thing as many of you may have, you know, be already aware of that. So that's our mapping staff. They can directly upload sometimes data to our catalog. Some of them have permissions to do that. That means it goes straight to our Postgre server. We also have an intermediate development. Another one. This is a Postgre ST server where some of the mapping staff directly edit on that and that is moved over by our catalog to our production ST server. Most of our major automated data updates take place via the following methods. The ETL jobs could be the SSIS packages which is SQL Server Integrations packages or Penteho that's open source done with Spoon. I don't know if any of you use Penteho or Spoon. Then of course we have plain SQL jobs, Postgre SQL jobs which is run via the extension PGA agent. Then STE has its own language. So we have to run some conversions through command line STE and of course they are scheduled via back files. Then ArcGIS, the mark map which I talked about earlier, has a big model builder and that is where the actual GIS data conversion and modifications can be done. The models can be exported to Python script so you can either write all this in Python with ArcPy class or you can do it through model builder. Then all this, many of this including the Spoon jobs and the models and pythons are all scheduled via Dos command back files. Some of the spatial files which we update daily is like our address points. The whole county has, every address has a point and we have the land records file or the parcels file, lots as many of you may know the streets file, building footprints and the tax annals. These are very critical data to any jurisdiction in the country because that's where how your 911 services work. If this fails or if it is outdated, we get calls from the police department and all kinds of people. The other non-spatial data files which we use, which are joined with these spatial files are the real estate files which have information on ownership, sales, etc. These are actually maintained by our IT staff and they're on a SQL server box and we move it to our SQL server box and then finally to PostgreSQL. This is just an example. I'm going to show you some examples of our spatial data flows on a day to day basis. The master address points I edited on a SQL server through ArcMap. They are either edited or created or whatever as new addresses come in or changes come in. A text file is generated from that and that text file has XY coordinates. That is pulled into this model builder which you see to the right over here. That text file is then converted through this Python script or model builder to a shape file. The shape file is then moved over to file servers because a lot of people still use shape file for desktop manipulations and it's also moved over to our production PostgreSQL server. Now that's on the right shows the same model builder in Python format and that's scheduled via a BAT file. The parcel file that's again edited by our land record staff on a SQL server box. We use this Python script to just extract a feature class which has only few fields which we need for our production server which also hosts one of our major websites which I'll show you at the end if we have a few minutes. This parcel file is extracted via this script and then there we use another VB script to check if there are any errors because sometimes this extract doesn't work well. If somebody has entered maybe a parcel number is eight digits and if they've entered more than eight digits it fails or if somebody has entered some apostrophe or some strange things even while editing the lines. Once we had a line drawn all over the county and none of the records went through. So things like that, human errors. So we first check for that and then if everything is good we load it into our production server and those are some of the Python scripts to the right. The parcel layer is then also joined to a lot of those spatial files which I talked about which came from SQL server to Postgre which have all our ownership and sales and all that to create different views, parcel land views, sales, the KMA data and that's what our view looks like in our catalog with the parcel files and the data showing up on the right side. These are those non-spatial files which are used to create the views. They have land information like the basic land value, the assessed value, the building information whether it's commercial, residential, how many bathrooms, fireplaces, etc. It has sales value, historic and present sales location which is the site address and then of course basic info like the deed page, deed value and things like that. These files, this is an example of an SSIS package if you have ever used. This is SQL server integration services and that moves it over from SQL to SQL. You could use this to move it to Postgre SQL but I haven't had much luck with it so I generally just use this for SQL to SQL and then I feel that this kettle from Pentaho is much more easier to use when you have to go from database to database and so I use this to move from SQL to Postgre SQL. The screen to the left is the actual transformation and to the right is a job in Kettle and then it's scheduled via back file like that. This is just an example of our task manager from our script server, just a snapshot from one of our servers showing all the various jobs we've done. Now just like how is Postgre used to display GIS data? You could either use PostGIS which many of you are familiar with and or you use ESRI special database engine component. At Mecklenburg County we use both. The PostGIS we have an open source server like that with Postgre and PostGIS and it supports all our open source apps but since most of our database is edited by our catalog, our map and most of our end users are still using our GIS we use SD for our major production servers and who knows maybe in a year we might get away with that and go to PostGIS. So for installing Postgre for us we first have to install and then create an SD user with complete writes just like the Postgre user and then you create the table space and database. Next you create the SD schema and other schemas which may be necessary. Now prior to Postgre 9.3 at this point you would have to install STE and that would install all the STE tables into the Postgre database but now we can do it through the Arc Toolbox. You just go there and enable it to become a Geo database. So at that point all these tables like STE table config and STE table registry all this gets installed into the Postgre database. This is just some samples of data. Restore and backup was done through command line till 9.3. Now it can be done directly by a PG admin tool and the only thing I think which is a little different I don't know about for our data is that first we have to restore the public schema and then the SD schema. If we don't do it in that order the data doesn't come through right. This is a snapshot of some of our major online applications and I can show you an example. This is our website. If you enter like it's called Polaris 3G and I hope it works. I just typed in an address and it will bring it up and also the pictures. This is all part of our GIS database. This is one of our major websites. It gets almost million hits a year. All reactors, even nosy neighbors everybody is looking at this all the time. Anyway that's the main thing. It can be seen. I thought of it. That's our website. I just typed in that address on the top and this is what you get to see. I'll do it again. It has these photographs. It also has historic pictures and back side and front side. If you have any questions at this point let me know. Windows. We use Linux only for the open source just post GIS and post that's done on Linux. This is all Windows because SD won't work with Linux. All the SD software won't work with Linux. A kettle. It's from Pentaho. It's also an open source I think. It's Pentaho and it's called Kettle. It has parts and it's called Pan and Spoon and Kitchen. It's got all these strange names but it works really well. If you look at Pentaho you should be able to see it. Yes, you can do it. You can view it but you can't do much beyond that. That's why we have these two groups. The ones who use post-GIS are just our web app people. None of the desktop users are really... But then it's also a matter of getting them... Even getting them to use our GIS is a big thing sometimes. To switch them to something else might take a long time. Oh no, we don't. No, we do post-grade authentication not Windows. For most of the readers we have a generic account and then we have few for those who edit and the database has a little different. When we had the SD SQL server, when we first started, we had individual accounts and that was so we moved to just... We have ten accounts. That's it basically. It's very odd that ESRI, I don't know if you've heard of ESRI, ESRI has this big international conference in July in San Diego and like about six years ago when we moved over, I was in the post... administering post-grade with SD. There were six people sitting but this year there were at least 200 people sitting. So there are a lot of people now doing this. There are a lot of cost saving especially in the government sector. Don't they edit SD SQL server? They are still on SQL server. No, the other stuff is all the other users mapping and all our websites, many of our websites, we have a lot of online applications. If you go to www.sharmac.org and there's a whole section called map, all those are done through production. But I see no... They could move to post-grade. It's just that we have some other databases in SQL server which are especially all our real estate data which I talked about. Those people don't want to work with post-grade. It's a matter of mindset. They don't want to change. And for all SQL servers we have our whole IT group. There's a lot of database administered but this we are on our own. It's like we are told if you have issues, you're on your own, you're the ones who moved to this. But we've been with this for almost six years and I feel there are less issues. Yeah, that's true.