Teradata and ArcGIS

Use data subsets

In most cases, you store large amounts of data in Teradata. To perform analyses from ArcGIS or publish data, 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 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, 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, uncheck the option to Make newly added layers visible by default before adding your data to the map. Next, open the Query Builder dialog box and define a subset of data using SQL. Once you have restricted the amount of data that will display in ArcMap, 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 in the Catalog window in ArcMap 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.

Additional guidelines for using Teradata data in ArcGIS

The following is a list of guidelines for using Teradata with ArcGIS:

• The spatial columns in your tables must be named shape or aliased to shape in a view.

  The underlying storage for the Teradata ST_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.

  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.
• 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:

  1. Create a backup of your database.
  2. 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;
     

  3. 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;
     

  4. Upgrade the Teradata source database.
  5. Connect to the database to which you copied the tables.
  6. 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;
     

  7. 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;
     

  8. If necessary, regrant privileges on these tables to users who create data in the database.

Related Topics