You can connect from ArcGIS clients and work with the data in the database management systems or data warehouse appliances listed here. To see how databases are used with ArcGIS Server, see Databases and ArcGIS Enterprise.
Database management systems differ from each other in how they are implemented and what functionality they provide. As a result, there are sometimes caveats or rules you need to be aware of when working with each database type in ArcGIS. These are listed in the following sections.
Dameng
Spatial data is stored in the Dameng geometry type. A typical installation of Dameng includes a spatial type; however, you must initialize it to use it.
See what versions of Dameng are supported with ArcGIS 10.7.1.
IBM Db2
Db2 authenticates connections to the database using operating system logins. However, when you connect to Db2 from the Database Connections dialog box or the Create Database Connection geoprocessing tool, you can specify the Database authentication option. This allows you to save the specific user name and password of the login, which is required if you will register the database connection with ArcGIS Server.
ArcGIS requires a unique identification field to render features in a map. ArcGIS relies on the database to insert values to the unique identification field when new features are created. Since Db2 z/OS does not have a data type or built-in mechanism to populate values in an ID field, ArcGIS tools that create new rows in a database table cannot be used with databases in Db2 z/OS. Also, since you cannot insert features to a Db2 z/OS table from ArcGIS, you cannot publish a feature service that contains data from a Db2 z/OS database.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, the ability to replicate data, or you want to publish feature services containing data from Db2 z/OS, you can enable a geodatabase in your Db2 database.
IBM Informix
Informix authenticates connections to the database using operating system logins. When connecting to Informix from the Database Connections dialog box or the Create Database Connection geoprocessing tool, though, you can specify the Database authentication option. This allows you to save the specific user name and password of the login, which is required if you will register the database connection with ArcGIS Server.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your Informix database.
See what versions of Informix are supported with ArcGIS 10.7.1.
IBM Netezza Data Warehouse Appliance
IBM Netezza Data Warehouse Appliance is deprecated at this release.
Data warehouses typically store large amounts of data. To use ArcGIS to visualize, perform analyses, or publish data to Netezza, use a subset of data. How you do this depends on what you want to do with the data.
- If you are adding data to ArcMap or ArcGIS Pro for viewing and analysis within the map, add a query layer and define the query layer expression to return only a subset of the data.
- If you are adding data to ArcMap to publish a map service or to ArcGIS Pro to publish a map image layer that references registered data, define a database view that contains only a subset of the data. Database views are stored in the database. You can use the Create Database View geoprocessing tool or an SQL client to define views.
- If you are adding data to ArcMap to publish a feature service or to publish to a portal, uncheck the option to Make newly added layers visible by default before adding your data to the map. Next, open the Layer Properties and create a Definition Query that defines a subset of data using SQL. Once you have restricted the amount of data that displays in the map, make the layer visible and prepare your map for publishing.
- If you are adding data to ArcGIS Pro to publish a feature layer that references registered data or to publish to a portal, uncheck the option to Make newly added layers visible by default before adding your data to the map. Next, create a Definition Query for the layer that defines a subset of data using SQL. Once you have restricted the amount of data that displays in the map, make the layer visible and prepare your map for publishing.
Caution:
Unless your map is set so that data is not automatically displayed when added, do not drag data directly from your database connection to the map if you have not already defined a subset of data. Extremely large datasets, as are normally stored in a data warehouse appliance, would take an inordinate amount of time to display on the map.
There are two ArcGIS-specific limitations when using a Netezza Data Warehouse Appliance:
- If you use the legacy Netezza spatial type, the spatial columns in your tables must be named shape or aliased to shape.
The legacy spatial type uses the varchar data type; therefore, the shape column name or alias is how ArcGIS identifies that the column stores spatial data rather than text. If the spatial column in your table has a different name, create a view on the table and alias the spatial column name to shape.
- ArcGIS requires a unique identification field to render features in a map. ArcMap and ArcGIS Pro prompt you to specify this unique ID field when you add a spatial table to the map.
ArcGIS tools do not insert new values into the unique ID fields in a database; rather, they rely on the database to insert values to the field. Since Netezza does not have a data type or built-in mechanism to populate values in an ID field, ArcGIS tools that create new rows in a database table cannot be used with Netezza. Also, since you cannot insert features to a Netezza table from ArcGIS, you cannot publish a feature service that contains data from a Netezza Data Warehouse Appliance.
Other limitations are related to Netezza itself.
- Since Netezza spatial packages use different data types, which spatial package you use affects how you configure the Netezza ODBC driver to connect to Netezza from client applications.
- As a Netezza user, you know that Netezza Spatial stores simple data. Therefore, you cannot paste nonsimple data from a geodatabase or another database into Netezza.
- As a Netezza user, you also know that rows are limited to a total size of 65,535 bytes. To help you calculate row sizes, the following table shows the Netezza data types that ArcGIS creates in Netezza and the amount of disk space the data types use:
| ArcGIS field type | Netezza data type created | Disk usage |
|---|---|---|
DATE | timestamp | 8 bytes |
DOUBLE | numeric(p,s) By default, p (precision) = 38 and s (scale) = 8. | Precision of 9 or less = 4 bytes Precision of 10–18 = 8 bytes Precision of 19–38 = 16 bytes |
FLOAT | If precision of 6 or less is specified, numeric(p,s) is created. If precision 7–15 is specified, double is created. | numeric = 4 bytes double = 8 bytes |
GEOMETRY | If you use the legacy Netezza Spatial Package, a variable length character (up to 64,000) column is created. If you use the Netezza Spatial Esri Package, an ST_Geometry column is created. | For the legacy Netezza Spatial Package
For the Netezza Spatial Esri Package
Note:If the size of any individual geometry exceeds the size of the field when data is inserted to the feature class, a null geometry is inserted. For example, if you copy a polygon feature class from another data source and paste it into your Netezza database, the table gets created with a geometry field (maximum size of 64,000 bytes). Next, the records are inserted into the table. If any of the geometries being inserted exceed 64,000 bytes, the record and all the other attributes are inserted, but a null is inserted into the geometry field. |
GUID | Fixed-length character(38) | 40 bytes |
LONG INTEGER | If scale is greater than 0, numeric(p,s) is created. If scale is 0, an integer is created. | integer = 4 bytes numeric with precision of 9 or less = 4 bytes numeric with precision of 10–18 = 8 bytes numeric with precision of 19–38 = 16 bytes |
OBJECTID | integer | 4 bytes |
SHORT INTEGER | smallint | 2 bytes |
TEXT | Variable length, Unicode(p) P is the field length you specify for the text field. The default value is 50. | p x 4 = number of bytes used |
See what versions of Netezza Data Warehouse Appliance are supported with ArcGIS 10.7.1.
Microsoft Azure databases
If you deploy ArcGIS on Microsoft Azure, you can use any of the following Microsoft Azure cloud-based database services to store your data:
- Microsoft Azure SQL Database
- Microsoft Azure Database for PostgreSQL
When possible, you should run your ArcGIS clients on Microsoft Azure and use the same location (region) as your Azure databases for better performance.
Tip:
Esri provides a Microsoft Azure image that includes ArcGIS Enterprise. You can download the ArcGIS Enterprise Cloud Builder for Microsoft Azure from My Esri, and use it to deploy ArcGIS Enterprise on Microsoft Azure. You can optionally register an Azure database with ArcGIS Server sites in an Enterprise deployment. See ArcGIS Enterprise on Microsoft Azure help for more information.
See what versions of Azure database services are supported with ArcGIS 10.7.1.
Azure SQL Database
You can use a single database or Managed Instance of Azure SQL Database with ArcGIS. To connect to these databases, install a supported Microsoft ODBC driver for SQL Server on the ArcGIS client machine.
Keep the following in mind when working with Azure SQL Database from ArcGIS:
- Because Azure SQL Database and SQL Server implementations are so similar, there is not a separate Azure SQL Database option in the Database Connection dialog box or the Create Database Connection geoprocessing tool. Specify SQL Server when connecting to Azure SQL Database, and follow the instructions for creating a connection from ArcGIS to SQL Server.
- You must type the database name In the Database Connection dialog box. You cannot choose the database from a drop-down list, and you cannot leave the Database text box blank.
- When loading large amounts of data to a feature class (several million records or more), you should estimate the spatial (x,y) extent of the data and create an appropriate spatial index on the feature class before loading the data. The operation of creating a spatial index on a feature class containing millions of records could be terminated if Microsoft Azure determines that the operation is consuming too many server resources. This can leave your feature class with no spatial index. Therefore, Esri recommends you create an appropriate spatial index first, and then load data.
- There are some tasks separate from ArcGIS that you perform directly in the database management system. If you are familiar with using SQL Server, there are some differences you should be aware of if you intend to use Azure SQL Database instead. See the Microsoft TechNet article, Feature comparison: Azure SQL Database versus SQL Server for information on these differences.
Azure Database for PostgreSQL
When you use an Azure Database for PostgreSQL (whether it contains a geodatabase or not), spatial data is always stored using a PostGIS spatial data type. You cannot install or use ST_Geometry in these databases.
Microsoft SQL Server
Although Microsoft allows SQL Server user accounts to write data to schemas of any name, ArcGIS requires that user accounts that create tables, views, or other objects in the database have identical user names and schema names. For example, you cannot connect from ArcGIS as elmer and write to a schema named dataowner.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your SQL Server database.
See what versions of SQL Server are supported with ArcGIS 10.7.1.
Oracle
ArcGIS supports Oracle SDO_Geometry and Esri ST_Geometry spatial types in Oracle databases. SDO_Geometry is a separate, third-party installation. If you want to use the Esri ST_Geometry type to store spatial data, copy the ST_Geometry shape library to your Oracle machine and run the Create Spatial Type geoprocessing tool to create the ST_Geometry type, subtypes, and functions.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your Oracle database.
See what versions of Oracle are supported with ArcGIS 10.7.1.
PostgreSQL
ArcGIS supports PostGIS geometry and geography types and Esri ST_Geometry spatial types in PostgreSQL databases. PostGIS is a separate, third-party installation. If you want to use the Esri ST_Geometry type to store spatial data, copy the st_geometry library into your PostgreSQL installation and run the Create Spatial Type geoprocessing tool to create the ST_Geometry type, subtypes, views, and functions.
Although PostgreSQL allows user accounts to write data to schemas of any name, ArcGIS requires that user accounts that create tables, views, or other objects in the database have identical user names and schema names. For example, you cannot connect from ArcGIS as vlad and write to the public schema.
If you require functionality such as attachments, archiving, topology, geodatabase versions for simultaneous editing, or the ability to replicate data, you can enable a geodatabase in your PostgreSQL database.
See what versions of PostgreSQL and PostGIS are supported with ArcGIS 10.7.1.
SAP HANA
Keep the following information in mind when using SAP HANA:
- SAP HANA uses columnar data storage, which behaves as its own index. Therefore, the spatial columns in SAP HANA tables do not require a spatial index for query performance.
- Since SAP HANA does not utilize user-defined indexes, you do not manage or rebuild indexes on tables in SAP HANA.
- When you load data into SAP HANA, the spatial reference used by the data must exist in the SAP HANA system table.
- SAP HANA redefined their spatial reference system EPSG ID 4326 from a planar representation of WGS84 to a round-earth representation. Tables that store spatial data in SAP HANA with spatial reference system 4326 may not behave as expected in ArcGIS.
The round-earth implementation of WGS84 (EPSG ID: 4326) in SAP HANA contains key limitations that makes it unsuitable when working with certain types of data. For example, global datasets that exceed hemisphere boundaries or cross-hemisphere boundary lines return inconsistent results. Data you create or load using ArcGIS continues to be stored using a planar version of the WGS84 (EPSG ID 1000004326) coordinate system to ensure that ArcGIS can work with all types of data. However, ArcGIS can read round-earth WGS data that you create in SAP HANA using SQL or a third-party product.
- SAP HANA stores spatial reference system information in their ST_SPATIAL_REFERENCE_SYSTEMS system view. Spatial data you load into SAP HANA must either have a null spatial reference, or use a spatial reference that exists in the ST_SPATIAL_REFERENCE_SYSTEMS system view. If this view does not contain the spatial reference system for data you want to load, use the SAP HANA CREATE SPATIAL REFERENCE SYSTEM Statement to update the view with the spatial reference system you need.
Tip:
More information can be found in the CREATE SPATIAL REFERENCE SYSTEM page of the SAP HANA Spatial Reference documentation.
If the spatial data you import or copy to your SAP HANA database does not have a spatial reference defined, SAP HANA assigns a default SRID of 0, a Cartesian spatial reference system that works with data on a flat 2-dimensional plane.
See what versions of SAP HANA are supported with ArcGIS 10.7.1.
SQLite
ArcGIS supports SQLite databases with the following file extensions and that contain one of the following storage types:
- Esri ST_Geometry—The database file must have the extension .sqlite.
- SpatiaLite—The database file must have the extension .sqlite.
- GeoPackages—The GeoPackage file must have the extension .gpkg.
You can use only one spatial storage type per SQLite database. See Spatially enable an SQLite database for information on adding a spatial storage type to an existing database.
To create an SQLite database that uses either ST_Geometry or SpatiaLite storage, or to create a GeoPackage, use the Create SQLite Database geoprocessing tool.
See what versions of SQLite are supported with ArcGIS 10.7.1.
Data access
You control access to an SQLite database or GeoPackage by controlling permissions on the folder where it is stored. Unlike other databases, you do not create users who are authenticated by the database or grant privileges on specific datasets to other users.
SQLite and GeoPackages can be read by multiple users, but do not update the database or any of its contents while someone else is editing data or updating the database. For example, do not append data into an existing table or create a table in the database while someone else is loading data or creating a table in the database.
Data types
SQLite is also different from other databases in that fields are not assigned specific data types and data type definitions are not strictly enforced. Instead, SQLite uses storage classes in which values of different data types can be stored.
ArcGIS, however, can only work with one data type per field and strictly enforces data types. You should be aware of this difference in data type enforcement when viewing SQLite data in ArcGIS.
The following example creates a table with integer and text fields:
CREATE TABLE mytable (
id INTEGER PRIMARY KEY NOT NULL,
item TEXT,
weight INTEGER,
store TEXT;Even though the weight field is defined as an integer, SQLite will allow you to store numbers with decimals in it. It will even allow you to store text in it. For example, you can insert the following records:
INSERT INTO mytable (id, item, weight, store) VALUES(
1,
"magnetic dual elliptical trainer with seat",
75,
"CardioPlus Equipment"
);
INSERT INTO mytable (id, item, weight, store) VALUES(
2,
"superfit treadmill4000",
81.2,
"Sports Pit"
);
INSERT INTO mytable (id, item, weight, store) VALUES(
3,
"serenity yoga mat",
.4588,
"Aerobic Angels Sporting Goods"
);
INSERT INTO mytable (id, item, weight, store) VALUES(
4,
"swim fins",
"two",
"The Plunge"
);However, the values appear as follows in ArcGIS because the weight field is defined as integer:
| id | item | weight | store |
|---|---|---|---|
1 | magnetic dual elliptical trainer with seat | 75 | CardioPlus Equipment |
2 | superfit treadmill4000 | 81 | Sports Pit |
3 | serenity yoga mat | 0 | Aerobic Angels Sporting Goods |
4 | swim fins | 0 | The Plunge |
See DBMS data types supported in ArcGIS for a list of which SQLite data types map to which ArcGIS data types.
Teradata Data Warehouse Appliance
To connect from ArcGIS to a Teradata Data Warehouse Appliance, you must install the Teradata GSS client, ICU library, and ODBC driver on the ArcGIS client machines in a specific order. See Connect to Teradata for information.
The following is a list of guidelines for using a Teradata Data Warehouse Appliance with ArcGIS:
- The spatial columns in your tables must
be named shape or aliased to shape in a view.
The underlying storage for the TeradataST_Geometry type is CLOB; therefore, the name or alias of the column (shape) is the only way ArcGIS can identify that the column stores spatial data.
- You must use the tessellation functions provided by Teradata to create and maintain a spatial index on a Teradata ST_Geometry column.
Teradata uses the tessellation spatial index with a predefined area of interest. Therefore, ArcGIS spatial operations that are based on dynamic inputs, such as zoom in, zoom out, and pan, cannot use the tessellation index. However, the tessellation index can be used in a query layer if the SQL statement used to define the query layer explicitly uses the tessellation index to query a subset of data based on an area of interest.
- ArcGIS requires a unique identification field to render features in a map and to publish web services. ArcMap prompts you to specify this unique ID field when you add a spatial table to the map.ArcMap and ArcGIS Pro prompt you to specify this unique ID field when you add a spatial table to the map.
ArcGIS tools do not insert new values into the unique ID fields in a database; rather, they rely on the database to insert values to the field. Since Teradata does not have a data type or built-in mechanism to populate values in an ID field, ArcGIS tools that create new rows in a database table cannot be used with Teradata.
- As a Teradata user, you know that Teradata 14.x ST_Geometry only supports two-dimensional spatial data; therefore, you cannot paste spatial data that contains z- or m-coordinates into Teradata 14.x. Teradata 15.0 ST_Geometry supports z-coordinates but not m-coordinates; therefore, you cannot paste spatial data that contains m-coordinates into Teradata 15.0.
- In most cases, you store
large amounts of data in a Teradata Data Warehouse Appliance. To visualize, perform analyses, or publish data from an ArcGIS client, use a subset of data. How you do this depends on what you want to do with the data.
- If you are adding data to ArcMap or ArcGIS Pro for viewing and analysis within the map, add a query layer and define the query layer expression to return only a subset of the data.
- If you are adding data to ArcMap to publish a map service or to ArcGIS Pro to publish a map image layer that references registered data, define a database view that contains only a subset of the data. Database views are stored in the database. You can use the Create Database View geoprocessing tool or an SQL client to define views.
- If you are adding data to ArcMap to publish a feature service or to publish to a portal, uncheck the option to Make newly added layers visible by default before adding your data to the map. Next, open the Layer Properties and create a Definition Query that defines a subset of data using SQL. Once you have restricted the amount of data that displays in the map, make the layer visible and prepare your map for publishing.
- If you are adding data to ArcGIS Pro to publish a feature layer that references registered data or to publish to a portal, uncheck the option to Make newly added layers visible by default before adding your data to the map. Next, create a Definition Query for the layer that defines a subset of data using SQL. Once you have restricted the amount of data that displays in the map, make the layer visible and prepare your map for publishing.
Caution:
Unless your map is set so that data is not automatically displayed when added, do not drag data directly from your database connection to the map if you have not already defined a subset of data. Extremely large datasets, as are normally stored in a data warehouse appliance, would take an inordinate amount of time to display on the map.
- If you use ArcGIS to add a feature class to your Teradata database and the spatial reference you specify for the feature class is not in the Teradata system table, ArcGIS adds a record to the Teradata SPATIAL_REF_SYS table and, consequently, the GEOMETRY_COLUMNS table.
This allows you to use more spatial references than are provided by default with Teradata. However, upgrades to major Teradata releases may drop these tables and re-create them with only default values in them.
Before you upgrade Teradata, you should always make a backup of your database. Be sure that this includes the records in these tables so you can restore them, if necessary, after a Teradata upgrade. You can follow this general procedure to back up and restore the SPATIAL_REF_SYS and GEOMETRY_COLUMNS records during a database upgrade operation:
- Create a backup of your database.
- Connect to a database in which you will make copies of the SPATIAL_REF_SYS and GEOMETRY_COLUMNS tables.
In this example, the database is named mybackupdb.
database mybackupdb; - Create backup copies of your tables.
In this example, source tables are in the sysspatial database.
CREATE TABLE mybackupdb.spatial_ref_sys AS sysspatial.spatial_ref_sys WITH DATA; CREATE TABLE mybackupdb.geometry_columns AS sysspatial.geometry_columns WITH DATA; - Upgrade the Teradata source database.
- Connect to the database to which you copied the tables.
- Restore the tables to your upgraded database.
--Restore the geometry columns table. INSERT INTO sysspatial.geometry_columns SELECT * FROM mybackupdb.geometry_columns; --Restore the spatial_ref_sys table. INSERT INTO sysspatial.spatial_ref_sys SELECT * FROM mybackupdb.spatial_ref_sys MINUS SELECT * FROM sysspatial.spatial_ref_sys; - Once the contents of the tables are restored, you can delete the backup copies of the tables.
DROP TABLE mybackupdb.spatial_ref_sys; DROP TABLE mybackupdb.geometry_columns; - If necessary, regrant privileges on these tables to users who create data in the database.