SQL implementation differences for the ST_Geometry type

There is little difference between the implementation of spatial SQL (ST_Geometry) implementation for Informix, DB2, Oracle, and 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, 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

  ST_Geometry functions must be qualified with the schema name when executing SQL against tables in enterprise geodatabases in Oracle. This is true for all geodatabases in Oracle created at ArcGIS 9.3 or later releases.

  You can quallify the ST_Geometry functions when executing SQL against tables with ST_Geometry columns in DB2, Informix, or PostgreSQL, but it is not required.