I am looking to write a simple UDF in Teradata 13 Express. This method will take in 2 parameters, both of these will be the type ST_Geometry.
I am looking for a tutorial which will guide me through the following process: 1. Obtaining the C/++ Headers for Teradata 2. Writing the Source code (C/++) 3. Compiling to what format? 4. And implementing into Teradata.
From my tests, appears that Geospatial Features in Teradata 13 was simply "hacked" in. Not really well implemented, and UDF cannot access geospatial stuff.
Geospatial types are not "true" SQL types unlike other SQL DB's such as PostgreSQL which implements these as "true types". In TD they are simply System Defined functions which pretends to be a type.
I wrote my first test prototype to work within PostgreSQL, this included a few "optimization" and rewrites of some functions. Optimization includes MBR (TD supports this), and a quicker and faster contains method.
My optimization was very simple, First check against boxes of the polygons, this is a very FAST operation. This operation may return multiple polygons, then we do a more precise match against the actual polygon to determine which one it is in. This effectively reduces the required checks between polygons (for example, total polygons 100, to down to about 2-3 polygons). The check against polygon was using a custom method which performed the same as ST_CONTAINS, but using a more performance optimized algorithm.
TD has a contains method but I find it to be too slow. Cannot really implement a new contains method as UDFs cannot access Geospatial types.
TD supports tessellation but seems extremely complex to setup, and unsure of what some of the values required are. I have not tested tessellation as of yet, but I suspect it will not perform anywhere near PostgreSQL's geospatial speed.
At the moment, I have put testing Geospatial on the backburner. This is due to many of the above reasons.
The ST_Geometry type is a type not a system function, but you can't access them within a UDF. You can always pass in the WKT or WKB to a UDF and process it that way.
Tessellation will give much better performance than using UDFs for the ST_Contains operation. There is an Orange Book available in the geospatial download section that describes how to use tessellation.