Getting started with spatial analysis

To get started analyzing your spatial data, first load spatial data into a database.

Loading spatial data

Create a table with a geometry column as a VARCHAR column. For example, the following command creates a table that is suitable for loading point data:

CREATE TABLE PointData (PointID integer, the_geom varchar(200));

To insert a point to the table, issue commands similar to the following commands:

INSERT INTO PointData VALUES (1, inza..ST_Point(3423, 4356));
INSERT INTO PointData VALUES (1, inza..ST_WKTToSQL('Point (3423 4356)'));

Note that only one of these INSERT commands is needed to insert the point into the PointData table. Executing both INSERT commands results in two points with the same PointID. The column the_geom can be used anywhere that the datatype ST_Geometry (or the subclass ST_Point) is shown in the Spatial API functions.

The following command displays the geometry type of the data in the the_geom column:

SELECT inza..ST_GeometryType(the_geom) FROM PointData;

ST_GEOMETRYTYPE
---------------------
ST_Point
ST_Point

To create a table that contains polygons, issue the following command:

CREATE TABLE Polys (PolyID integer, the_geom varchar(200));

To add polygons to the table, issue commands similar to the following commands, which specify points that are the vertices of two square polygons:

INSERT INTO Polys VALUES (1, inza..ST_WKTToSQL('Polygon ((1000 1000, 1000 5000, 4000 5000, 4000 1000, 1000 1000))'));
INSERT INTO Polys VALUES (2, inza..ST_WKTToSQL('Polygon ((100 100, 100 500, 400 500, 400 100, 100 100))'));

With the polygons defined in the Polys table, you can issue basic point-in-polygon queries by joining through the ST_Intersects() function, as follows:

SELECT PointID, PolyID FROM PointData AS a, Polys AS b WHERE inza..ST_Intersects(a.the_geom, b.the_geom);

 POINTID | POLYID
---------+--------
       1 |      1
       1 |      1

Spatial data representation

When you create a geometric object or load existing spatial data into the database, the data is stored using an internal format. Consequently, if you use a standard SELECT * FROM <table> query to view spatial data, the data that is returned is unreadable. To display the data in a readable form, use the spatial conversion functions.

For example, the following commands create a small table named geoms and add a polygon and linestring object to the table:

CREATE TABLE geoms (PolyID INTEGER, the_geom VARCHAR(64000));
INSERT INTO geoms VALUES (1, inza..ST_WKTtoSQL('Polygon ((1 1, 1 4, 3.5 2.5, 6 4, 6 1, 1 1))'));
INSERT INTO geoms VALUES (2, inza..ST_WKTtoSQL('Linestring (1 1, 1 4, 6 4, 6 1)'));

To display the data in geoms in a readable form, use one of the spatial conversion functions:

To render the spatial data as text, use ST_AsText:

SELECT inza..ST_AsText(the_geom) FROM geoms;

ST_ASTEXT
---------------------------------------------------------------------
POLYGON ((1 1, 1 4, 3.5 2.5, 6 4, 6 1, 1 1))
LINESTRING (1 1, 1 4, 6 4, 6 1)

To render the spatial data in Keyhole Markup Language (KML), use ST_AsKML:

SELECT inza..ST_AsKML(the_geom) FROM geoms;
ST_ASKML 
---------------------------------------------------------------------
<LineString><coordinates>1,1 1,4 6,4 6,1</coordinates></LineString>
<Polygon><outerBoundaryIs><LinearRing><coordinates>1,1 1,4 3.5,2.5 6,4 6,1 1,1</coordinates></LinearRing></outerBoundaryIs></Polygon>

How spatial data is stored

The data type of the columns that store spatial data depends on the type of the cartridge being used:

Spatial: Spatial data is saved in a VARCHAR column.
Spatial ESRI: Spatial data is saved in a ST_GEOMETRY column.

Note: Although the spatial data is stored as character data, its internal format can become corrupted if you attempt to manipulate it using non-spatial string functions or operators.

Each column has a maximum size of 64,000 bytes, and a database row has a maximum size of 65,535 bytes. Although most geometries objects can be defined completely within a single 64,000-byte field, some, such as a polygon with many vertices or a linestring or multipoint with many points. might require more space.

Typically, a polygon that has a single ring (its outer ring) and that uses XY coordinates for vertices can have up to 3990 vertices before reaching the column size limit. The maximum number of vertices that can be in one column decreases if a polygon has interior rings (that is, holes), or if its vertices use XYZ, XYM, or XYZM coordinates.

Loading spatial data from other databases

When loading spatial data from other databases, it is possible that some geometries are too large to store in a single column. It is also possible that the data contains non-simple geometry types, or uses characters or other values that are not supported by the loading functions.

The Feature Manipulation Engine (FME) Workbench application, which is a product of Safe Software Inc., helps prepare data sets and load them into the Netezza Performance Server database. For example, FME Workbench can help you in the following ways:

It can detect polygons with greater than 3990 vertices and take a user-specified action, such as chopping the geometry into smaller geometries, skipping and ignoring the geometry, aborting the load process, or generalizing (or smoothing) the geometry to make it small enough to fit within the target field.
It can detect non-simple geometries, which are supported by the Spatial ESRI cartridge, but not by the Spatial cartridge. It can either filter them out or buffer them so that they later can be transformed into simple geometries.
It can load binary spatial data while detecting and escaping known binary values that are not supported by the loading functions.

For more information about FME Workbench application, refer to the documentation provided by Safe Software Inc.

Using spatial functions in user databases

When the Spatial or Spatial ESRI cartridge is installed, all spatial functions and stored procedures are registered in the INZA database by default. However, you can register the spatial functions and stored procedures also into user databases. This provides a way for you to have more than one version of the spatial functions available in a singles system, for example, for testing purposes.

To register spatial functions and stored procedures for use within a user database, carry out the following steps:

Create the new user database by entering the following command, where newdb represents the name of the user database that you want to create.
```
% nzsql -c "create database newdb;"
 CREATE DATABASE
```
Register nzspatial by entering the following command:
```
% nzcm -d newdb -r nzspatial
```
Run the create_inza_db.sh script by entering the following command:
```
% /nz/export/ae/utilities/bin/create_inza_db.sh newdb
```

Within the user database, spatial functions and stored procedures are registered as follows:

In the INZA schema, when the full schema support Netezza Performance Server feature is enabled
In the default schema, when the full schema support Netezza Performance Server feature disabled

Upgrading from the Spatial cartridge to the Spatial ESRI cartridge

You can use the convert.sh script to help you to migrate data from the well known binary (WKB) format used by the Spatial cartridge (nzspatial) to the ESRI binary format used by the Spatial ESRI cartridge (nzspatial_esri).

Note: It is strongly recommended that you back up your database before migrating your data.

The script makes use of the ST_CONVERT function, so it is not necessary to call that function separately. The script is located in /nz/export/ae/products/netezza/nzspatial_esri/<version>/scripts/convert.sh.

The convert.sh script converts a table with a VARCHAR column containing WKB geometry data to a new table with a ST_GEOMETRY column containing ESRI binary geometry data. The column names remain the same. The script also normalizes the two non-standard SRIDs in the well known binary (WKB) database:

SRID 1111 is converted to the SRID you specify (4269 is the default).
SRID 1234 is converted to the SRID you specify (27700 is the default).

The new table can be either in the same database or in a different database.

You must run the convert.sh script as user nz. Before you can run the script, the following conditions must be met:

nzspatial functions exist.
nzspatial_esri functions exist.
The destination database is INA-initialized.

Syntax

convert.sh command

Table 1. Parameters of the convert.sh script
Parameter	Description
<nzspatial db>	The source database name containing the nzspatial table data.
<nzspatial table>	The source table name containing nzspatial data.
<geometry column>	The geometry column name.
<nzspatial function database>	The name of the database containing the nzspatial functions.
<nzspatial_esri function database>	The name of the database name containing the nzspatial_esri functions.
<nzspatial_esri db>	The destination database name containing the newly created nzspatial_esri table data. The default is the source database.
<nzspatial_esri table>	The destination table name containing the newly created nzspatial_esri data. The default is a name of the form <nzspatial_table>_tmp.
<new sphere srid>	The SRID to assign to the newly created nzspatial_esri data that was previously assigned to SRID 1111 (Spherical). The default is 4269.
<new cartesian srid>	The SRID to assign to newly created nzspatial_esri data that was previously assigned to SRID 1234 (Cartesian). The default is 27700.