In the last couple of months we have been using the new st_geometry type of the ArcSDE geodatabase.
st_geometry allows us to use simple queries in order to retrieve and manipulate geographic data in a database.
For example:
SELECT * FROM buildings b
WHERE sde.stintersects(b.shape,stpoint('POINT (35.122 32.999)',1) = 1;
This query will return all the buildings that intersects the point (35.122,32.999).
st_geometry is available from version 9.2 of ArcSDE (ESRI recommends to use st_geometry from service pack 4).
More information on st_geometry can be found here and here.
In the next posts I will describe this feature, how to use it, how we use it today with .Net and some tips and troubleshooting for problems we encountered while starting to use st_geometry in our systems.