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 (DBMS) and the SQL spatial type you want to use.
- You must use a release of the DBMS that is supported by the ArcGIS version you are using. Check the ArcGIS Resource Center for DBMS support information for ArcGIS Server.
- The query layer definition must include a unique, not null column or combination of columns that can be used as the object ID 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 DBMS you are using, there may be some configuration you must do in the DBMS to use spatial types.
- Also depending on the type of DBMS, you may need to do some configuration on each of the client computers from which you will create query layer connections.
IBM DB2 databases
The DB2 database must be registered with the DB2 Spatial Extender before you can use the ST_Geometry spatial type. Contact your GIS administrator or database administrator (dba) if you are not sure if the database you are connecting to is enabled for ST_Geometry.
You must install the DB2 client application on the computers that will connect to the database, and the client must be configured to connect to a specific DB2 database. Use the DB2 Configuration Assistant to configure the client to a DB2 instance.
See the IBM documentation site (http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?) for information on how to register the DB2 database with the Spatial Extender and install and configure a DB2 client.
IBM Informix databases
The Informix database you want to use must be registered with the Informix Spatial DataBlade before you can use the ST_Geometry spatial type. Contact your GIS administrator or DBA if you are not sure if the database you are connecting to is enabled for ST_Geometry.
You must also install the Informix client SDK or IConnect application and configure an ODBC connection. 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.
See the IBM online documentation for information on installing the Informix client. See Setting up the Informix Setnet 32 application and Setting up an ODBC data source for Informix on Windows for information on setting up an ODBC connection.
Microsoft SQL Server databases
If you are creating query layers that include a spatial column, you must connect to a SQL Server 2008 (or higher) database and create query layers on tables that contain Microsoft Geometry and Geography columns. If the query layer you create only includes nonspatial columns, you can connect to a SQL Server 2005 database as well.
ArcGIS Desktop automatically installs the SQL Server Native Client on your computer. This allows you to make a query layer connection.
Oracle databases
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 with ArcSDE for Oracle. Therefore, to make a query layer that contains a column with this spatial type, you must install ArcSDE and use a geodatabase. You also need to configure the Oracle EXTPROC to use ST_Geometry. Your GIS administrator or DBA 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. However, to access all SDO_Geometry functions, you may need to install Oracle Spatial in the DBMS.
All clients connecting to an Oracle database to create query layers must have the Oracle Net application installed and configured. If you are not an administrator on your computer, contact your information systems department to have the Oracle client installed and configured on your computer.
For information on installing and configuring the Oracle client, see Oracle's documentation set.
PostgreSQL databases
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 with ArcSDE for PostgreSQL. Therefore, to make a query layer that contains a column with this spatial type, you must install ArcSDE and create a geodatabase.
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 so that it is enabled to store the PostGIS geometry type.
Contact your GIS administrator or DBA to determine whether your database has been configured to use either of these spatial types.