Best practices and considerations

Edit online

When using Geospatial Analytics, there are certain things you need to consider.

Best practices

  1. When inserting or updating geospatial data for a column defined as a geometry type, the constructor functions should be used to enforce data correctness. For example, to insert a value into a column defined as an ST_POINT data type, use a statement like the following:
    INSERT INTO mytable (geopoint) VALUES ST_POINT('point (30 40)');
    Without using these functions, data that does not conform to the geometry type can be inserted into the column.
  2. Geometry type arguments passed to geospatial functions expect properly formatted geometry values. Use a constructor function to guarantee the value is a correctly formatted geometry value. For example, when working with data in a global variable, use statements like the following to ensure the content of myline_gvar is correctly formed:
    CREATE VARIABLE myline_gvar QSYS2.ST_LINESTRING;
SET myline_gvar = QSYS2.ST_LINESTRING('linestring(10 10, 20 20)');
SELECT * FROM mytable t where QSYS2.ST_INTERSECTS(t.linestring_column, myline_gvar) = 1;
  3. When using an untyped parameter marker or the NULL value, use the CAST specification to provide a data type for the parameter marker or NULL value that function resolution can use to find the correct function. For more information, see Using parameter markers or the NULL values as function arguments
  4. The geospatial functions and geospatial data types reside in the QSYS2 library. If you use an unqualified function or geospatial data type, the SQL path is used to locate the object. When using unqualified geospatial functions and data types, ensure QSYS2 is included in the SQL path. For more information, see Unqualified function, procedure, specific name, type, and variables.
  5. Most of the system-provided geospatial functions are defined with the MODIFIES SQL DATA attribute. When creating a procedure or function that uses one of these functions, the MODIFIES SQL DATA attribute needs to be included in the routine definition.

Considerations

Java™ environment

Because the geospatial functions use functionality provided by Java, a Java environment is created in the current job. This requires the following conditions.
  1. A JVM must not already exist in the job (with the exception of a JVM created by the Java stored procedures support).
  2. The job CCSID cannot be 65535.
  3. PASE must be installed and operational. The CHKPRDOPT PRDID(5770SS1) OPTION(33) CL command can be used to verify that PASE is installed.
  4. The Geospatial functions are implemented via LANGUAGE JAVA functions and always run in the default activation group, ACTGRP(*DFTACTGRP). Therefore, these functions should not be used within programs that were built with ACTGRP(*NEW).

Well-Known Text (WKT) Considerations

There are cases where WKT is adjusted to a preferred representation.:
  • Polygon WKT and the right-hand rule
    The right-hand rule is used to determine the ordering of points. Polygons are defined by a linear ring that describes the polygon's boundary. On a globe, the same boundary can be used to represent two different polygons depending which side of the linear ring is the interior of the polygon and which side of the linear ring is the exterior of the polygon. Consider a polygon that describes London and a polygon that describe the full Earth except for London. These two polygons would have the same boundary but different interiors. In order to determine which side of the boundary is the interior of the polygon you are trying to define, the "right-hand rule", as defined by RFC 7946: The GeoJSON format, is used. This rule states that for exterior rings, points must be defined in a counterclockwise direction, and for holes, points must be defined clockwise. Since many existing data sets have polygons defined in the incorrect order due to lack of awareness of this rule, Geospatial Analytics adjusts the order of the points. It assumes the area of interest is smaller than half of the full Earth.
  • Multipoint WKT
    If the same point is defined multiple times in a multipoint geometry, the duplicate point is removed.