Working with Enterprise Geodatabases
Learning Objectives
Understand, connect to and work with ArcSDE geodatabase.
Understand versioning and replication in ArcSDE geodatabase management.
Develop green infrastructure data for a coastal spatial-temporal database.
Understand and publish ArcGIS feature services.
Assignment Data
Data students may collect from various sources
Data in the gishub(gishub2) geodatabase. Example geodatabase.
Deliverables
A completed answer sheet, answering all questions provided below.
A screen shot of a schema diagram of the coastal watershed geodatabase.
A screen shot of the web map of your published feature service.
1. Introduction
Lab assignments 4, 5 and 6 together constitute the final project for GIS Data Management. You will design your geodatabase, collect necessary data, create and populate your geodatabase on the geography server, create metadata for your datasets, and present your work in both written and verbal formats. To accomplish that, assignment 4 lays the foundation for the project, where you will design your geodatabase and start collecting source data. In addition, you will also learn how to work with ArcSDE multi-user geodatabase and feature services. In Assignment 5, you will work with metadata for the data. In assignment 6, you will complete your geodatabase and document and present the processes of creating the geodatabase and the resulting products.
You will work as two-to-three person groups to complete the tasks for labs 4-6. As described in the final project topic document you will select 1 of 15 municipalities within King County. See in particular Appendix III and V in the report by Tezak and Danker 2016 noted in the final project topic document.
ArcSDE Geodatabase
Up to this point, you have been working with file geodatabase using ArcGIS for Desktop on your local computer. In this assignment, you will learn how to work with an enterprise geodatabase management system by the Department of Geography.An UW Geography ArcSDE geodatabase is set up based on ESRI’s ArcGIS for Server suite of software. It utilizes their server technology along with a relational database management system, PostgreSQL (9.4), to store, manage, and share data.
In ArcGIS, there are several types of geodatabases that have been designed to support different organizational workflows and data storage needs: personal geodatabase, file geodatabase, and ArcSDE[1] (spatial database engine) geodatabase. For detailed descriptions and comparisons of the three types of geodatabases, please see These geodatabases vary in data sizes, number of users, and the capability to perform concurrent editing, etc. Personal and file geodatabases are designed to be edited by a single user. Workgroup file geodatabases are designed to be edited by a small group of users. However, neither personal or file geodatabases support geodatabase versioning. A multiuser editable geodatabase is typically found in larger organizations where multiple users need to view and edit the GIS database at the same time. Multiuser geodatabases supportversionsandreplicationand require ArcSDE technology and a database management system (DBMS) such as Oracle, Microsoft SQL Server, IBM DB2, IBM Informix, or PostgreSQL. Although workgroup and enterprise geodatabases can be considered multi-user, management of the former uses manual procedures (human input and coordination) to version and replicate, whereas management of the latter makes use of automated procedures for versioning and replication. An enterprise approach requires additional information technology administration.
Question 1: Describe three types of geodatabases in ArcGIS. What are the major differences among them? What are the advantages of using an ArcSDE approach to geodatabases over the file and personal geodatabase approaches? What are the disadvantages?
Watershed Characterization for Ecosystem Services in Puget Sound
Stormwater is the single largest source of pollution in Puget Sound. The standard approaches to treat stormwater involve single-purpose and expensive gray stormwater infrastructure projects that use vaults and/or pipes to retain and/or channel water. Unlike gray infrastructure, green infrastructure is a resilient and affordable solution to water filtration that makes use of “natural services” from vegetation and soil, parks and open spaces, wetland, rain gardens, green roofs etc. acting as an interconnected system to maintain healthy waters, reduce cost, deliver multiple benefits to urban residents and neighborhood, and thus support sustainable communities (EPA, 2013)[2]. It is one of many ecosystems services that can provide considerable value to communities through “natural” functions. By weaving natural processes into the built environment, green infrastructure provides not only stormwater management, but also flood mitigation, air quality management, and much more. For example, see the Natural Capital Project for insights about ecosystems services and how GIS is being used to ‘value’ such services, particularly InVEST software.
Creating a Geodatabase for Green Infrastructure Systems in Puget Sound
In the final project you will create a geodatabase for the green infrastructure system for an area in the Puget Sound region by employing concepts, methods, techniques that you have learned in previous and future lessons and assignments. Specifically, you will focus on green stormwater infrastructure within a municipality in King County. Each of the municipalities falls within a Washington Water Resource Inventory Area (WRIA) that provides a coarser scale perspective to your focal scale work at the municipal scale. WRIA is the term given to watersheds in WA State for water resource management purposes[3].
Much information is needed to build and maintain a sustainable green infrastructure system. What should you include in your geodatabase? How should you construct and structure the geodatabase? You can think about these questions from the conceptual, the logical, and the physical level of building a geodatabase, which have been discussed in lessons. Please also read the document on “Developing a Geodatabase” that summarizes the processes of designing and creating a geodatabase. At the conceptual level, we can treat each feature dataset as a theme or super category without committing to geodatabase data model implementation. A feature dataset takes the concept of them and makes it operational at the logical-level of the geodatabase model. Feature classes in a feature dataset should share spatial, temporal, and/or functional relationships. The questions listed below will also help you build a good database structure.
When you build a green infrastructure system, what kind of information would you need to know?
How well do you think your feature datasets will characterize a green infrastructure system as a connected set of elements?
What are the feature classes that you will need for each of the feature datasets?
What are the relationships among those data? Do you think the logical relationships among the feature classes truly justify the use of these feature datasets?
What kind of attribute data will you need for your feature classes?
Make sure to do the following tasks when creating your feature datasets:
Set an appropriate coordinate system for the feature datasets.
Group members have privileges to read and edit data (will be discussed later in this assignment).
Incorporate the number of the municipality within the name of your feature dataset (e.g. name of your geodatabase.feature dataset name.municipality number).
Extract feature classes to the drainage area of your municipality if they cover a larger area. Be careful, as drainage areas do not necessarily pay attention to municipal boundaries.
Create subtypes, domains, relationships, topologies, or geometric networks if you think they are necessary for your feature dataset and feature classes.
At this point, you are not expected to collect all of the data, i.e., feature classes and feature datasets and raster layers for your research question and the geodatabase. However, the more you collect the easier the final project. Every student must own at least one feature dataset, e.g. 1) Stormwater pipes, 2) green infrastructure, and 3) drainage units. NOTE: It must be processed as a versioned feature dataset, SEE VERSIONING BELOW. Remember, we create feature datasets to establish logical relationships among data.
Question 2: Write a problem statement including a research question, the feature datasets you will create, the (possible) data you will collect, and the rationale of structuring your geodatabase. The research question is to be a refinement of question 1 posed by Dept of Ecology on their landing page in regards to a municipality, as we are focusing on a detailed assessment of drainage characterization; that is, the same question 1 on the first page of the final project topic document.
2. Connecting to and Working with ArcSDE Geodatabases
A spatial database connection enables users with direct access to the ArcSDE geodatabase when using ArcGIS Desktop/Workstation clients. These clients include ArcGlobe, ArcScene, ArcCatalog, and ArcMap. This method requires connection syntax and authentication protocol to connect with the geodatabase server and can store a connection file on the user’s workstation. This method also enables the user to connect to either the default geodatabase version or previous versions. Once connected, the user can view all data stored in the geodatabase.
There are several methods to connect to the ArcSDE geodatabase. We will introduce two methods in this course: using connection dialog box in ArcCatalog with two modes, and using a Python flat file in ArcCatalog.
Database Connection Options
The performance of the ArcSDE geodatabase can be affected by the bandwidth on both the user end and the server end, especially when multiple users access the geodatabase at the same time.
When using Direct Connection to the SDE geodatabases by using computers on campus, e.g. in the Sherman Lab, you will experience the fastest connection among these options. The physical Ethernet connection has the greatest bandwidth.
Using Direct Connection through an off-campus computer would be using physical wires wherein the narrowest bandwidth will constrain your top speed.
Database Connection Using the Dialog Window in ArcCatalog
- Open ArcCatalog. Expand “Database Connections” in the catalog tree.
- If you are not familiar with Windows 8.1 on the Remote Desktop, programs can be found by clicking the Start button in the lower left corner, followed by searching for the program in the upper right corner. Right-click > Pin to Taskbar will allow you to access it from the desktop.
- Double-click “Add Database Connection” to bring up the Database Connection dialogue box and enter the following parameters:
✓Database Platform: PostgreSQL
✓Instance: geog-db01.geog.uw.edu
✓Authentication Type: Database Authentication
✓User Name: Your UWNetID
✓Password: Your UWNetID password
✓Database:select the name of your geodatabase from the dropdown menu (Select “inres482582_2016” for the first connection; this is Assignment 4’s geodatabase.)
- A connection to the geodatabase will appear in the “Database Connections” folder in ArcCatalog Tree. The database name will be shown as the default “Connection to db1.geog.uw.edu.sde”, you can click and change it to a name that is meaningful to you. Double-click the icon to make the connection if it has a red X on it.
- You can see that the geodatabase will be empty after you add the connection. This will be your working geodatabase for creating and populating your feature dataset. You can expand the geodatabase and browse feature datasets and feature classes in the same manner as in a file geodatabase in the Desktop application. Data is stored as a part of your ‘schema’, denoted by the user name (e.g., database name.username.feature dataset name). This indicates the ownership of a feature dataset, which affects data access privileges that will be discussed in detail later.
- Repeat steps 1-3 for the other database on the server: “gishub”. Be sure to give the connection a name to distinguish it from the database that you will be editing yourself (inres482582_2016). These additional database provides sources of data that you may use either directly or indirectly as examples for your own work.
gishub is a collection of both original data and selected groups’ submissions from previous work in geography.
- You should now have two connection files, one each for inres482582_2016 and gishub,under Database Connections in ArcCatalog.
Database Connection Using Flat File
You can also connect to the geodatabase using a flat file written in Python scripts.
- Click the PythonWindow button on the standard Toolbar in ArcCatalog, and it will open the Python interactive window.
- Copy and paste the command line below to the empty space (after >) in the Python window.
arcpy.CreateDatabaseConnection_management ("Database Connections", "your geodatabase name.sde", "POSTGRESQL", "geog-db01.geog.uw.edu", "DATABASE_AUTH", "Your UW NetID", "Your UW Password", "SAVE_USERNAME", “inres482582_2016”, “#”,”TRANSACTIONAL","#","#")
- Change “Your UW NetID” and “Your UW Password” to your own ID and password, and change the geodatabase name in the sample below to that of your own. Then press enter to run the script.
- Once the script is run successfully, you will see a result message similar to the example below (the geodatabase name will be different).
- Make sure to change the geodatabase name in the script if you want to connect to another geodatabase.
ArcGIS Field naming conventions:
Field naming guidelines (Found at )
Field names need to adhere to certain conventions for them to be valid. Keep the following guidelines in mind when you name a field:
Spaces and certain characters are not supported in field names. Special characters include hyphens such as in x-coordinate and y-coordinate; parentheses; brackets; and symbols such as $, %, and #. Essentially, eliminate anything that is not alphanumeric or an underscore.
Be sure to edit the field names in delimited text files or other tables to remove unsupported characters before trying to use the files in ArcGIS.
Avoid starting field names with a number or an underscore.
Avoid using field names that contain words that are considered reserved keywords, such as date, day, month, table, text, user, when, where, year, and zone. Each underlying DBMS can have its own set of reserved keywords. For a list of keywords for MS Access, see thisMicrosoft support article.
Geodatabase feature class, table, and field names can be up to 64 characters. (More specifically, you can only enter up to 52 characters for a personal geodatabase feature class name because the system appends characters to total 64.) Shapefiles and .dbf field names can be up to 10 characters long. For INFO tables, use up to 16 letters or numbers. In addition, the underlying DBMS may impose other restrictions on field names.
Also note:File geodatabase size and name limits
Feature Dataset Privileges
In ArcSDE geodatabase, if you are not the owner of a feature dataset, or the contents of the feature dataset are not shared with you by the owner, the contents in that dataset will not be visible to you, not to mention editing the data. Only the owner of a feature dataset can alter other users’ permission on that dataset.If others are to be able to interact with your data you must assign privileges. An exception is that the server administrator can grant and revoke dataset privileges at the geodatabase level.
There are four types of privileges that can be granted on a feature dataset: select (retrieve), insert (create), update (edit), and delete (delete), as per the standard CRUD operations of any database management systems. Select privileges allow a user to read and select from but not modify the contents of a feature dataset. Insert, update, and delete privileges allow a user to modify the contents. However, these privileges only apply to the features (rows) of the data. They do not give the ability for others to modify the schema of datasets that you own (e.g. adding a new field to a feature class, importing or deleting a feature class, adding a topology, etc.). Generic user roles (shared between multiple users) are one way to overcome this. However, this requires greater effort on the part of editors to keep track of who is making what changes. For this assignment, do not attempt to share your account with others. Generic users may be set up for individual group geodatabases later as part of the final project to enable group members to modify each other’s datasets.
To grant privileges to other users:
In ArcCatalog, right click the feature dataset Manage Privileges, and this will bring up the Privileges dialog window.
Click Add to bring up the User/Role window select one or more users from the list of registered users. Ignore the User/Role text box. The generic user role “grp_inres482582” is available if you want to assign privileges to all students in the class.
Check the privilege option(s) that you would like to assign to each of the added users.
Note: Since you work as a group in this and the following assignments, the one who creates the feature dataset needs to assign privileges to other group member(s) in order for them to work with the data.