Handling invalid spatial geometry data

Invalid spatial geometry data can be loaded into tables if the DB2_GEO_ALLOW_INVALID Db2® registry variable is enabled. The attempted insertion of invalid spatial geometry data is identified with a SQL20397W message. The warning message indicates that you must fix the invalid geometry entries in the table.

The DB2_GEO_ALLOW_INVALID Db2 registry variable is disabled by default. To enable the feature, run the following command:

db2set DB2_GEO_ALLOW_INVALID=true

For more information about updating registry variables, see Setting registry and environment variables Setting registry and environment variables Setting registry and environment variables.

With the registry variable enabled, invalid geometries are stored in the type of the transport format and the status is set to invalid. When an invalid geometry is inserted, the transaction returns the following warning message:

SQL20397W Routine "GEOFROMTXT" execution has completed, but at least one error, "GSE", 
was encountered during the execution. More information is available. SQLSTATE=01H52

Invalid spatial geometries are treated as analogous to empty geometries, or, where applicable, as null when the registry variable is disabled. However, some property functions, in particular ST_IsFormat, ST_SrsId, and, if the type was identified, ST_GeometryTypeID, plus the output function that matches the format of the invalid geometry, are the exceptions. For example, an invalid geometry with format WKT can be retrieved with the ST_AsText function, and an invalid geometry with format WKB with the ST_AsBinary function.

For large geometries, it is possible that the length of the data content in the transport format exceeds the capacity of the ST_Geometry data type. In that case, the content is cut off and marked with a .. at the end and you must fix the original source data. You can find and select invalid geometry entries with the existing ST_IsValid function. The ST_IsFormat function returns the format of the stored geometry, in particular, format SDE for valid geometries, and otherwise the transport format, for example, WKT.

Usage scenarios

In the following usage scenarios, examples are given of cases where invalid geometry data was identified.

Scenario 1

Inserting an invalid geometry with id 999 and srs-id 1:

insert into points1 values (999, st_point('point (3.5 4.5, 8)',1))

SQL20397W Routine "GEOFROMTXT" execution has completed, 
but at least one error, "GSE", was encountered during the execution. 
More information is available. SQLSTATE=01H52

Scenario 2

Geometry properties, with invalid geometry in id 99999 that was inserted similar to scenario 1:

select id, 
st_isvalid(g) as valid, 
st_isformat(g) as format, 
st_ismeasured(g) as msrd, 
st_is3d(g) as threed, 
st_numpoints(g) as numpoints, 
st_geometrytypeid(g) as geotype, 
st_srsid(g) as srsid 
from polygons1 
order by id


         ID   VALID   FORMAT     MSRD     THREED   NUMPOINTS   GEOTYPE    SRSID
    -------   -----  -------    -----    -------   ---------   -------    -----

      1000        1      SDE        0          0           0        16        1
      1001        1      SDE        0          0           5        16        1
      1002        1      SDE        0          0           5        16        1
      1350        1      SDE        1          1           5        19        1
      1400        -        -        -          -           -         -        -
     99999        0      WKT        -          -           0        -1        1

   6 record(s) selected.

Scenario 3

Distance function, with invalid geometry in id 99999 in table POLYGONS1:

select p1.id point1, p2.id poly2, 
st_distance(p1.g, p2.g) distance 
from points1 p1, polygons1 p2
order by p1.id, p2.id


     POINT1      POLY2                 DISTANCE
     ------     ------   ----------------------

         0        1202                        -
         0        1203                        -
         0        1300                        -
         0       99999                        -
         1        1202   +5.94629380370664E+001
         1        1203   +5.98910761299211E+001
         1        1300                        -
         1       99999                        -
         2        1202   +7.38241153011670E+001
         2        1203   +7.37902432574931E+001
         2        1300                        -
         2       99999                        -
       100        1202                        -
       100        1203                        -
       100        1300                        -
       100       99999                        -
       101        1202   +4.66826777295390E+001
       101        1203   +4.77035889635151E+001
       101        1300                        -
       101       99999                        -
       999        1202                        -
       999        1203                        -
       999        1300                        -
       999       99999                        -

 24 record(s) selected.