当ArcGIS遇到Netezza

如果对Netezza不是很了解，可以先看一下

当ArcGIS遇到Netezza——初识Netezza

从ArcGIS10.1就支持了连接Netezza数据，在ArcGIS10.2新特性又说明了ArcGIS10.2支持Netezza7.0已经INZA2.5，首先我们还是看看相关的系统需求

http://resources.arcgis.com/en/help/system-requirements/10.2/index.html#/Netezza_Data_Warehouse_Appliance_Requirements/01510000007n000000/

从系统需求我们可以看出：

• See Setting up a connection to Netezza for important information about configuring ODBC for Netezza.
• ArcGIS Desktop需要使用安装Netezza的ODBC驱动
• Required add-ons for Netezza 6.0: Netezza Spatial Package version 1.1.2 or higher.
• 需要安装Netezza的空间包
• Geodatabase functionality cannot be enabled in a Netezza database.
• Netezza不支持ArcGIS的Geodatabase
• If using the legacy Netezza Spatial Package, all spatial columns must be named, or aliased, to SHAPE for ArcGIS to recognize it as a spatial column in the table.
• 如果使用Netezza的空间包，所有的空间字段名字或者别名必须是SHAPE
• Feature services are not supported with Netezza.
• Netezza不支持要素服务

说明：

1：关于Netezza ODBC驱动，有32Bit和64BitODBC，那么大家都只到ArcGIS10.2 Desktop 是32Bit的，ArcGIS Server10.2 是64Bit的

2：Netezza提供了两种空间包（Spatial Netezza Package和Spatial Netezza Esri Package）

参考：

Is ArcGIS 10.1 certified to support IBM Netezza 7.0 and INZA 2.5 including the Netezza Spatial Esri Package?
http://support.esri.com/en/knowledgebase/techarticles/detail/40616 

首先看看，怎么来查看这两个版本

[nz@netezza ~]$ nzrev
Release 6.1, Dev 2 [Build 16340]
[nz@netezza ~]$ nzsql -d inza
Welcome to nzsql, the Netezza SQL interactive terminal.

Type:  \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit


INZA(ADMIN)=> select * from product;
         NAME          | SHORTNAME |   VERSION   | INSTALLED
-----------------------+-----------+-------------+------------
 IBM Netezza Analytics | INZA      | 2.0.3.25221 | 2013-08-01
(1 row)

首先来安装Netezza的ODBC程序，需要配置32Bit的ODBC：C:\Windows\System32\odbcad32.exe，需要配置ODBC，添加Netezza程序

说明：

如果使用Netezza  Spatial Package 需要勾选Optimize for ASCII character set

如果使用Netezza Spatial Esri Package 不需要勾选Optimize for ASCII character set

如果需要配置Linux或者ArcGISServer的话可以参考： http://resources.arcgis.com/en/help/main/10.2/index.html#/Set_up_a_connection_to_Netezza/019v00000015000000/

其他说明：

NPS：这是Netezza PureData System,其实就是Netazza数据库服务器

INZA：这是IBM NetaZza Analytics的缩写，这里面默认有两个表Geometry_columns和Spatial_ref_sys

查看一下相关的结构

INZA(ADMIN)=> \d geometry_columns
                            Table "GEOMETRY_COLUMNS"
     Attribute     |              Type               | Modifier | Default Value
-------------------+---------------------------------+----------+---------------
 F_TABLE_CATALOG   | NATIONAL CHARACTER VARYING(256) | NOT NULL |
 F_TABLE_SCHEMA    | NATIONAL CHARACTER VARYING(256) | NOT NULL |
 F_TABLE_NAME      | NATIONAL CHARACTER VARYING(256) | NOT NULL |
 F_GEOMETRY_COLUMN | NATIONAL CHARACTER VARYING(256) | NOT NULL |
 GEOMETRY_TYPE     | INTEGER                         |          |
 COORD_DIMENSION   | INTEGER                         |          |
 SRID              | INTEGER                         |          |
 DISSOLVE_COLUMN   | NATIONAL CHARACTER VARYING(256) |          |
Distributed on hash: "F_TABLE_CATALOG"

INZA(ADMIN)=> \d spatial_ref_sys
                    Table "SPATIAL_REF_SYS"
 Attribute |          Type           | Modifier | Default Value
-----------+-------------------------+----------+---------------
 SRID      | INTEGER                 | NOT NULL |
 AUTH_NAME | CHARACTER VARYING(256)  |          |
 AUTH_SRID | INTEGER                 |          |
 SRTEXT    | CHARACTER VARYING(2048) |          |
 PROJ4TEXT | CHARACTER VARYING(2048) |          |
Distributed on hash: "SRID"

spatial_ref_sys是一个投影系统表，这个在我们使用teradata、MongoDB都看到过，就是存储OpenGIS 、OGC标准的投影信息，那么geometry_columns就是存储经过ArcGIS导入到Netezza数据库里面的空间字段的注册，一般情况下Netezza只支持存储在Spatial_ref_sys里面的投影信息（3750），如果是自定义的投影，数据会导入进去，但是查看属性会提示这个投影信息在投影表里面查看不到的错误。

INZA(ADMIN)=> select count(*) from spatial_ref_sys;
 COUNT
-------
  3750
(1 row)

INZA(ADMIN)=> select * from spatial_ref_sys where srid=4326;
 SRID | AUTH_NAME | AUTH_SRID | SRTEXT    | PROJ4TEXT
------+-----------+-----------+---------------------------------------------------------------------------------------------------------------------------
 4326 | EPSG      |      4326 | GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]] | +proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs
(1 row)

INZA(ADMIN)=> select * from geometry_columns;
 F_TABLE_CATALOG | F_TABLE_SCHEMA | F_TABLE_NAME | F_GEOMETRY_COLUMN | GEOMETRY_TYPE | COORD_DIMENSION |  SRID  | DISSOLVE_COLUMN
-----------------+----------------+--------------+-------------------+---------------+-----------------+--------+-----------------
 INZA            | ADMIN          | POLY         | SHAPE             |            11 |               2 |   4326 |
 INZA            | ADMIN          | XZQ          | SHAPE             |            11 |               2 | 900914 |
 INZA            | ADMIN          | PT           | SHAPE             |             1 |               2 |   4326 |
(3 rows)

Netezza Spatial package包含大量的空间函数来处理空间数据，这个类似于Esri的ST_Geometry、Oracle的SDO_Geometry、PostGIS的Geometry、MS的Geometry和Geography提供的空间函数一样

空间函数包括

• 构造函数  ST_Point等
• 空间关系 ST_Within等
• 返回函数 ST_Buffer等

Netezza数据库不支持LOB（BLOB、CLOB、NCLOB、RAW），关于空间字段的数据存储以VARCHAR字段，新的NPS7.0支持ST_Geometry，

INZA(ADMIN)=> \d pt
                          Table "PT"
 Attribute |         Type          | Modifier | Default Value
-----------+-----------------------+----------+---------------
 OBJECTID  | INTEGER               | NOT NULL |
 SHAPE     | CHARACTER VARYING(80) |          |

Constraints:

Name                             Settings
PT_PK                            Primary key(OBJECTID)

Distributed on hash: "OBJECTID"

INZA(ADMIN)=> select * from pt;
 OBJECTID | SHAPE
----------+-------
        1 | g
(1 row)

同样，我们可以使用相关的函数对这些信息进行解析

INZA(ADMIN)=> select st_astext(shape) from pt;
      ST_ASTEXT
---------------------
 POINT (1.188 1.568)
(1 row)

关于这个空间字段我们可以参考：

If you use the legacy Netezza Spatial Package, a variable length, character varying(up to 64,000) 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).


If you use the Netezza Spatial Esri Package, an ST_Geometry column is created.
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).

我们看一个使用空间关系的例子

INZA(ADMIN)=> select st_contains(st_wkttosql('polygon((0 0,0 1,1 1,1 0,0 0))'),st_point(0.5 ,0.5));
 ST_CONTAINS
-------------
 t
(1 row)

我们来看看Netezza支持的空间数据量的情况

一般情况下，使用数据仓库来存储数据的级别都是TB级别的（矢量数据），如果以单个要素类的量一般都是一亿条记录以上

而且数据仓库的优势不再与存储这些数据，而是在这么大的数据量直接进行复杂的分析功能，这是其他普通关系型数据库特别是在关注效率层次上难以比肩的

这是使用Netezza数据库在某些海量数据和复杂查询分析的效率与普通数据库的对比

比如：我们国家的最远点是哪里？那个区域最难到达？我们的海岸上有多少海滩？这些问题需要大量的计算功率，但是借助于Netezza就非常容易了。

关于ArcGIS与Netezza的问题

1：为什么我使用ArcMap的Import工具将FGDB的要素类导入到Netezza中，看到的数据属性的Objectid都是一样的？如图所示

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

ArcGIS的数据要求一个唯一的标识字段。当你添加一个空间数据到地图中，系统会提示你指定此唯一ID字段。ArcGIS工具不会再数据库中为你的数据添加这个唯一值字段，它是依靠数据库的能力来产生这个唯一的ObjectID。任何GP工具（import工具）都需要产生这个ObjectiD，而且Import导入数据是需要对ObjectID的值进行重新排列，Netezza自身的原因不能提供这个能力，所以尽管你使用Import将一个有ObjectID字段而且是唯一值信息的数据导入到Netezza中，看到的结果永远是ObjectID是一个值的现象。


解决方法：使用ArcGIS的Copy/Paste的工具

2：Netezza和Teradata一样，作为数据仓库，数据量都是非常巨大的，大数据量，尽可能的不要再ArcMap中以可视化显示出来。

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.

3：ArcGIS For 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.

------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
------------------------------------------------------------------------------------------------------