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.
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';
select bf.building_id "Building id" from buildingfootprints bf, lots where sde.st_contains(lot,footprint) = 1;
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) );
insert into linestring_test values ( linefromtext('linestring(10.01 20.03, 20.94 21.34, 35.93 19.04)',coordref()..srid(1)) );
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.