ST_Distance returns the distance between two geometries. The distance is measured from the closest vertices of the two geometries.

Oracle

sde.st_distance (g1 sde.st_geometry, g2 sde.st_geometry)

PostgreSQL

st_distance (g1 st_geometry, g2 st_geometry)

Double precision

A code enforcement officer needs to find if any of the buildings on a particular parcel are within 1 foot of the lot line. The building_id column of the buildings table uniquely identifies each building. The lot_id column identifies the parcel on which the building sits. The buildings polygon stores the geometry of each building's footprint. The parcels table stores the apn, which uniquely identifies each lot and is the same as the lot_id in the buildings feature class, and the parcel ST_Polygon that contains the lot geometry.

CREATE TABLE buildings (building_id integer, lot_id integer,
footprint sde.st_geometry);

CREATE TABLE parcels (apn integer unique, parcel sde.st_geometry);

INSERT INTO buildings (building_id, lot_id, footprint) VALUES (
1,
400,
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);

INSERT INTO buildings (building_id, lot_id, footprint) VALUES (
2,
400,
sde.st_polygon ('polygon ((12 3, 12 6, 15 6, 15 3, 12 3))', 0)
); 

INSERT INTO buildings (building_id, lot_id, footprint) VALUES (
3,
400,
sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 0)
);

INSERT INTO buildings (building_id, lot_id, footprint) VALUES (
4,
402,
sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 0)
);

INSERT INTO parcels (apn, parcel) VALUES (
400,
sde.st_polygon ('polygon ((-1 -1, -1 11, 11 11, 19 11, 31 11, 31 -1, 19 -1, 11 -1, -1 -1))', 0)
);

INSERT INTO parcels (apn, parcel) VALUES (
402,
sde.st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 0)
);

Next, the code enforcement officer queries the buildings and parcels tables to get a list of all the building IDs on parcel 400 and the distance they are from the lot line. (Units are defined in the projection system you're using.) Since there are three buildings on this parcel, three records should be returned.

Oracle

SELECT UNIQUE p.apn, b.building_id,  sde.st_distance(b.footprint, sde.st_boundary(p.parcel)) DISTANCE
FROM buildings b, parcels p
WHERE b.lot_id = p.apn
AND p.apn = 400
ORDER BY DISTANCE;

APN		BUILDING_ID      DISTANCE

400		           1					1
400   			  3					1
400    			  3					4

PostgreSQL

SELECT DISTINCT p.apn, b.building_id, sde.st_distance(b.footprint, sde.st_boundary(p.parcel)) AS Distance
FROM buildings b, parcels p
WHERE b.lot_id = p.apn
AND p.apn = 400
ORDER BY Distance;

apn	     building_id		distance

400            1				1
400            3				1
400     	   2				4