GEOACE in the Community

Introduction

It’s exciting to be a member of an organization that is increasingly spatially aware and growing their geospatial resources as a result. However, sometimes this growth can lead to confusing or unfamiliar scenarios that leave a traditional GIS user a little perplexed or skittish about what to do next. One such scenario is the use of an enterprise geodatabase within the Esri framework.  Note that an enterprise geodatabase is often-times referred to as an SDE (Spatial Database Engine…which I had to look up because I previously had no clue), which is an acronym we’ll be using for the remainder of this tutorial. So what is a SDE? Simply put, a SDE is a proprietary (to Esri) database tool built on top of an object-relational database system (RDS) such as Microsoft’s SQL Server, Oracle, or PostgreSQL. Licensing for the support, use, and creation of SDEs is included with most ArcGIS Server or Enterprise licenses.

Storing data within any RDS, and specifically a SDE in this case, has many advantages within an organization. A few of these advantages include the ability for simultaneous editing (the ability for multiple users to update the same collection of data at the same time), versioned and/or archived workflows (the ability to roll back any changes made to a collection of data or support supervisory workflows), flexibility within ArcGIS Enterprise (i.e., portal), and process automation.

Regardless the reasoning behind the use of a SDE, it can be intimidating to get started with one if you’re used to “windows file system and shapefile” workflows. We’ve found that most of the time folks have existing data in the form of spreadsheets or shapefiles that they are looking to bring into the enterprise geodatabase. Today we’ll be using a shapefile example as our primary focus, but we’ll also mention how one could add in a table with or without geometry. This tutorial is meant to serve as an introduction to working with an SDE using ArcGIS Pro. Our objectives today are as follows:

1. Show how to connect to an existing enterprise geodatabase using ArcGIS Pro
2. Show how to import a feature class (i.e., shapefile) into a SDE using ArcGIS Pro
3. Show how to import a table (e.g., csv) with coordinates into a SDE using ArcGIS Pro
4. Show how to import a table (e.g., csv) without coordinates into a SDE using ArcGIS Pro

Note that Esri has a huge wealth of information freely available at their website, including step-by-step tutorials on how to accomplish the above objectives. We are simply providing this tutorial per the request of a client in an effort to create the workflow easier to follow and complete. We’ll let you be the judge as to whether or not it’s been useful to you. It’s also worth noting that there are generally a plethora of ways to accomplish any of these tasks. We are simply providing what we believe is one easy and intuitive mechanism to do so.

Prerequisites

• A licensed Enterprise Geodatabase (SQL Server, Oracle, or PostgreSQL with PostGIS) and a user account with the ability to read and write to the database
  • Since our datapack includes utilities layers, we’ll be using PostgreSQL in this tutorial as the user “util” in a database also called “util”.
    • In a production environment, a separate user should be created and used for these tasks. We do NOT recommend using the postgres user in production.
  • Note that we’ve opted to leave creation of the database itself out of this tutorial since many organizations have staff fully dedicated to this type of thing, but if it interests you and you’d like to know how, drop a comment and we can certainly look into some follow-up posts about it in the future.
• ArcGIS Pro License
• Datapack.ZIP file (or your own shapefile, geo-enabled csv, and non-geo-enabled csv)

Section 1. Connecting to Existing Enterprise Geodatabase and Adding a Layer to a Map

1. Extract datapack.ZIP to a temporary file location and open ArcGIS Pro. It contains a shapefile and a CSV.
   • Note: This link takes will download the package from our company ZIP drive. You are welcome to use any shapefile and .csv for this tutorial. They should all work just the same! Additionally, if you want to download real data from one of our sources, you can go here and download the file in both shapefile and csv (spreadsheet) formats: https://data-seattlecitygis.opendata.arcgis.com/datasets/seattle-city-light-poles-1?geometry=-123.365%2C47.450%2C-121.279%2C47.774
2. Within ArcGIS Pro, select “View” (red) and click “Catalog Pane” (green). This opens your catalog pane on the right-hand side of the screen.

3. In the catalog pane, right click on “Databases” (green) and select “New Database Connection” (red).

4. Input information as follows and then click “Ok”:
   • Database Platform: PostgreSQL
   • Instance: somemachine.somedomain.com
     • This is normally the fully qualified domain name of the machine which hosts the relational data store
   • Authentication Type: Database Authentication
   • Username: util (note that postgres is the default superuser for postgresql; change “util” to a username specific to your organization)
   • Password: *yourpassword*
     • Note: Checking the “Save User/Password” box means that the login information will be stored in the map document, and as such anybody who opens this map document will have access to the enterprise data contained within the connected database. Do this at your own risk.
   • Database: util (again, postgres is generally the default that comes with a clean install; change “util” to a username specific to your organization)
     • Note: You will only be able to select a database if the previous connection information was put in correctly. If nothing is showing up here, check connection information again to ensure everything is as it should be.

5. Rename the new connection file as somedatabaseuser@machinename.databasename.sde (green) and save the map document (red).
   • Best Management Practice: Whenever connecting to a database within a GIS, it is good practice to use this format or a similar one so your future self and other users know exactly what the connection is pointing to as well as the login info. I like to follow the format username@instance.database.sde  

6. You’re now connected. Expand the geodatabase to view its contents by clicking the expand arrow (green). Right click on any feature class witihn the database (City_Facilities in our example, but what you have loaded–if anything– will obviously vary) feature class (red) and select “add to current map” (blue). You now have access to read/write properties for this feature class and can treat it as any other shapefile/layer you’re used to working with in ArcGIS Pro.

7. To reset the canvas and prepare for the next section, remove the City_Facilities layer from the map.

Section 2. Import a Shapefile Into a SDE

1. In the catalog pane of same map document, right click on the SDE connection (blue) and select Import (red) > Feature Class (green). This will open up the “Feature Class to Feature Class” geoprocessing tool with the Output Location prepopulated to your SDE.
   • Note the ability to select “Feature Class(es)”, a bulk import tool.

2. Click the browse icon (green) and navigate to your extracted shapefile (red). For the output name, type “san_main.”

3. Click “Run”. This will add the new SDE layer into the map by default.
4. Verify that the layer has been added to your SDE (red). Right click on the layer (red) and select “Delete” (green), if desired, to remove the layer from your enterprise database. Save the project.

Section 3. Import a table (e.g., csv) with coordinates into a SDE as a Feature Class

1. Navigate to “Analysis” (red) and select “Tools” (green) in the geoprocessing section. The geoprocessing pane will appear on the right hand side of the screen.

2. Type “xy to point” on the search bar of the geoprocessing pane. Select “XY Table to Point” (Data Management Tools). The XY Table to Point tool will open within the geoprocessing pane.                           

3. On the input table, browse (green) to the .csv table included in the extracted datapack folder (Seattle_City_Light_Poles.csv). Select and click “ok”.

4. Click browse on the “Output Feature Class” section, then navigate to your SDE connection under databases (red). Select the database connection (green), and then specify any name you’d like for the new point layer in your SDE. We’ll call this layer “Seattle_Lights”.

5. Specify the X field as “X” and the Y field as “Y”. Click “Run”. The layer will automatically be added to your map once complete. Confirm the layer exists in your SDE, and then remove as specified in Step 4 of Section 2.

Section 4. Import a non-spatial table into a SDE

1. In the catalog pane of same map document, right click on the SDE connection (blue) and select Import (red) > Table (green). This will open up the “Table to Table” geoprocessing tool with the Output Location prepopulated to your SDE.
   • Note the ability to select “Table(s)”, a bulk import tool.

2. Click the browse icon (green) and navigate to your extracted table (red). For the output name, type “seattle_light_table.”

3. Click “Run”. This will add the new SDE layer into the map by default.
4. Remove the table in the same way as specified in Step 4 of Section 2. Save the project and exit.

Wrapping Up

Hopefully this tutorial gave some insight on how you can start to leverage the power of a relational database within your organization’s workflows. If this is a topic you would like to see more of in the form of another tutorial or best management practices, please contact us to let us know! We’d welcome any feedback you have to offer. Thanks, and see you next time!

Additional Esri Resources

General “About” Enterprise Geodatabases: https://enterprise.arcgis.com/en/server/latest/manage-data/windows/enterprise-geodatabases-and-arcgis-enterprise.htm

XY Table to Point Geoprocessing Tool Documentation: https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/xy-table-to-point.htm

Feature Class to Feature Class Geoprocessing Tutorial and Documentation: https://pro.arcgis.com/en/pro-app/latest/tool-reference/conversion/feature-class-to-feature-class.htm

Table to Table Geoprocessing Tutorial and Documentation: https://pro.arcgis.com/en/pro-app/latest/tool-reference/conversion/table-to-table.htm