Preparing to use query layers

To create a query layer on a spatial database, you must complete specific tasks to ensure the query layer works with ArcGIS.

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:

IBM DB2 databases

To use a query layer to display spatial data from a DB2 database, the database must be registered with the DB2 Spatial Extender, and the computer from which you will connect to the database must have the IBM Data Server Runtime Client for DB2 installed on it.

DB2 Spatial Extender

The DB2 database must be registered with DB2 Spatial Extender before you can use the ST_Geometry spatial type. Contact your GIS administrator or database administrator if you are not sure the database you are connecting to is enabled for ST_Geometry.

IBM Data Server Runtime Client for DB2

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 the Esri Customer Care portal 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 are connecting 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 the Esri Customer Care portal 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 the Esri Customer Care portal 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.

Which 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 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 Configuring the Oracle listener to use SQL 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

On your client computer install the Oracle Net application. You can obtain the Oracle Instant Client from the Esri Customer Care portal or get the Instant or full 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 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 must be 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

Place the PostgreSQL libpq files in the ArcGIS bin folder on your client computer. The libpq files can be downloaded from the Esri Customer Care portal. See Setting up a connection to PostgreSQL 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.

Related Topics

3/5/2014