SQL implementation differences for the ST_Geometry type

There is little difference between the implementation of spatial SQL for Informix, DB2, and the spatial type for Oracle or PostgreSQL. ESRI, a partner and codeveloper of extended spatial SQL within the IBM database management system (DBMS) products, worked in a cooperative environment to ensure that the standards dictated by the Open Geospatial Consortium (OGC) were applied to the fullest extent possible.

There are, however, three notable exceptions, none of which actually breach the OGC standards, but are minor implementation idiosyncrasies of the DBMSs themselves.

• Predicate values

  The predicate functions of Informix and PostgreSQL return a t for true and f for false, whereas DB2 and the spatial type for Oracle use 1 for true and a 0 for false.In this example of Informix SQL, the select statement returns only those building IDs for which the ST_Contains function returns t for building lots that contain building footprints.

  select bf.building_id "Building id"
  from buildingfootprints bf, lots
  where st_contains(lot,footprint) = 't';

  The same ST_Contains function for a DB2 or spatial type for Oracle SELECT statement returns the same building IDs for which 1 is returned when a lot contains a footprint.

  select bf.building_id "Building id"
  from buildingfootprints bf, lots
  where sde.st_contains(lot,footprint) = 1;

• SRID input

  For well-known text (WKT), well-known binary (WKB), and ESRI shape representations in DB2, the spatial reference ID (SRID) is entered through the srid method of the coordref object. For Informix and the spatial types for Oracle and PostgreSQL, the SRID value is entered directly.In this example, the SRID of 1 is entered directly into the Informix linefromtext function.

  insert into linestring_test values
  (
     linefromtext('linestring(10.01 20.03, 20.94 21.34, 35.93 19.04)', 1)
  );

  In this example, the SRID of 1 is entered through the coordref srid object into the DB2 linefromtext function.

  insert into linestring_test values
  (
     linefromtext('linestring(10.01 20.03, 20.94 21.34, 35.93 19.04)',coordref()..srid(1))
  );

• Qualifying functions

  SQL functions can be qualified with the schema name. For example, sde.ST_Buffer. This can be done when executing SQL against tables with ST_Geometry columns in any of the database management systems. However, for such tables in Oracle databases, the functions must be qualified with the schema name. This is true for all ArcSDE geodatabases for Oracle newly created at ArcGIS 9.3 or later releases.