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, PostgreSQL, and SQLite. Esri (and IBM, in the case of Informix and DB2) worked to ensure that the standards dictated by the Open Geospatial Consortium (OGC) were applied to the fullest extent possible.

There are, however, two exceptions, neither of which actually breach the OGC standards, but are minor implementation differences of the database management systems.

• Predicate values

  The predicate functions of ST_Geometry in Informix and PostgreSQL return a t for true and f for false, whereas ST_Geometry in DB2, Oracle, and SQLite 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 an 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;
  

• Qualifying functions

  ST_Geometry functions must be qualified with the schema name when executing SQL against tables in enterprise geodatabases in Oracle.

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

  SQLite does not use schema names, so you do not qualify the ST_Geometry functions when executing SQL against tables with ST_Geometry columns.