Current Oracle Spatial Blog Articles
The need to be able to create and use Delaunay triangles (or voronoi diagrams) inside a database is something not often heard. In addition, neither is Z interpolation of grid cell values using Delaunay triangles in consort with an Inverse Distance Weighted (IDW) algorithm.
“Why would you want to?” is a common cry.
And, for many years I would have agreed with you but recently two things intersected in a most unexpected way.
1. SC4O API Project
In the SC4O project, I decided to expose JTS’s ability to create Delaunay triangles (and Voronoi cells) from a set of input vertices (I have seen talk of the need for this in the PostGIS discussion list but no solution, integrated into the PostGIS API is yet available). I have also exposed the ability to interpolate Z values from a Delaunay triangle. These exposed functions are:
An example of some sample points being turned into a set of Delaunay triangles can be seen here:
2. Customer Question
Someone (now a customer) contacted me asking if I could help build a set of tools inside PostgeSQL/PostGIS for the interpolation/extrapolation of observed values at regular sample points throughout agricultural fields.
The reason the customer contacted me is that he had seen my articles on gridding vector polygons in PostGIS and wondered if this gridding could be extended to include interpolation to code each created grid cell with a value calculated from a set of sample points.
The field, and values observed at each sample point (I only include 4 out of all the variables: Potassium, Phosphorus, Ph and Sodium) are shown in the following diagram.
Bringing both Together
Having thought about it I first looked at the new ST_Raster capabilities of PostGIS 2.0. It has enormous promise (I could create the raster, assign the sample point values the associated cell but…) except for one thing: there is no ability to do any interpolation in the current system through which I could take the limited sample point values and compute and assign values for the other grid cells. It may be my lack of ability with raster processing (likely) but looking at ST_Raster’s ST_MapAlgebraFctNgb didn’t convince me that it would help with the interpolation: I kept coming back to the vector nature of the problem space.
To solve the problem I first needed to (Oracle first, later PostGIS).
Then, to solve the problem (via SQL) I did the following:
All the above was done using vector processing.
The following 4 images show the result of interpolating and assigning the Potassium (K), Phosphorus (P), Ph and Sodium (Na) observed values across the whole sampled areas.
The following query gives one an idea of the number of (10×10 meter) cells created.
This sort of processing can be executed at any one of three levels in a solutions architecture: data, application, client.
The customer is currently doing this processing using a collection of open source software from a variety of projects glued together via a scripting language.
When the ability to do this processing is available at the data tier (ie inside the database) then what we see is something that tightly links the creation of source data to associated computed data. Databases offer a variety of mechanisms for tightly integrating the two. For example, the creation of new field (polygon) and sample data can drive, automatically, the creation of the derived data either via database triggers (probably linked to an asynchronous script) or through technologies like materialized views. Thus, if an agricultural scientist noted that they had entered the value for a sample variable incorrectly (eg K) then any update to that value in the database could automatically fire the derived data generation. No one needs to remember to re-run a client script or middle tier application program.
Anyone interested in the solution please contact me directly.