GEOG 865
Cloud and Server GIS

GIS databases and web editing


GIS vector datasets come in many formats. Some of these are better suited to web editing than others. Since we are working with ArcGIS Server in this exercise, we'll talk about some of the data formats that Esri offers and which ones are required for web editing. You'll then load some GIS data into a database on your EC2 instance.

Whether you're working with Esri software or not, one of the most ubiquitous formats for exchanging GIS datasets is the shapefile. This is a data format developed and openly documented by Esri, meaning that other software companies are allowed to use, create, and share shapefiles. A shapefile actually consists of multiple files with the same root name and different suffixes (.shp, .dbf, .prj, etc.) that store the data's geometry, attributes, projection information, and so on. You'll often see shapefiles available on GIS data warehouse sites that allow you to browse and download geographic datasets.

A shapefile is handy for exchanging data, but it's not very useful for web editing. Because the shapefile is an openly documented file format, it may be possible for a web developer to write an application that edits shapefiles. However, this would be a significant amount of work and ArcGIS does not supply out-of-the-box web editing functionality for shapefiles. Nor does ArcGIS support web editing with the shapefile's more advanced (but less openly documented) cousin, the file geodatabase.

In order to perform web editing with ArcGIS Server, your data must be stored in the ArcGIS Data Store or in a geodatabase hosted in a relational database management system (RDBMS). Here's what those terms mean:

  • The ArcGIS Data Store is a built-in data repository that comes with ArcGIS Enterprise. You can read more about it on the ESRI website. It's intended to support data hosting needs for installations that don't have a fully-functional relational database management system and geodatabases. For production data within an organization, a geodatabase is recommended because it possesses many data management capabilities. However, for use cases in which data simply needs to be uploaded or visualized in a less-critical manner (via apps, for example), the Data Store offers a simple way to create "hosted" services and store data in your server environment.
  • An RDBMS is a heavy-duty database used by enterprises to store large amounts of data. This includes GIS data, but RDBMSs also store many other types of datasets. Anytime you apply for a driver's license, fill out a hospital admittance form, or buy something online, your information is probably getting pushed into an RDBMS of some sort. Common RDBMSs include Microsoft SQL Server, Oracle, and the open source PostgreSQL. ESRI also supports a scaled-down version of an RDBMS called SQL Server Express that is offered for free by Microsoft.
  • Geodatabases (you may also recall the term ArcSDE) are an Esri technology that allows data stored in an RDBMS to be easily used within ArcGIS. It provides features such as versioning and replication that allow you to maintain different branches and copies of your data to accommodate enterprise workflows.

Why are these things required for web editing with ArcGIS Server? One thing you have to consider is that when you configure editing on the web, you may not want to expose your main production database to everyone on the network. Your data is valuable. You may have spent thousands of dollars collecting it. It may be required to meet certain quality standards. To protect your data, you'll probably choose to expose a copy, or replica, of it for web editing. This replica goes on your EC2 instance. You'll keep a separate replica of the data in your on-premises environment. This on-premises replica can be protected by your firewall, data quality checks, and so on.

From time to time, you can synchronize the two replicas using ArcGIS software tools. This means that one replica gets sent the changes that were made to the other replica, and vice versa. ArcGIS Server even provides a special type of web service for synchronizing two replicas, called a geodata service.

Terms you may see during this lesson include geodatabase and feature class. Geodatabase is an Esri-coined term to describe a database containing related GIS datasets, tables, relationship classes, topologies, and so on. A feature class is a vector dataset within a geodatabase.

Creating a geodatabase in SQL Server Express

Let's load some data onto your EC2 instance and prepare it for web editing. Your whole goal is to make a map on your instance and expose it through an ArcGIS feature service, which is the type of service that you can edit over the web. The first step is to get the data onto your instance and load it into SQL Server Express.

The first part of this process for us is to install SQL Server Express and its required licensing and system components.

  1. Before we begin installing a database on your server, download the following files to C:\data from our course management system; we'll need them in the next few steps:
    1. SQL Server Express 2017 installer
    2. .prvc license file that you used to install ArcGIS Server using the Cloud Formation template
  2. .NET Framework 3.5 is a required component of the Database Server, which we'll install first:
    1. On your EC2 machine, open Server Manager from the Start Menu.
    2. Click the Manage tab at the top.
    3. Click Add Roles and Features.
    4. Click Next until you get to the Features section highlighted along the left.
    5. Expand the .NET Framework 3.5 Features section, and check the box next to .NET Framework 3.5.
    6. Click Next and Install to finish the installation of the .NET Framework 3.5.
      1. You may see an error about missing files which you can ignore
  3. With the .NET Framework installed, proceed to install SQL Server Express:
    1. Right-click the SQL Server Express installer and Run as Administrator.
    2. Choose the Basic install and Accept the terms.
    3. Install to the default location.
    4. When the install finishes, you'll notice on the confirmation window that your database instance is called SQLEXPRESS and your local Administrator user is designated as a database admin.
    5. Click Close.
  4. To create an enterprise geodatabase in SQL Server Express we will need to provide a keycode file. The following steps take us through the process of generating one from our license file.
    1. Obtain the Authorization Number:
      1. We already have an authorization file (.pvrc), but we need to retrieve an authorization number from it to create the required keycode file.
      2. If you haven't already, download the .prvc file you used to install ArcGIS Server using Cloud Formation and save it in the C:\data folder on your EC2 machine.
      3. Right-click it and Open With Notepad to view the text contents of the file.
      4. Scroll down to the Features and authorization numbers section.
      5. Copy the code number next to ArcGIS Server. It should have the form, ECP123456789.
    2. Now we'll run a Software Authorization program to generate the keycode file:
      1. Open Windows Explorer and browse to C:\Program Files\Common Files\ArcGIS\bin.
      2. Right-click SoftwareAuthorization.exe and Run As Administrator.
      3. Select "I have installed my software and need to authorize it."
      4. Choose ArcGIS Server as the Product to be Authorized.
      5. Click Next.
      6. Choose "Authorize with Esri now using the Internet", and click Next.
      7. Leave the default values on the Authorization Information page or, if it is blank, enter your own name and Penn State email address, and use the following contact information for the rest:
        1. Phone Number: 814-865-3433
        2. Location: United States
        3. Zip: 16802
        4. State: PA
        5. City: University Park
        6. Address 1: 302 Walker Building
        7. Department: Geography
        8. Organization: Penn State
      8. On the second Authorization Information page, enter the following:
        1. Your Organization: Education-Student
        2. Your Industry: Higher Education
        3. Yourself: Student
      9. On the Software Authorization Number page, paste the authorization number you copied earlier in the ArcGIS Server box.
      10. Select the option that you do not want to authorize any extensions.
      11. Click Next without checking any of the boxes to evaluate other products.
      12. Click Finish.
      13. Open Windows Explorer and browse to C:\Program Files\ESRI\License10.9\sysgen.
      14. You should see a new file called, keycodes. This is the file you'll provide to the database installer in the next section.
  5. Now that we have installed our underlying RDBMS (SQL Server Express), our keycode file, which we'll use to authorize the database, and the required system components, we can proceed to create an enterprise geodatabase:
    1. Open ArcGIS Pro on your EC2 instance.
    2. Open the Toolbox (under the Analysis tab) and find the Create Enterprise Geodatabase tool.
    3. Run the Create Enterprise Geodatabase tool and select SQL_Server in the Database Platform dropdown.
    4. For Instance, type the name of the instance of SQL Server Express that you just installed: localhost\SQLEXPRESS.
    5. For the Database, enter a name, such as geodata. This will be the name of the geodatabase we create in our SQL Server RDBMS.
    6. Check the box next to Operating System Authentication. This indicates that we'll use our Windows Administrator login to connect to the database, rather than a separate user that we create within SQL Server Express.
    7. Uncheck the box next to Sde Owned Schema.
    8. In the Authorization File box, browse to the keycode file you generated earlier (C:\Program Files\ESRI\License10.9\sysgen\keycodes).
    9. Click the Run button to issue the creation of your geodatabase in SQL Server Express.
  6. Download the Bighorn Sheep data to your EC2 instance and extract it so that the datasets lie immediately under C:\data\BighornSheep. You can choose to download the data onto your local computer and copy it to the cloud using Remote Desktop (like we did in the previous lesson), or you can try to download it directly onto the instance through this web page.

    The data is a map document and a bunch of shapefiles showing Rocky Mountain bighorn sheep habitat and sightings in Carbon County, Utah. These were obtained from the State of Utah Automated Geographic Reference Portal (AGRC) except for the sightings, which are fictional.

    You will prepare a feature service out of this data and create a web editing application that people can use to report bighorn sheep sightings. You'll also see how to expose the habitat boundaries for web editing.
  7. On your EC2 instance, start ArcGIS Pro with a new empty map and make sure the Catalog pane is displayed.
  8. Find the Databases folder and right-click it to create a New Database Connection. Specify SQL Server in the Database Platform dropdown, and enter localhost\SQLEXPRESS in the Instance box. Be sure the Operating System Authentication is selected as the Authentication Type. At this point, ArcGIS Pro should make a connection to the enterprise geodatabase you created in SQL Server. You should now be able to select your database, called geodata, in the Database dropdown. Click OK and you should see an entry for your database appear in the Databases folder in the Catalog pane. Rename your connection, geodata. You have now successfully created an enterprise geodatabase and connected to it from ArcGIS Pro! You now have the capability of loading data in your geodatabase as an alternative to file based formats like shapefiles and file geodatabases.

    You may have noticed that your connection has the suffix, .sde. Technically, when we create a geodatabase within a commercial database like SQL Server, we are using an ESRI product called ArcSDE, or Spatial Database Engine. ArcSDE used to be a separate software package that you would install manually; now, it is embedded within the geodatabase tools in Pro, and the requisite components are loaded into SQL Server to enable the storage of spatial data. For more information about this, check out one of our other Penn State courses, Geog868 - Spatial Database Management.
  9. Right-click your geodatabase 'geodata' and click Import > Feature Class(es).
  10. For Input Features, browse to the lesson data folder you extracted at C:\data\BighornSheep and select all the shapefiles contained therein. Use the Shift key to help you select multiple shapefiles.
  11. Click the Run button to import the data into your enterprise database.

    When the job completes successfully, you should see your datasets appear in the Catalog pane under "geodata".

    In the end, you are going to allow editing for the sightings and sheep habitat layers. Now that you are accessing your data through ArcSDE, you are required to register these datasets as versioned before you can edit them. Versioning is an ArcGIS feature that allows you to have multiple working versions, or edit sessions, of your dataset available. Edits made to these versions can then be incorporated into the master database as needed. Versioning allows you to have multiple editors working on a dataset at the same time.

    Since you're required to register the datasets as versioned, perform the following steps.
  12. In the Catalog pane, find the datasets you just imported. Right-click your dataset named <Database name>.DBO.Sightings and click Manage
  13. Check the Versioning box.
  14. Select the Traditional option and check the box to Move Edits to Base.
  15. Repeat the above two steps to also register <Database name>.DBO.RockyMountainBighornSheep as versioned. This is the habitat layer.

    You don't need to do any more work with versioning beyond the above steps. However, if you want to learn more about versioning you can browse the ESRI documentation.

    Now that you've got the database all set up, you'll register it with ArcGIS Server.
  16. Realize that during the preceding steps, we were running ArcGIS Pro under the Windows user, Administrator. This user had been granted access to the SQL Server Express database earlier in our setup process. ArcGIS Server, however, runs as its own user, arcgis, which we configured during the install of Server with Cloud Formation. Therefore, we need to grant the arcgis user access to the database so that ArcGIS Server can read and write to it.
  17. Right-click your database connection, geodata.sde, in the Catalog pane of ArcGIS Pro and choose Administration, Create Database User.
  18. Leave the Input Database Connection set to your geodata database.
  19. Click the box to specify that we are creating a database user that corresponds to an existing Operating System user (arcgis).
  20. In the Database User box, enter you EC2 computer's name followed by \arcgis. You can find your computer's name by opening Server Manager from the Start button and clicking the Local Server tab along the left. Your computer name will be shown at the upper-left. It will look something like, EC2AMAZ-P48DB6O. In this example, I would enter "EC2AMAZ-P48DB6O\arcgis".
  21. In the Role box enter, db_owner. The db_owner role is one of a number of roles that exists in SQL Server, each with a different set of permissions. We need to grant the arcgis user editing privileges on the feature classes in the geodata database. For the purposes of this class, we are going to grant the arcgis user full administrative rights. (In practice, you probably wouldn’t give this user full control; rather, you’d give it just the amount of privileges that it needs.) For more information about creating users and customizing their permissions within SQL Server, see our course Geog868 - Spatial Database Management.
  22. Click the Run button to create the user.
  23. To register our database with ArcGIS Server, we'll use the Manage Data Store tool under the Share tab in ArcGIS Pro. 
  24. Click the dropdown list and select Portal Items.
  25. Click the Add button and choose Database.
  26. Give your database a name and a tag, and click the Add button under the Publisher Database Connection box. Enter the following information:
    1. Platform: SQL Server
    2. Instance: localhost\SQLEXPRESS
    3. Authentication Type: Operating System Authentication
    4. Database: geodata
  27. Click OK to close this window.
  28. Check the box next to Same as Publisher Database Connection.
  29. Check the box next to your ArcGIS Server in the list to indicate that that's the place where you want the database to be registered.
  30. Check the box to Share the database with Everyone and click ok to complete the registration process.
  31. To confirm that we successfully registered the database with our server, let's use the web-based Server Manager to inspect our Server's properties.
  32. In a web browser, enter the URL for your Server Manager. It will look something like,
  33. Sign in and click on the Site tab along the top.
  34. Click on the Data Stores heading along the left and you should see both your geodata database and your C:\data folder in the list of registered items. Use the Validate All button to confirm that Server can access all of the data stores.

    Your data is now loaded, prepared, and registered with ArcGIS Server. In the next section, you'll start working with some maps that use this database. You will prepare them to run as feature services that can be edited over the web.