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.

CautionCaution:

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

NoteNote:

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

Related Topics

2/5/2015