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.

Comedic Relief

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.

Tutorial Section 1 Step 2 Image
  1. In the catalog pane, right click on “Databases” (green) and select “New Database Connection” (red).
Tutorial Section 1 Step 3
  1. 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.
Tutorial Section 1 Step 4
  1. 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  
Tutorial Section 1 Step 5a
Section 1 Step 5b
  1. 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.

Tutorial Section 1 Step 6a
Tutorial Section 1 Step 6b
  1. 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.
Tutorial Section 1 Step 1
  1. Click the browse icon (green) and navigate to your extracted shapefile (red). For the output name, type “san_main.”
Section 2 Step 2
  1. Click “Run”. This will add the new SDE layer into the map by default.
  2. 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 2 Step 4

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.
Section 3 Step 1
  1. 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.                           
Section 3 Step 2
  1. 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”.
  1. 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”.
  1. 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.
  1. Click the browse icon (green) and navigate to your extracted table (red). For the output name, type “seattle_light_table.”
  1. Click “Run”. This will add the new SDE layer into the map by default.
  2. 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

An ArcGIS Online management dashboard was recently developed by Mike Bowen of GEOACE to help organize and optimize content within our ArcGIS Online organization. Rather than keep it to ourselves, we’ve decided to share it with the world! In addition to the dashboard itself, Mike developed an ArcGIS Story Map that serves as a tutorial on how to create one. Since sharing, we’ve received a lot of positive feedback, and the story map has since been featured on GIS Lounge. For those of you who are unaware, GIS Lounge is an industry-leading information site covering research and case studies about the applications of geographic information systems, geospatial technologies, and cartography.

We’re incredibly grateful to GIS Lounge for publishing the article on our management dashboard. More than that, we’re incredibly grateful to all of you for the interest and feedback! If you want to check out the article, you can find it here. Alternatively, you’d like to skip directly to the tutorial then you can find the story map here. Finally, if you’d like to have a conversation about how you can take the dashboard further or implement it in your own organization, then just contact us!

Management Dashboard Screenshot

We were very happy to see GEOACE cofounder Mike Bowen front and center at the 2020 Esri IMGIS conference. During the conference’s Electric Utility keynote, Mike was representing AEP as he showcased multiple innovative solutions his team created.

Esri’s QuickCapture

In his talk, Mike discussed an Esri QuickCapture mobile application which was spun up on a whim to support aerial inspections. This application was appropriately-named “TFS Aerial Patrol Tool.” The inspector found it to be very easy to use, and upper management liked the application so much that Mike was authorized to enhance and formalize the workflow.

Quickcapture Photo 1
Quickcapture Photo 2

Oil Containment Inspection Dashboard

Web GIS has potential to increase efficiency and collaboration in the office, too. Mike went on to discuss various web applications and dashboards tailored for project managers and team members. While they were typically created for the office, in reality they can be used any place with internet connectivity. One of these applications was an oil containment dashboard. The dashboard turned a previously-laborious and time-expensive excel process into an interactive, visual workflow. Upon completion, the department head did the math. He determined that the new Esri workflow saved approximately one million dollars-worth of man hours relative to the old process. Configuration of the application took less than forty hours. How’s that for Return on Investment!? Note that if you’d like more details about this application, a paper was published about it in the 2019 Cigre Grid of the Future Conference.

Oil Containment Inspection Dashboard
Active Transmission Projects

Another solution mentioned in the Esri IMGIS talk was a suite of applications called ATP, or Active Transmission Projects. The suite currently exists as two applications. The first is a dashboard dedicated to project management information and activities, and the second is a web app dedicated to project team usage. The latter was purposed as a collaboration tool for project teams to view progress and outcomes from the efforts of all departments. Together, these applications enable team members to view things as broad-scale as regional progress tracking. Similarly, they enable team members to view things as granular as property access status and even landowner interaction information. Fun fact: In addition to Mike’s contributions, a significant portion of this suite was developed by GEOACE cofounder Aaron Laver.

Active Transmission Projects
Field Condition Assessment Tool

The Field Condition Assessment Tool, or FCAT for short, was a full-fledged location-based solution. The solution was designed to get critical information from the field to the office. The field component consists of an inspection form which has been fully digitized as a Survey123 form. Once in the office, the data is displayed and easily digested using an interactive dashboard. Where cellular data is available, the solution enables field crews to upload critical inspection information in real time. Alternatively, where cellular data is slow or absent, the inspection information is saved locally and uploaded upon entering an area with service. The sync can be set to run manually or automatically. This not only saves time across the board, but also eliminates paper from the process entirely.

Field Condition Assessment Tool

Suffice it to say, Mike has a lot going on with his team at AEP. We hope you were able to tune at the Esri IMGIS conference, but in case you missed it, Esri has uploaded a highly-requested portion of the talk where mike discusses his QuickCapture solution. Check it out!