Home / Tutorials

Geodatabases

An ArcGIS geodatabase is an organized collection of geographic datasets held in a common file system folder (file geodatabase) or a multiuser relational database management system (RDBMS) (ESRI 2024).

There are two types of geodatabases that you will commonly work with in ArcGIS Pro:

This tutorial will cover basic geodatabase concepts and techniques in ArcGIS Pro and ArcGIS Enterprise.

File Geodatabases

File geodatabases are collections of files in a folder in local storage on your personal machine that are used to store geodatabase tables and relationships (ESRI 2024).

A project geodatabase is a file geodatabase unique to every ArcGIS Pro project that is the default location for new data created or imported into the project.

The actual files in file geodatabases are managed by ArcGIS Pro and are cryptically named by the software, so you will generally want to avoid making any direct changes to the files outside of ArcGIS Pro.

A folder containing a project geodatabase

Tables

A relational database table is a collection of related data organized as columns and rows.

For example, this is a simple table of Illinois Counties in the Chicago metropolitan area (Chicago–Naperville–Elgin, IL–IN–WI Metropolitan Statistical Area).

County Population Median_Household_Income
Cook 5,275,541 76,632
Dekalb 100,420 68,590
DuPage 932,877 102,152
Grundy 52,533 89,993
Kane 516,522 93,343
Kendall 131,869 101,447
Lake 714,342 101,442
McHenry 310,229 98,907
Will 696,355 96,668

An ArcGIS feature class is a table representing a set of common features that have a common set of attributes and a common spatial representation (point, line, or polygon). Feature classes stored in relational databases have a hidden shape column and associated spatial characteristics (such as the type of projection) (ESRI 2024).

Map of Chicagoland Illinois counties polygon feature class

Data Types

Relational database table columns have specific data types that represent the types of values that can be stored in those columns. The names for these data types come from the underlying programming language(s) used to store the data in computer memory and in databases. Data types commonly used for feature classes include:

Other types you will encounter (especially in older data) include the following. These are shorter variants on the types given above and can be used when trying to minimize storage needs for tables with extremely large numbers of records on systems with limited resources.

Tables used for feature classes in ArcGIS Enterprise contain additional columns with feature IDs, shape size, and shape geometries. These are maintained by the software and you will generally want to leave these columns alone to avoid corrupting your data.

You can view the data types in a feature class table by showing the Attribute Table and switching to Fields View.

Viewing feature class field types in ArcGIS Pro

Export Features

The Export Features tool can be used to copy data from shapefiles or feature services into new feature classes in a geodatabase.

For this example we create a Counties feature class in the project geodatabase from the Minn 2015-2019 ACS Counties feature service in the University of Illinois ArcGIS Online organization, with a filter to bring in just counties in Illinois.

Export features from a feature service

When importing shapefiles, you need to first unzip the files if they were distributed in a .zip archive. You also need to make sure that the destination is in the project geodatabase rather than simply another shapefile.

For this example we create a County_Roads feature class from a TIGER/Line shapefiles of roads in Peoria County, IL.

Export features from a shapefile

Export Table

The analogous Export Table tool can be used to import CSV files into non-spatial tables in a geodatabase.

For this example, we creae a Manufacturing table with manufacturing data by county from the US Census Bureau's 2017 Economic Census.

Export table

Table Data Input

You can create a new database table in the Catalog Pane by right-clicking on the database, selecting New, and choosing the type of table or feature class to create.

Manually creating a new database table

Standalone File Geodatabases

File geodatabases separate from the project geodatabase can be created and used in ArcGIS Pro using the Create File Geodatabase tool.

Some organizations will share data as zipped file geodatabases, and you can create new file geodatabases if you need to keep your data separate from your project.

Creating a file geodatabase

Domains

While column data types provide useful restrictions on the ranges of values you can place in columns, there are situations where you will want to further constrain the values that can be entered into specific columns to maintain consistency across the table and mitigate the entry of erroneous or impossible values.

Domains are geodatabase rules that describe and limit the possible values for fields.

Domains are defined for a geodatabase as a whole, and then applied to specific table fields as needed.

Categorical domains are used to restrict the values of text field to a specific set of possible categorical values.

Categorical domains

Quantitative domains can be used to restrict numeric fields (integer and real) to specific ranges of values.

Numeric domains

Date domains can be used to restrict date fields to specific ranges of dates.

Date domains

Enterprise Geodatabases

ArcGIS geodatabases are an extension of relational databases.

When you are collaborating with multiple users in an organization (such as a government office), you will often want to keep your data in an enterprise database managed with a relational database management system (RDBMS).

While the organization of relational databases can initially seem overly complex, this technique for storing large amounts of complex data has proven to be exceptionally flexible and powerful, which explains the popularity and ubiquity of relational databases for enterprise data storage.

pgAdmin 4 administrative console for PostgreSQL

Data Stores

An ArcGIS Enterprise data store is database used for storing geospatial data in an enterprise environment.

RDBMS data stores can provide significant advantages over keeping everything in the portal data store.

However, RDBMS require significant additional maintenance compared to using the portal data store for everything, and smaller organizations may find the portal data store adequate to satisfy organizational needs.

Data stores in ArcGIS Enterprise

Database Architecture

Enterprise geodatabase architecture

Connecting to an Enterprise Geodatabase

You can connect to an enterprise geodatabase in the ArcGIS Pro Catalog Pane by right-clicking on Databases and selecting Connect.

You will need to enter a database user name and password supplied by your systems administrator. Note that these credentials will probably be different from the credentials you use to login to portal.

Adding a connection to an RDBMS in ArcGIS Pro

Copying Tables into an Enterprise Geodatabase

If you have a table or feature class in an existing project geodatabase, you can drag and drop in the Catalog Pane to copy the data into (or from) an enterprise geodatabase.

Copying data between geodatabases in the Catalog Pane

Disconnecting from a Database

Unfortunately, ArcGIS Pro has no mechanism for disconnecting from a database.

This will cause permissions problems if you need to reconnect as an administrator or different user.

The only way to disconnect from a database is to close ArcGIS Pro and restart.

Administrative Consoles

A database administrative console is a user interface that administrators of enterprise databases can use to analyze performance and diagnose issues.

pgAdmin 4 is the administrative console that is included in installations of PostgreSQL. pgAdmin has a rich set of tools but can be somewhat challenging to install and use, especially for novices.

pgAdmin 4

Adminer is a web-based console in PHP that supports multiple types of databases and is somewhat simpler to use with PostgreSQL than pgAdmin. An administrator can install Adminer on a Linux server running PHP.

To interact with a database, you need to log in with credentials supplied by the database administrator.

Adminer

Creating Enterprise Geodatabases

Database administrators can create new enterprise databases in an RDBMS using the Create Enterprise Geodatabase tool.

Create enterprise geodatabase

An administrator can delete a database.

DROP DATABASE db_name;

Creating Users and Schemas

A geodatabase administrator can create a new user, which also creates a new schema.

Create database user

To list users:

SELECT * FROM pg_user;

To view users currently connected to the database:

SELECT * FROM pg_stat_activity

To change a user password:

ALTER USER user_name WITH PASSWORD 'new_password';

To drop users:

DROP USER username;

Granting Privileges

Schema owners can grant privileges to view and/or edit tables to other users in ArcGIS Pro by right-clicking on the table and selecting Privileges...

Granting privileges

DB administrators can create group roles, grant privileges to group roles, and add individual login roles to group roles

Selection

Structured query language (SQL) is the primary language for communicating with databases for creating tables, inserting data into tables, querying data from tables, and performing administrative actions on the database.

While Portal for ArcGIS and ArcGIS Pro hide geodatabase interaction behind graphical user interfaces and save users from the learning curve and complexity of SQL, there are tools in ArcGIS Pro where some knowledge of SQL can be helpful for performing complex operations, and general knowledge of SQL can be a useful job skill when working with databases in the non-ESRI world.

SELECT Statements

SELECT is the primary SQL command for querying data from tables.

The simplest form of a SELECT statement is:

SELECT column_names FROM table_name WHERE condition;

For example, this statement selects all counties in Illinois.

SELECT * FROM Counties WHERE ST = 'IL';

SELECT statement WHERE clauses indicate which records to select. They are composed of one or more comparisons, which can be tied together with logical operators.

Simple Comparisons

Simple comparisons indicate a set or range of field values that are acceptable for records to be selected. Operators include:

Note that text (strings) needs to be included in single quotation marks.

SELECT * FROM Counties WHERE Region = 'Midwest';

The greater than and less than symbols can be used to select features within ranges of values.

SELECT * FROM Counties WHERE Population >= 100000;

Logical Operators

Logical operators are used in SQL to combine multiple comparisons into compound statements.

Parentheses are commonly used to avoid ambiguous syntax and make statements easier to interpret.

For example, to isolate small, high-income counties:

SELECT * FROM Counties WHERE (Total_Population <= 100000) AND (Median_Household_Income > 70000);

Logical operators can be used to isolate ranges of quantitative values. This example is counties in the middle quartiles of median household income.

SELECT * FROM Counties WHERE (Median_Household_Income >= 43000) AND (Median_Household_Income <= 60000);

Mathematical Operators

Mathematical operators permit calculations prior to comparison. The operands can be fixed values or names of fields.

For example, this statement selects countries with a lower population density than the US median.

SELECT * FROM Counties WHERE (Total_Population / Land_Area_Sq_km) <= 36;

Range Comparisons

Range comparisons are statements that test whether field values are in a particular range or set of values.

BETWEEN ranges can be clearer than compound comparison statements.

SELECT * FROM Counties WHERE Median_Household_Income BETWEEN 43000 AND 60000;

Likewise with IN set operations. This example selects the five Midwestern states.

SELECT * FROM Counties WHERE ST IN ('OH', 'IN', 'IL', 'WI', 'MI');

Percent signs (%) are used as wildcards in LIKE statements to match any text. This statment isolates counties that have "Red" in their name.

SELECT * FROM Counties WHERE Name LIKE '%Red%'

LIKE statements can be useful for working with groups of US Census Bureau GEOIDs. For example, this statement isolates all census tracts in Peoria County (FIPS 17143). 

SELECT * FROM Tracts WHERE GEOID LIKE '1400000US17143%'

Column Selection

SELECT statements can also be used to select specific columns from tables.

For example, to select only a minimum number of columns to make a choropleth of median age in Illinois:

SELECT GEOID, Name, Median_Age, shape FROM Counties WHERE ST = 'IL';

Aggregation

Aggregation is the combination of multiple values into a smaller set of values for comparison or analysis. SQL has multiple functions for aggregation and commonly used functions include:

One or more of these functions can be used in the SELECT column list. For example, this statement shows the range and average of county land area in square miles for US counties.

SELECT MIN(Square_Miles), AVG(Square_Miles), MAX(Square_Miles) FROM Counties

Aggregation functions can also be used for comparisons, although the syntax is a bit more complex than a simple SELECT.

Subqueries

Conceptually, SQL SELECT statements operate on one record at a time while aggregation functions operate on multiple records at a time.

Subqueries are queries within queries that can be used for performing complex operations that involve working with more than one row at a time.

Subqueries are enclosed in parentheses in a statement. For example, this statement selects the county with the highest median household income.

SELECT * FROM Counties 
	WHERE Median_Household_Income = (SELECT MAX(Median_Household_Income) FROM Counties);

This statement selects countries with above average median household income.

SELECT * FROM Counties 
	WHERE Median_Household_Income > (SELECT AVG(Median_Household_Income) FROM Counties);

Subqueries can become exceptionally complicated, but are often useful to encode complex queries in SQL where they can be optimized and executed quickly by the RDBMS.

Filters

There are three places you can perform selects in ArcGIS Pro:

Filters in ArcGIS Pro are used to subset groups of features for display or tool operations. Filters can be constructed using dialog options for assistance, or can be defined using the same syntax used for SQL WHERE clauses.

The Export Features tool includes a filter option. The Export Features tool is more appropriate than other select options when:

This is an example of filtering large counties from the Minn 2015-2019 ACS Counties feature service with a population of 1 million persons or more (Large_Counties).

Using a filter on Export Features

Definition Queries

Definition queries are filters added to map layers so they display only the features that satisfy the filter conditions. Definition queries also limit the number of features processed when that layer is used with an analysis tool.

A definition query is more appropriate than other select options when:

Definition queries are added by right-clicking on the layer, selecting Properties, and then selecting Definition Query.

For example, this adds a definition query to show only countries with a population density of 100 residents per square mile or fewer.

Definition query

Query Layers

Query layers in ArcGIS Pro are map layers that use SQL SELECT statements to get data from enterprise geodatabases. Query layers permit access to the broad range of SQL SELECT syntax beyond simple WHERE clauses created as filters.

A query layer is more appropriate than other options when:

For example, this query layer selects specific columns for counties with higher than average median household income.

  1. Under Analysis, Tools run the Make Query Layer tool.
  2. Input Database Connection: Select the enterprise database
  3. Output Layer Name: Provide a meaningful name (Wealthy_Counties)
  4. Query: Always include the Objectid and shape in your selected columns.
    5. SELECT Objectid, Name, ST, Median_Household_Income, shape
	FROM Counties WHERE Median_Household_Income > 
	(SELECT AVG(Median_Household_Income) FROM Counties)
  5. Define the spatial properties of the layer: If unchecked, the tool will attempt to infer the spatial properties (shape, CRS) from the first record returned from the database. Check this box and specify those properties below if no symbols display.
  6. Unique Identifier Field(s): Use a field (text or numeric) that can be used to uniquely identify features (Objectid).
  7. Shape Type: Select the type of shape used by the feature class (Polygon). Selection of a different shape type from the data will cause the tool to silently fail and display no features.
  8. Coordinate System: Select the feature class's coordinate system. This example data matches Current Map, but specification of an incorrect coordinate system will cause the tool to silently fail and display no features.
Creating a query layer

Unfortunately, the Make Query Layer tool will accept your query even if there are problems with the syntax, and the layer will appear in the Contents pane with no features.

You can view and validate a layer's query under Properties, Source.

Correcting and validating a query layer

Subset Columns in ArcGIS Pro

When working with tables containing large numbers of columns, selecting only the necessary subset of those columns can make working with the data easier.

The Export Features tool includes a Field Map that can be used to add, delete, rename, and reorder fields, as well as change other field properties.

For example, this creates an County_Income feature class by selecting specific fields from the Minn 2015-2019 ACS Counties feature service.

Selecting columns with a field map

When working with existing tables, you can define aliases and hide fields in the Attribute Table. Alias field names in ArcGIS Pro are alternative (often more-readable) field names that can be used while retaining the original field names in the data.

Defining aliases and hiding columns in the attribute table

Joins

Relationships

Relationships are the logical connections between tables based on keys, and this aspect of relational databases distinguishes relational databases from other methods of storing data.

A key is a field in a table that can be used to uniquely identify specific rows in that table.

A foreign key is a field or set of fields in one table that can be used to connect related rows in another related table in a join.

For example, take this table of mean Commuting time (in minutes) in each county.

A one-to-one relationship between tables means that at most one row in one table matches with a row in the other table. In this example of voter participation percentages in the 2020 election, exactly one row in the Particpation table is matched to exactly one row in the Counties table above.

County Participation
Cook 72.2
Dekalb 74.2
DuPage 76.6
Grundy 79.9
Kane 73.7
Kendall 76.7
Lake 69.4
McHenry 69.7
Will 75.1

A many-to-one relationship means that multiple rows in one table can match a single row in the other table. For example, this Releases table represents toxics releases from facilities in different counties. Each county can have more than one facility.

County Company Release tons 2022
Cook Wieland Metals 3,619
Cook Ingredion 428
DuPage SWD 47
DuPage KNS 9
McHenry Rohm and Haas 190
Will ExxonMobil 1,000

Schema

A schema is a set of table definitions and relations between those tables in a relational database.

Entity-relationship diagrams (E-R diagrams) graphically represent relational database schema and are commonly used in the creation and analysis of relational database designs.

Entity-relationship diagram

E-R diagrams can be quite complex when the database is used for complex business data.

(Wikimedia Commons 2018)

The Generate Schema Report tool can be used in ArcGIS Pro create detailed reports on the feature classes, tables, domains, and relations con+figured in a file or enterprise geodatabase. While these reports do not contain graphical E-R diagrams, the reports can be useful for diagnosing database problems and creating new databases with similar capabilities.

Generating a schema report in ArcGIS Pro

Table Joins

A join is a database operation where you connect two related tables based on keys in the tables that are used to match rows in one table to rows in the other table.

Joins are especially useful for attaching non-spatial data tables to spatial feature classes.

Attribute joins

When joining two tables, the table you start with (usually on the left side of a diagram) is the target table, which is often a feature class when working with geodatabases. The table you are joining to the target table is the join table.

For example, a join of the Counties and Participation table based on the common County key would result in this table:

County Population Median_Household_IncomeParticipation
Cook 5,275,541 76,632 72.2
Dekalb 100,420 68,590 74.2
DuPage 932,877 102,152 76.6
Grundy 52,533 89,993 79.9
Kane 516,522 93,343 73.7
Kendall 131,869 101,447 76.7
Lake 714,342 101,442 69.4
McHenry 310,229 98,907 69.7
Will 696,355 96,668 75.1

Join Types

You will commonly use two of the four different types of joins in ArcGIS Pro.

Join types

Join Field

The Join Field tool can be used to perform attribute joins based on a common key field.

This example joins the Counties and Participation tables described above using the county name as the key.

Join Field adds columns to the target feature class, and if you want to keep a copy of the feature class without the new columns, you should first run Export Features to create a duplicate that will be modified (Participation_Counties).

Join Field keeps all input features to create a left join.

Join field

Add Join

ArcGIS Pro supports in-memory joins that can be added to layers by right-clicking in the Contents Pane and selecting Joins and Relates and Add Join.

Add Join (left join)

You can leave Keep all input records checked for a left join or uncheck for an inner join.

Add Join (inner join)

SQL Joins

In an SQL SELECT statement the LEFT JOIN keywords are added after the column list or wildcard, folowed by the keyword ON and the condition connecting the keys for matching rows.

Because you are working with multiple tables, if you have columns in the two tables that have the same name, you need to identify fields with both the name of the table and name of the column in the format table_name.column_name.

SELECT Counties.*, Participation.Participation FROM Counties 
	LEFT JOIN Participation 
	ON Counties.Name = Participation.County

An INNER JOIN would use the same syntax:

SELECT Counties.*, Participation.Participation FROM Counties 
	INNER JOIN Participation 
	ON Counties.Name = Participation.County

Additional WHERE conditions can be added after the join if needed.

SELECT Counties.*, Participation.Participation FROM Counties 
	INNER JOIN Participation 
	ON US_Counties.Name = Participation.County
	WHERE Median_Household_Income > 60000;

WHERE can also be used to create an inner join of tables in the FROM clause.

SELECT * FROM Counties, Participation 
	WHERE Counties.Name = Participation.County

Joins in Query Layers

Query layers in ArcGIS Pro are map layers that use SQL SELECT statements to get data from enterprise geodatabases.

When performing joins using tables in file or enterprise geodatabases, you should select only specific columns from the join table to avoid bringing in duplicate OBJECTID or key fields that will cause the join to fail without any kind of warning or error message.

SELECT Counties.*, Participation.Participation FROM Counties 
	INNER JOIN Participation 
	ON Counties.Name = Participation.County
Join with a query layer

Mismatched Names

Joins are very sensitive to idiosyncrasies of the underlying data, and there are a variety of problems that can cause joins to have missing rows or fail altogether.

Text keys used for joins must match exactly and the use of place names as keys can result in incomplete or failed joins.

A common adage is that data scientists spend 50% to 80% of their time cleaning data so that it can be analyzed (Lohr 2014).

Options for addressing these mismatches include:

Incomplete join with mismatched names

Mismatched Key Data Types

Keys used for joins must be of the same data type, and mismatched key data types are especially common when working with numeric codes that are represented as text in one table and an integer in the other table.

You can use Calculate Field to create a numeric version of the text code

This example changes the text zip code field from the TIGER cartographic boundary file (Zip_Codes) to numeric to match numeric zip codes from Illinois State Cancer Registry data (Breast_Cancer)

  1. View the feature class Attribute Table and select Add.
  2. Return to the Attribute Table, right click on the new field, and select Calculate Field.
  3. Run Export Features tool to create a copy of your feature class that will be modified below (Cancer_Zip).
  4. Run the Join Field tool and join the two tables using the converted field.
Calculating a numeric field from a text field

You can avoid the headaches of field calculation when working with query layers by using the CAST operator to transform (cast) the text field to a numeric (INT) field.

SELECT Zip_Codes.*, Breast_Cancer_Count FROM Zip_Codes
	INNER JOIN Breast_Cancer
	ON CAST(Zip_Codes.NAME20 AS INT) = Breast_Cancer.Zip_Code
Casting a data type in a query layer

Leading Zero Problem

Numeric codes like FIPS codes or zip codes sometimes have leading zeroes, and the inadvertent removal of leading zeros by the software creates a variation of the mismatched name problem.

Although it is possible to reformat the numbers as text with the leading zero, the easiest course of action will probably be to convert both the feature class and table fields to numeric (Long integer).

Spatial Queries

Spatial queries are database queries defined by spatial relationships between features, such as intersection or proximity.

Select by Location

The Select Layer by Location tool selects features based on a spatial relationship to features in another dataset or the same dataset.

Note that the "select" in the name of the tool refers to selection in the editor and if you need to preserve the editor selection, you will also need to run Export Features to export the selection to a new feature class.

This example creates a new Hospitals layer in or near Peoria County, Illinois. The hospital location data is from the US Department of Homeland Security open data site.

Select by location

Summarize Within

The Summarize Within tool can be used to aggregate values from one layer (summary features) into polygon areas (input polygons).

This example summarized the count of hospitals and total hospital beds by county.

Summarize within

Nearest Neighbor

The Near tool calculates distance between each input feature and the closest feature in another feature class.

In this example we calculate the distance from each Illinois county to the nearest hospital. Because the Near tool modifies the input feature class, we first use the Export Features tool to create a new feature class (Hospital_Distance) to preserve the original data.

Nearest neighbor

Spatial Join

A spatial join copies attributes from a join layer into features in a target layer that match based on a spatial relationship.

The Join Features tool can be used to perform both attribute joins (above) and spatial joins.

In this example we spatial join hospitals to counties to compare neighborhood income distribution.

Spatial join features

Geometry Attributes

The Calculate Geometry Attributes tool can be used to add columns to a feature class with geometric characteristics of individual features like centroid lat/long, perimeter, or area.

Calculate geometry attributes

PostGIS

The PostGIS extensions to PostgreSQL provide additional operators that can be used to create WHERE selection conditions on spatial relationships like proximity or intersection.

Select by Location

The PostGIS ST_DWithin() function indicates which features in the first parameter feature class are contained with a specified distance (third parameter) feature(s) in the second parameter feature class.

SELECT Hospitals.* 
	FROM Hospitals, County_Boundary
	WHERE public.st_dwithin(
		public.st_transform(Hospitals.shape, 102009),
		public.st_transform(County_Boundary.shape, 102009), 50000)
Select by location using ST_Within

A query using ST_DWithin() can be used as query layers.

A query layer using ST_Within

Summarize Within

The PostGIS ST_Intersects() function indicates which features in the first parameter feature class intersect (overlap) features in a second parameter feature class.

SELECT Counties.Name, SUM(Hospitals.beds) AS beds
        FROM Counties
        JOIN Hospitals
        ON public.st_intersects(
                public.st_transform(Counties.shape, 102009),
                public.st_transform(Hospitals.shape, 102009))
        GROUP BY Counties.Name
        ORDER BY Counties.Name
Summarize within using PostGIS functions

If you want to create a query layer with a summarized intersection, you also need to include the polygon shape field in the selected columns and the GROUP BY so the software has a geospatial field to map.

SELECT SUM(Hospitals.beds) AS beds,
        COUNT(Hospitals.beds) AS hospitals,
        Counties.Name, Counties.shape
        FROM Counties
        JOIN Hospitals
        ON public.st_intersects(
                public.st_transform(Counties.shape, 102009),
                public.st_transform(Hospitals.shape, 102009))
        GROUP BY Counties.Name, Counties.shape
        ORDER BY Counties.Name
A query layer Summarize within using PostGIS functions

Nearest Neighbor

The MIN function with the PostGIS ST_Distance() function can be used to calculate distance between each feature in the first parameter feature class to the the closest feature in the second parameter feature class.

SELECT MIN(public.st_distance(
		public.st_transform(County_Tracts.shape, 102009), 
		public.st_transform(Hospitals.shape, 102009))) 
		/ 1000 AS distance_km,
	County_Tracts.objectid, County_Tracts.shape
	FROM County_Tracts, Hospitals
	GROUP BY County_Tracts.objectid, County_Tracts.shape
Finding distance to the nearest neighbors using ST_Distance

Spatial Join

A spatial join copies attributes from a join layer into features in a target layer that match based on a spatial relationship.

The ST_Within() function indicates which features in the first parameter feature class are located entirely within a second parameter feature class.

In this example we spatial join hospitals to census tracts of ACS data in Peoria County, IL to find the median household income in the neighborhoods surrounding each hospital.

SELECT County_Tracts.Median_Household_Income, County_Hospitals.*
	FROM County_Hospitals
	INNER JOIN County_Tracts ON
	public.st_within(
		public.st_transform(County_Hospitals.shape, 102009),
		public.st_transform(County_Tracts.shape, 102009))
PostGIS spatial join

Joined feature classes can also be used in query layers, although the performance may be unacceptably slow when joining feature classes with large numbers of features.

PostGIS spatial join query layer

SELECT INTO

If you have a complex, slow query and access to an administrative console that will let you submit queries, you can execute queries and save them to new feature classes or tables using an INTO clause.

If you create a table with a geospatial shape column, you will need to right-click on the new feature class in the Catalog Pane and select Register with Geodatabase to use the layer in ArcGIS Pro.

SELECT Counties.Name, SUM(Hospitals.beds), COUNT(Hospitals.beds), Counties.shape
	INTO County_Hospital_Summary
	FROM Counties
	JOIN Hospitals 
	ON public.st_intersects(
		public.st_transform(Counties.shape, 102009), 
		public.st_transform(Hospitals.shape, 102009))
	GROUP BY Counties.Name, Counties.shape
	ORDER BY Counties.Name
Select into

Geometry Attributes

PostGIS functions can be used to calculate geometric characteristics of individual features like centroid lat/long, perimeter, or area.

Area calculations use the North America Albers equal area conic projection (ESRI 102008) 

SELECT Counties.name,
	public.st_area(public.st_transform(Counties.shape, 102008)) / 1000000 AS square_km,
	public.st_perimeter(public.st_transform(Counties.shape, 102008)) / 1000 AS perimeter_km,
	public.st_astext(public.st_centroid(Counties.shape)) AS longlat,
	Counties.objectid,
	Counties.shape
	FROM Counties
PostGIS geometry attributes

Publishing

Publishing in ArcGIS Enterprise involves making data available across a network with a service URL or REST endpoint. Published data can be used to create web maps, web apps, or ArcGIS Pro layers.

There are two sources of data for a service:

There are characteristics that vary across four types of published services:

Characteristic Map image Feature Image tile Vector tile
User can change styling No Yes No No
Performance with large data sets Slow Slow Fast Fast
Downloadable data No Yes No No
Requires enterprise geodatabase Yes No No No
Rendering Server Client Server Server
REST server MapServer FeatureServer MapServer MapServer

Publishing Feature Classes

Feature classes can be published in all four different types of services.

To publish a feature class:

  1. Add the feature class to a map in ArcGIS Pro.
  2. Adjust the Symbology as desired.
  3. Right click on the layer in the contents and select Sharing, Share as Web Layer
  4. In the Contents page in your portal, verify that the feature service has been published.
Publishing a feature class as a feature service

Feature services that draw from the portal data store are listed in the portal Content page as Feature layer (hosted).

Feature services that draw from an enterprise geodatabase (registered data) are listed in the portal Content simply as Feature layer.

Hosted feature services (health data in data store) vs. feature services ( parcels in enterprise geodatabase)

If you get Error 00231 Layer's data source must be registered with the server when trying to publish, right click on the ellipsis (...) in the error, select Register Data Source With Server and register your schema with the portal. Your data store name should a generalized name you can reuse in the future (e.g. Peoria_County) rather a name specific to one feature service (e.g. Peoria_County_Parcels). You can view registered data stores with the Manage Registered Data Stores tool.

Registering a schema as a portal data source

Publishing Raster Data

Rasters can be published as map images or tile layers.

Publishing a raster image

Group Layers

Group layers combine multiple related feature layers into a single bundle that can be easily added to web maps and kept together in the layers list.

Group layers are useful for layers that function as a group (such as police stations and police precinct boundaries) or represent different scales of the same variable (states + counties + tracts).

  1. Add all layers to the project and give them related names (Minn_2023_PLACES_Counties, Minn_2023_PLACES_Tracts).
  2. Symbolize the layers consistently.
  3. For each layer, right click on the layer and select Properties. On the General tab, Add visibility ranges so that tracts are visible when zoomed in and counties are visible when zoomed out.
  4. Right click on the map in the Contents Pane and select New Group Layer.
  5. Right click on the group layer and select Sharing, Share as Web Layer.
  6. Click Analyze.
  7. Click Publish.
  8. Go into your portal, select the new feature layer, and Open in Map Viewer to verify that the symbology and visibility work as desired.
Publishing a group layer

Relationship Classes

Relationship classes define relationships between geodatabase tables.

To publish a feature class and related table together in a feature service:

  1. Add both the feature class and the related table to the map.
  2. Run the Create Relationship Class tool.
  3. Right click on the feature class and Share as Web Layer as a Feature Layer.
  4. Auto-assign feature IDs sequentially
  5. After publication finishes, Create a new map with the service
  6. Edit the popups, Add Content with Related Records.
Publishing a feature service and table linked with a relationship class

ArcGIS Enterprise Sites

ArcGIS Enterprise Sites is a web interface for distributing content from portal to users outside your organization. Sites is the same user interface used for ArcGIS Hub, a cloud platform commonly used by government agencies for open data portals.

Create and Customize the Site

  1. On your portal Content page, select Create App and Sites.
  2. Give your site a meaningful name (Peoria County Illinois Open Data) and Create site.
  3. Delete the unused placeholder sections.
  4. Update the placeholder contact e-amil address.
  5. Update the copyright.
  6. Change the top row background image.
Creating an ArcGIS Sites site

Content Library

The site content library is the collection of portal services data objects that are available to site visitors.

  1. Open the site in edit mode.
  2. In the second drop-down menu on the edit navigation bar (the name of the site), select Content Library.
  3. Click the Find existing content
  4. Select services listed under Data.
    • When two Datasets with the same name are listed, one will probably be the service definition (.sd file), and the other is the feature service you want. You will need to try opening them to find out which is which.
    • After adding all services, click Save.
  5. When you are done, click Save on the lower left side of the screen.
Adding feature services to the content library

Content Cards

Content cards are icons in the Explore your data section of your site home page that have links to different categories of available data.

The categorization of data for these categories can be made with content tags, with explicit category assignments, or with portal groups. For a small site like this, the easiest option is to use tags.

  1. Adjust the Content cards to define and represent categories.
  2. Add tags to assign the data to categories.
  3. View the categories to verify successful categorization.
Adding content cards

Data Export

One of the advantages of using ArcGIS Enterprise Sites for open data portals is that the software provides the ability for users to download feature data in multiple formats: CSV, zipped shapefile, GeoJSON, file geodatabase, and Excel spreadsheets.

To allow users to export data from a feature service, go into the Settings for the feature service layer(s) and select Allow others to export to different formats.

Data export in different formats

Publish the Site

Changes made in the site editor are staged as drafts until you publish them.

Publish the site

Deleting a Site

You can delete a site by clicking the ellipsis (...) at the top of the editor page and selecting Delete site.

Delete a site