Netezza and ArcGIS

If you are using a Netezza data warehouse appliance, you can connect to your database from ArcGIS to perform complex spatial analysis on subsets of your data.

To use ArcGIS with Netezza, you must install a supported version of the Netezza Spatial Package in the database management system, and install and configure the Netezza ODBC driver on the ArcGIS client machine from which you will make a database connection. See the IBM Netezza Data Warehouse Appliance requirements for ArcGIS for a list of supported Netezza data warehouse, spatial package, and ODBC drivers.

Working with data

In most cases, you will have very large amounts of data stored in the data warehouse appliance. To perform analyses from ArcGIS, you would use a subset that contains only the data that you are interested in analyzing. You can define that subset in a query layer definition or you can create a database view that returns only the data subset.

If you want to use query layer definitions, create the definition files in ArcMap. Click File > Add Data > Add Query Layer to open the New Query Layer dialog box, connect to your database, then type the SQL statement to define the data subset.

Caution:

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.

If you want to use database views, you can use the Create Database View geoprocessing tool or SQL to define the data subsets you want to analyze.

Guidelines for Netezza data used in ArcGIS

There are two ArcGIS-specific limitations for using Netezza:

• If you are using 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.

• ArcGIS requires a unique identification field to render features in a map. When you add a spatial table to the map, you are prompted to specify this unique ID field.

  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.

The other limitations are related to Netezza itself. As a Netezza user, you know that Netezza Spatial stores simple data. Therefore, you cannot paste nonsimple data from 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 are created when you use ArcGIS to create a table or feature class in Netezza, and the amount of disk space they 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: Point geometries are created as character varying(80). The base size for all other geometries is 64,000 bytes. However, if creating a field of this size would cause the total row size to exceed 65,535 bytes, the a varchar field will be created with a smaller precision. For example, if the total size of all other fields in the row is 2,000 bytes, the geometry field will be created as character varying(63,535). For the Netezza Spatial Esri Package: Point geometries are created as ST_Geometry(140). The base size for all other geometries is 64,000 bytes. However, if creating a field of this size would cause the total row size to exceed 65,535 bytes, the ST_Geometry field will be created with a smaller precision. For example, if the total size of all other fields in the row is 2,000 bytes, the geometry field will be created as ST_Geometry(63,535). 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 to 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