PostGIS and spatial database

[Connie Irwin]

It seems to me that much of the work done in terms of storing and manipulating the main two types of spatial data, geometric, and geographic is done by functions and procedures that are part of the database engine. Is this the case? Can user defined functions and procedures be created by a regular user, or must one access the database as a super user? Also, does the spatial database contain it's own SQL engine, or is it part of the Postgres SQL engine? One more question, can one use an ORM tool like hibernate to broker SQL to the database? If programming in java, can the JPA be used to map table data?
Sorry to ask so much in a single email, but I am extremely fascinated by the possibilities offered by a Spatial Data base.

Sincerely,

Connie Irwin

[Katrina Owen]

I can't answer most of your questions, but regarding the ORM, we are using an ORM (in Ruby) to broker access. In this case we needed an extra piece that integrated with our usual ORM. A few years ago we wrote our own little custom adapter, but these days there's a fairly large community of users, and an open source option is now available.

I don't know whether a plugin or adapter exists for Hibernate, though.

[chris webster]

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

[Connie Irwin]

Chris,

Thank you for your reply. So then, if I've understood you correctly, the rdbms does most of the work, and in addition, the spatial database has it's own reference data. Is there a universal reference architecture that is used by all spatial database systems? Is the spatial database reference data licensed?

Thanks again,

Connie

[chris webster]

Connie Irwin wrote:Chris,

Thank you for your reply. So then, if I've understood you correctly, the rdbms does most of the work, and in addition, the spatial database has it's own reference data. Is there a universal reference architecture that is used by all spatial database systems? Is the spatial database reference data licensed?

Thanks again,

Connie

Here's a quick intro to spatial references, coordinates etc.

It's a big topic, and you probably need to explore one of the books I mentioned, or an introductory GIS textbook, if you want to get into it in any depth.

Geographic coordinates can be specified in different ways. Longitude/latitude, or X/Y coordinates from a known point e.g. the UK Ordnance Survey system uses coordinates that represent the number of meters east/north of a fixed point to the southwest of the UK. So you already have to deal with converting between different coordinate systems e.g. if you want to display UK OS data on a lat/long map such as Google Maps.

But there's a lot more to it than that. The earth's surface is not flat, so you have to project the real surface positions onto an artificial flat surface in order to get coordinates you can use on a map. There are various kinds of projections (cylindrical, conical etc), which will influence how the coordinates turn out.

Not only that, but you also need to remember that the earth is not a smooth sphere either (in fact it's a lumpy spheroid), and also that places move around and up and down over time, so your coordinates will have resulted from a survey based on a suitable model of the earth (or at least the bit your map covers) at a particular time. This is known as the datum e.g. WGS84 is the World Geodetic Survey 1984 datum which is often used as the basis for lat/long maps.

The combination of coordinates, projection and datum define your spatial reference system, and there are standard codes describing these things (EPSG codes) and standard algorithms etc for converting between them where possible (obviously there's no point trying to convert the UK's national grid system into the national grid system used by Japan, because there is no overlap). This stuff is published, but tools like PostGIS or GeoServer (and most other GIS software such as ESRI) will include the information and tools needed to convert between them, i.e. to "re-project" and transform your coordinates from one system to another.

Hope this helps. Right, I'm off for a pizza!

Regards,
Chris

[Ravi Dalal]

I recently worked on using an ORM eclipselink with Oracle Spatial. Oracle Spatial uses SDO_GEOMETRY for storing of spatial data and also provide JGeometry java class for working with SDO_GEOMETRY in java. I was able to perform CRUD operation using eclipselink and Oracle Spatial. You may follow http://wiki.eclipse.org/Introduction_to_EclipseLink_Support_for_Oracle_Spatial_(ELUG) for better understading. Then there is also Hibernate extension named HibernateSpatial (http://www.hibernatespatial.org/) which provide (approx) database agnostic support for spatial data.