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. ODBC 17 Driver
    3. .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.
  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. Finally, install the required ODBC driver so that ArcMap is able to communicate with the version of SQL Server we installed:
    1. Double-click the ODBC 17 Driver install file.
    2. Accept the terms, accept the default settings, and finish the install.
  5. 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 (x86)\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 next couple of pages and stop at the Software Authorization Number page.
      8. On the Software Authorization Number page, paste the authorization number you copied earlier in the ArcGIS Server box.
      9. Select the option that you do not want to authorize any extensions.
      10. Click Next without checking any of the boxes to evaluate other products.
      11. Click Finish.
      12. Open Windows Explorer and browse to C:\Program Files (x86)\ESRI\License10.7\sysgen.
      13. You should see a new file called, keycodes. This is the file you'll provide to the database installer in the next section.
  6. 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 ArcMap.
    2. Open the Toolbox and find the Create Enterprise Geodatabase tool under Data Management Tools and Geodatabase Administration.
    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 (x86)\ESRI\License10.7\sysgen\keycodes).
    9. Click OK to issue the creation of your geodatabase in SQL Server Express.
  7. 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.
  8. On your EC2 instance, start ArcMap with a new empty map and make sure the Catalog window is displayed (click the Windows menu, then Catalog).
  9. Expand the Database Servers node of the Catalog tree. Double-click Add Database Server, enter the Database Server name "localhost\SQLEXPRESS" and click OK. You should see your 'geodata' geodatabase inside the database server connection you just made.
  10. Right-click your geodatabase 'geodata' and click Import > Feature Class (multiple).
  11. For Input Features, browse to the lesson data folder you extracted at C:\data\BighornSheep and select all the shapefiles contained therein. (You may need to add a folder connection to the C: drive.) Use the Shift key to help you select multiple shapefiles.
    Screen shot. Selection of Shape Files.
    Figure 3.1 Selection of Shape Files
  12. Click Add, and then click OK.
    You won't see anything except a small status bar in the lower-right corner of your ArcMap window. ArcGIS is using one of its geoprocessing tools to import the datasets into the geodatabase, and ArcGIS 10 introduced the concept of background processing that allows you to continue using ArcMap while a geoprocessing job is running. After a few minutes, you should see a small success message appear in the lower-right corner temporarily. If you miss the message, or if you just want to see how things are going, you can click Geoprocessing > Results and expand the tree to see messages about the current job.

    When the job completes successfully, you should see your datasets appear in the Catalog window 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 branches, 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.
  13. In the Catalog tree, find the datasets you just imported. Right-click your dataset named <Database name>.DBO.Sightings and click Manage > Register As Versioned.
  14. Check the option Register the selected objects with the option to move edits to base, then click OK.
  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 A quick tour of versioning in the ArcGIS Desktop Help.

    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 ArcMap 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 Server (localhost_sqlexpress.gds) in the Catalog window of ArcMap and choose Permissions.
  18. Click Add User and enter arcgis in the box.
  19. Click OK, and you should see the arcgis user added to the list, along with your Administrator user.
  20. Select the arcgis user, and check the box designating this user as a Server Administrator. This may not be the best practice in a production setting, but since we're using the same users to perform multiple tasks, we'll be generous and grant it admin privileges. Click OK to complete the process.
  21. In the Catalog window, find the GIS Servers node and make sure you are connected to your ArcGIS Server (this requires double-clicking the node if you see a little red x). Then right-click your ArcGIS Server connection, and click Server Properties.
  22. Click the Data Store tab, and in the top section Registered Databases, click the plus (+) button.
  23. Enter a Name such as Carbon County sheep database, and click the Add button.
  24. Enter the information as follows:

    Then click OK.

    • Database Platform - SQL Server
    • Instance - localhost\SQLEXPRESS
    • Authentication Type - Operating system authentication
    • Database - geodata
  25. Ensure that Same as publisher database connection is checked, then click OK. However, please note that if you were going to be doing any service publishing from your home computer, or some computer other than the server, you would add database connection information in this lower section of the dialog box representing the database seen by the publisher's machine. Don't try this with this lesson. 
  26. Click Validate All - and make sure you see a green checkbox next to your newly registered database. Click OK. If you don't see a connection, right-click your database geodata (VERSION:dbo.DEFAULT) and click Save Connection. This should make it appear in the Database Connections folder with a .sde file extension.

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.