To create a query layer on a spatial database, you must complete the following specific tasks to ensure the query layer works with ArcGIS:
- You must use a release of the database management system that is supported by the ArcGIS version you are using. Check the database system requirements on the ArcGIS Resources website for information on database management system support.
- The query layer definition must include a unique, not-null column or combination of columns that can be used as the ObjectID in the query layer. If the data you want to use does not have such a column or combination of columns, you must add one. See Choosing a unique identifier field for more information.
- Depending on the type of database management system you are using, there is some configuration you must do in the database management system to use spatial types.
- You must install and configure database management system clients on each of the client computers from which you will create query layer connections. You can download a supported client for DB2, Informix, PostgreSQL, and SQL Server from My Esri. Since you must be an administrator on your computer to install software, contact your information systems department to set up your computer for query layer connections if your login is not a member of the Windows Administrator group on your computer.
Some of these tasks vary depending on the type of database management system and the SQL spatial type you want to use. See the following sections for information specific to your database management system:
ALTIBASE
Although ALTIBASE comes with a spatial type, you must create two system tables to use the spatial data in ALTIBASE. See Use ALTIBASE with ArcGIS for instructions on creating these system tables.
To connect to ALTIBASE from ArcGIS, you must install the ALTIBASE client on the ArcGIS client machine. See Connect to ALTIBASE from ArcGIS for more information.
IBM DB2 databases
To use a query layer to display spatial data from a DB2 database, the computer from which you will connect to the database must have the IBM Data Server Runtime Client for DB2 installed on it.
Install the DB2 client application on your client computers. You can configure the clients to connect to a specific DB2 database or use a Data Source Name-less (DSN-less) connection string when connecting from ArcGIS. You can download the IBM Data Server Runtime Client for DB2 from My Esri or obtain it from IBM.
See the IBM DB2 documentation for information on how to install and configure a DB2 client.
IBM Informix databases
To use a query layer to display spatial data from an Informix database, the database must be registered with the Informix Spatial DataBlade, and the computer from which you will connect to the database must have the Informix IConnect client installed and configured on it.
Informix Spatial DataBlade
The Informix database you want to use must be registered with Informix Spatial DataBlade before you can use the ST_Geometry spatial type. Contact your GIS administrator or database administrator if you are not sure if the database you connect to is enabled for ST_Geometry.
Informix IConnect
Install on your client computer the Informix IConnect application and configure the Setnet32 application. Unless you will be using a DSN-less connection string to connect to Informix, you must configure an Open Database Connectivity (ODBC) connection.
You can download the Informix client application from My Esri or obtain it from IBM. See the IBM Informix documentation for information on installing and configuring the Informix client.
Microsoft SQL Server databases
Two spatial types are supported in SQL Server that you can use in your query layers: Geometry and Geography. These are present automatically in SQL Server databases; you do not have to install them.
To use a query layer to display spatial data from a SQL Server database, you must install a supported version of SQL Server Native Client on your client computer. You can obtain the SQL Server Native Client from My Esri or Microsoft.
See the Microsoft SQL Server documentation for installing the SQL Server Native Client.
Netezza data warehouse appliances
To use a query layer to display spatial data from a Netezza database, you must install a Netezza spatial package. Also, you must install the Netezza ODBC driver on your ArcGIS client computer and configure a data source name.
Netezza spatial package
There are two packages you can use with your Netezza database: the legacy Netezza Spatial Package and the Netezza Spatial Esri Package.
If you use the legacy Netezza Spatial Package, ArcGIS requires that the spatial column be named shape. If the name of the column is not shape, create a view on the table and alias the spatial column name to shape.
The spatial package you use affects how you configure the Netezza ODBC driver.
Netezza ODBC driver
You must install the Netezza ODBC driver and configure a data source name. You can obtain the Netezza driver from IBM. See the documentation provided by Netezza for information on installing and configuring the ODBC driver.
If you use the legacy Netezza spatial package, you must set the option to Optimize for ASCII character set when setting up the ODBC data source name.
If you use the Netezza Spatial Esri Package, do not set the option to Optimize for ASCII character set when configuring the ODBC data source name.
Oracle databases
To use a query layer to display spatial data from an Oracle database, a spatial type must be present in the database. Also, the computer from which you will connect to the database must have the Oracle client installed and configured on it.
Spatial types in Oracle
Two spatial types are supported in Oracle that you can use in your query layers: ST_Geometry and SDO_Geometry.
The ST_Geometry spatial type is installed when you create an enterprise geodatabase in Oracle, or it can be installed in an Oracle database using the Create Spatial Type geoprocessing tool. You also need to configure the Oracle EXTPROC to use ST_Geometry. Your GIS administrator or database administrator can tell you if your database has been configured to use this spatial type. For information on configuring the EXTPROC, see Configure the Oracle extproc and your Oracle documentation.
Oracle Locator objects, including the SDO_Geometry type, are present by default in Oracle databases. Therefore, you can access the SDO_Geometry type without additional installations, but to access all SDO_Geometry functions, you might need to install Oracle Spatial in the database.
Oracle client
Install the Oracle Net application on your client computer. You can obtain the Oracle Instant, Runtime, or Administrator Client from Oracle.
For information on installing and configuring the Oracle client, see Oracle's documentation set.
PostgreSQL databases
To use a query layer to display spatial data from a PostgreSQL database, a spatial type must be present in the database. Also, the ArcGIS client from which you will connect to the database must have the PostgreSQL libpq files placed in its bin directory.
Spatial types in PostgreSQL
Two spatial types are supported in PostgreSQL that you can use in your query layers: ST_Geometry and PostGIS geometry.
The ST_Geometry type is installed when you create an enterprise geodatabase in PostgreSQL, or it can be installed in a PostgreSQL database using the Create Spatial Type geoprocessing tool.
The PostGIS geometry type must be installed separately on the PostgreSQL database server. Once installed, the database you use must be created using the PostGIS template database or configured to use PostGIS so that it is enabled to store the PostGIS geometry type.
Contact your GIS administrator or database administrator to determine whether your database has been configured to use either of these spatial types.
PostgreSQL libraries
ArcGIS for Desktop, ArcGIS for Server, and ArcGIS Pro contain the libraries needed to connect to PostgreSQL. To connect from an ArcGIS Engine application, dowload the libpq files from My Esri and place them in the bin folder of the ArcGIS Engine client.
SAP HANA data warehouse appliances
Spatial data is stored in the SAP HANA ST_Geometry type, which is included in the default SAP HANA data warehouse installation. To use ArcGIS with SAP HANA, install and configure the SAP HANA ODBC driver on the ArcGIS client machines that will connect to SAP HANA. See Connect to SASP HANA from ArcGIS for more information.
Teradata databases
To use a query layer to display spatial data from a Teradata database, the computer from which you will connect to the database must have the Teradata GSS client, ICU library, and ODBC driver installed on it and a data source configured.
Install the Teradata ICU library, GSS client, and ODBC driver on your ArcGIS client computer (in that order). You can obtain these files from Teradata.
Next, configure a data source name for the Teradata database.
See the documentation provided by Teradata for information on installing the client files and configuring a data source name. Also see Setting up a connection to Teradata for specific options that must be set for using Teradata with ArcGIS.
ArcGIS requires that the spatial column in a Teradata feature class be named shape. If the name of the column is not shape, create a view on the table and alias the spatial column name to shape.