Hi Connie,
I'm an Oracle developer with some spatial/GIS experience, rather than a PostGIS expert, and I have no idea about ORM access to spatial databases (I suspect there might be problems with the spatial data-types), but until the real experts reply, here goes...
Spatial databases need to support spatial data types - points, lines, polygons, possibly rasters, etc - and spatial operations e.g. contains, near to, intersects or whatever. Spatial data also tends to come in very high volumes e.g. all the points/lines/polygons describing the infrastructure of a city. So your spatial functions often need to operate on huge volumes of data, which introduces the need for various optimisation techniques such as spatial indexing, to speed up searches and the application of those operations. Spatial indexes are different from ordinary data indexes, because the logic of spatial operations is different - you want the index to be able to spot points etc that are "near" each other, rather than simply in the same part of the alphabet or whatever.
Because of the huge volumes of data, it makes sense to implement and perform these kind of operations in the database wherever possible. After all, if you're searching a big database of all the fire hydrants in your city to find the nearest one to a given point, it's much easier to let the database engine do most of the work using the index and just return the one item you want, rather then fetching all the data and comparing each item with your search value individually. This is a good general principle when dealing with large volumes of data: the RDBMS is optimised for this stuff, so let the RDBMS do the heavy lifting and just return the items you're really interested in.
Another useful feature of many spatial databases is the ability to convert between spatial reference systems e.g. from national easting/northing grids (such as the Ordnance Survey system in the UK) to longitude/latitude. There are external libraries that can do this as well, but spatial databases typically include all the necessary reference data and functionality within the DB and make it available via SQL, which is handy, especially when you need to apply the same conversion across a lot of data.
The spatial data types, indexes and functions are implemented differently in different RDBMS, but with the common aim of meeting the above challenges as effectively as possible.
PostGIS extends existing geometric data-types and indexing mechanisms within PostgreSQL, then adds a set of
spatial functions (I think these are actually pgplsql wrappers around underlying programs in C), as well as some tables to keep track of where your spatially enabled data is kept. PostGIS also provides some nice helper functions to return your spatial data from SQL queries in standard XML formats such as GML or KML.
Oracle Spatial is an add-on option on the Oracle RDBMS, and provides its own set of spatial data types and reference tables, as well as an extensive set of packaged PL/SQL spatial functions.
In both cases, you get a platform-specific set of functions that can be included in your SQL queries etc to make spatial processing pretty seamless and very efficient. You can then use these functions in your own SQL or stored code in Oracle (PL/SQL) or PostgreSQL (PGPLSQL). As a database developer, my natural inclination is to make as much use as possible of stored code to encapsulate any complex data-intensive processing, and spatial processing would be an obvious candidate for this kind of approach.
There are also some proprietary products such as ESRI's ArcServer, which combine an RDBMS (such as Oracle or SQLServer) with various spatial functions etc and a lot of additional middle-tier functionality. These can be very powerful e.g. if you're committed to using ESRI's GIS client tools as well. But they are also very expensive, tie you into a proprietary solution, and are often less flexible than an approach based on more open database technologies such as PostGIS/PostgreSQL (or even Oracle RDBMS with Spatial add-on, plus your own choice of middle tier).
Spatial databases such as PostGIS/PostgreSQL or Oracle Spatial etc also integrate well with other GIS tools such as the excellent open source
GeoServer spatial server which runs in a J2EE/servlet container and provides a lot of additional spatial functionality out of the box, including support for standard XML-based spatial web service interfaces such as WMS and WFS.
You can find out more about the wonderful world of open source GIS tools beyond Google Maps, including PostGIS, from books like:
GIS for Web Developers by Scott Davis
Desktop GIS by Gary Sherman
Anyway, I reckon that's enough to tide you over until the PostGIS experts can correct my ramblings!
Regards,
Chris