Extensibility

Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-27-2009
11:02 AM

05-27-2009
11:02 AM

Fun with Teradata and Geometry

Now that you’ve installed Teradata’s geospatial features with Teradata Express let’s roll up our sleeves and see what we can do with this. (If you haven’t setup your TD Express with Geospatial yet, see my article. I think the best way to begin exploring these features is to start is with some basic geometric shapes. Even though we call this Teradata Geospatial, we can ignore the “geo’ part for now and just play around with simple spatial objects like Points, Lines and Polygons. When we talk about Geospatial, we usually mean that we are using objects mapped to the Earth’s reference space; ie longitude and latitude. But really we are free to use any reference space that we like. Teradata by default will simply see our coordinates as references to an arbitrary grid. In the examples here, I’m using a 10x10 grid. These aren’t inches or meters or miles but just plain units.

Let’s get started by creating a new table to hold some spatial objects.

CREATE TABLE shapes

(

id INTEGER NOT NULL PRIMARY KEY,

geometry ST_GEOMETRY

);

A very basic table that contains an integer column for the shape’s ID and a column named ‘geometry’ for data using the new spatial ST_Geometry type. This of course is where all the magic will happen.

Now let’s add some data. Here’s a list of 5 simple Points that we want to map onto our 10x10 grid.

INSERT INTO shapes VALUES (1,'Point(2 2)');

INSERT INTO shapes VALUES (2,'Point(8 7)');

INSERT INTO shapes VALUES (3,'Point(1 9)');

INSERT INTO shapes VALUES (4,'Point(9 2)');

INSERT INTO shapes VALUES (5,'Point(4 4)');

As you can see, the syntax for adding spatial objects is fairly straight forward. The format used to describe spatial objects is known as WKT or Well Known Text representation. Two things to notice are the single quote around the spatial object name and coordinates and that the X and Y coordinates are separated by a space, not a comma. This will be more noticeable later when we use lines and polygons.

Now that we have these 5 points in our table, let’s play with some of the spatial functions offered by Teradata. Here’s an example showing how to calculate the distance between 2 points. The easiest data points to start with are those with IDs 1 and 4. Since both have Y coordinate values of 2, it’s pretty easy to verify the calculation (the distance between (2,2) and (9,2) better be 7 or I’ll need to go find my high school geometry book!).

SELECT P1.geometry.ST_Distance(P2.geometry) AS Distance

FROM shapes P1, shapes P2

WHERE P1.id = 1 AND P2.id = 4;

Results:

Distance

7.00

The SQL is pretty easy to understand. Using dot notation, we’re calling the ST_Distance() spatial method on the geometry field. We reference the shapes table twice so that we can limit the calculation to only IDs 1 and 4. And as we predicted, the distance is 7 units. Again, these are arbitrary units, not tied to inches, meters, miles or geospatial coordinates.

Here’s the same query changed slightly to calculate the distance between all points on our grid:

SELECT P1.id, P2.id, P1.geometry.st_distance(P2.geometry) AS Distance

FROM shapes P1, shapes P2

ORDER BY 1,2;

Results:

id id Distance

1 1 0.00

1 2 7.81

1 3 7.07

1 4 7.00

1 5 2.83

2 1 7.81

2 2 0.00

2 3 7.28

2 4 5.10

2 5 5.00

3 1 7.07

3 2 7.28

3 3 0.00

3 4 10.63

3 5 5.83

4 1 7.00

4 2 5.10

4 3 10.63

4 4 0.00

4 5 5.39

5 1 2.83

5 2 5.00

5 3 5.83

5 4 5.39

5 5 0.00

Now that we’re comfortable with Points, let’s add a Line object to our table. Here is the syntax for adding a spatial Linestring object. Again, notice the space between the X and Y coordinates. The reason for this usage is now clearer; we need the comma to separate the pairs of coordinates for these multi-point shapes.

INSERT INTO shapes(6,'linestring(0 8,5 6,10 5)');

Now let’s calculate the length of our new line object (ID = 6). Again, this is a simple SQL statement using the ST_Length() method of our ST_Geometry data type, which in this case is the geometry field. This calculation measures all the line segments that make up our line string.

SELECT geometry.ST_Length() AS Length

FROM shapes

WHERE id = 6;

Results:

Length

10.48

Now let’s have some fun with this line and our original points. What is the distance between the original 5 points in our shapes table to this line? This could model some real world example, say where we want to calculate the distance of our stores from a major highway (In this SQL I’m aliasing the shapes table to Point and Line to help clarify the SQL – I hope!):

SELECT Point.id, Point.geometry.ST_Distance(Line.geometry) AS Distance

FROM shapes Point, shapes Line

WHERE Point.id <= 5

AND Line.id = 6

ORDER BY 2;

Results:

id Distance

3 1.30

2 1.57

5 2.23

4 3.14

1 4.83

From these results we can see that while most of the points are near the line, point ID=1 is the outlier and if this was a real-world query, we could take some action based on these results.

Now let’s take a look at Polygons. As we all remember from class, Polygons are shapes with multiple vertices. These shapes start with basic rectangles and squares to octagons and beyond. Teradata Geospatial release 1.0 (the one we’ve installed with TD Express 12.0) can support Polygons with up to 2,500 vertices. Teradata 13.0 improves on this and will support up to a million vertices.

Creating a simple Polygon is just as easy as creating the Linestring, though please note the double set of parenthesis - this is to enable more advanced Polygons that can contain internal polygons; imagine representing a donut – the internal polygon would represent the hole.

INSERT INTO shapes VALUES(7, 'Polygon((0 1, 0 5, 5 5, 5 1, 0 1))' );

Teradata Geospatial includes some very useful measurement methods, for example:

- ST_Length
- ST_Distance
- ST_Area
- ST_Permeter

Let’s try a few of these on our new Polygon (ID = 7)

SELECT id,geometry.ST_Area() AS Area

FROM shapes

WHERE id = 7;

Results:

id Area

7 20.00

20 is correct – this Polygon is a 5x4 rectangle.

SELECT id,geometry.ST_Perimeter() as Perimeter

FROM shapes

WHERE id = 7;

Results:

id Perimeter

7 18.00

Ah, 18 is also correct. Our 5x4 square has a perimeter of 18.

We also have a collection of other methods to analyze relationships between spatial objects.

These are known as our Topology methods and return TRUE of FALSE values represented by 0 or 1. This list includes such methods as

- ST_Contains
- ST_Intersects
- ST_Overlaps
- ST_Touches

For example, let’s find out if any of our original 5 Points are contained within this new Polygon:

SELECT Point.id, Point.geometry

FROM shapes Point, shapes Poly

WHERE Poly.id = 7

AND Point.id <= 5

AND Poly.geometry.ST_Contains(Point.geometry) = 1;

Results:

id geometry

5 POINT (4 4)

1 POINT (2 2)

I’ve also included the Point geometry column in the output to show its location and also to demonstrate how the ST_Geometry objects are returned from SQL queries using their WKT format. From these results we can see that Points (4,4) and (2,2) are indeed contained in our rectangle.

Now let’s add a second Polygon to the grid:

INSERT INTO shapes VALUES(8, 'Polygon((2 3,2 9,9 9,9 6,6 3,2 3))' );

Again, we can calculate some basic measurements on this shape:

SELECT id,geometry.st_area() AS Area,

geometry.st_perimeter() AS Perimeter

FROM shapes

WHERE id = 8;

Results:

id Area Perimeter

8 37.50 24.24

And again, let’s find any other shapes that are contained within the Polygon (ID = 8):

SELECT S1.id, S1.geometry

from shapes S1, shapes S2

where S2.id = 8

and S2.geometry.ST_Contains(S1.geometry) = 1

and S1.id != S2.id;

Results:

id geometry

5 POINT (4 4)

2 POINT (8 7)

Here’s a query to test whether any objects intersect this Polygon. In addition to any Points that are within the Polygon, we should expect to also see the first Polygon as well as our Linestring in these results:

SELECT S1.id, S1.geometry

from shapes S1, shapes S2

where S2.id = 8

and S2.geometry.ST_Intersects(S1.geometry) = 1

and S1.id != S2.id;

Results:

id geometry

7 POLYGON ((0 1,0 5,5 5,5 1,0 1))

5 POINT (4 4)

6 LINESTRING (0 8,5 6,10 5)

2 POINT (8 7)

Sure enough, the results still show Points 5 and 2 as being in the new Polygon, but also now the first Polygon and Linestring are returned as well. Those are not “contained” in this Polygon, but they do intersect it. And again, notice the “=1” comparison to the ST_Intersects() method to filter on the results, as in True, these objects intersect.

Now let’s look at another group of spatial methods, the ones that return new spatial objects. These methods include

- ST_Buffer
- ST_Boundary
- ST_Transform

Here’s an example using ST_Intersection(), which will return a new spatial object representing the intersection of our 2 Polygons:

SELECT S1.geometry.st_intersection(S2.geometry) AS Intersection

FROM shapes S1, shapes S2

WHERE S1.id = 7 AND S2.id = 8;

Results:

Intersection

POLYGON ((2 5,5 5,5 3,2 3,2 5))

Pretty cool, huh? And we can use our spatial methods on this new object too. For example, let’s look for Points that contained in both Polygons. This would answer the question “are there any Points in this intersection object?”

SELECT Points.id, Points.geometry

FROM shapes Points, shapes Poly1,shapes Poly2

WHERE Poly1.id = 7 and Poly2.id = 8

AND Points.id <=5

AND Poly1.geometry.st_intersection(Poly2.geometry).ST_Contains(Points.geometry) =1;

Results:

id geometry

5 POINT (4 4)

And we see that we do have one point contained in this intersection.

There’s almost a limitless amount of fun that we can have with Teradata and these geometric shapes. Let’s close this out with 2 more examples using the Linestring. First, let’s see which objects are in its path using the ST_Intersects method. In a real world example, this could be a query to find which towns this road passes through.

SELECT S1.id, S1.geometry

FROM shapes S1, shapes Line

WHERE Line.id = 6 AND S1.id != Line.id

AND Line.geometry.st_intersects(S1.geometry) = 1;

Results:

id geometry

8 POLYGON ((2 3,2 9,9 9,9 6,6 3,2 3))

OK, that was easy. Our line passes through Polygon ID = 8. But now let’s take a look at the ST_Buffer() method. ST_Buffer() is a spatial method that returns a new spatial object. It will create a new Polygon that is X units larger than the original spatial object in all directions. This could be a circular buffer around a single Point or a new larger Polygon around an existing Polygon or a new Polygon around a Linestring.

We’ll use the Linestring (ID=6) for our example. A real world example could use this to define a flood zone area around a river; or a buffer zone to mark the area impacted by the path of a hurricane.

SELECT geometry.ST_Buffer(3) AS Buffer

FROM shapes

WHERE id = 6;

Results:

geometry.ST_BUFFER(3)

POLYGON ((5.858436 8.887725,10.588348 7.941742,10.588348 7.941742,10.741501 7.906919,10.892621 7.864128,11.041295 7.813486,

11.187114 7.755133,11.32968 7.689229,11.468601 7.615953,11.603497 7.535508,11.733997 7.448112,11.859745 7.354007,

11.980396 7.253449,12.095618 7.146715,12.205096 7.034097,12.308531 6.915903,12.405638 6.792458,12.496151 6.664101,

12.579822 6.531182,12.656423 6.394066,12.725742 6.253129,12.78759 6.108757,12.841798 5.961346,12.888216 5.811301,

12.926718 5.659031,12.957198 5.504956,12.979573 5.349496,12.99378 5.193078,12.999782 5.036131,12.997562 4.879085,

12.987126 4.72237,12.968502 4.566417,12.941742 4.411652,12.906919 4.258499,12.864128 4.107379,12.813486 3.958705,

12.755133 3.812886,12.689229 3.67032,12.615953 3.531399,12.535508 3.396503,12.448112 3.266003,12.354007 3.140255,

12.253449 3.019604,12.146715 2.904382,12.034097 2.794904,11.915903 2.691469,11.792458 2.594362,11.664101 2.503849,

11.531182 2.420178,11.394066 2.343577,11.253129 2.274258,11.108757 2.21241,10.961346 2.158202,10.811301 2.111784,

10.659031 2.073282,10.504956 2.042802,10.349496 2.020427,10.193078 2.00622,10.036131 2.000218,9.879085 2.002438,

9.72237 2.012874,9.566417 2.031498,9.411652 2.058258,9.411652 2.058258,4.411652 3.058258,4.233304 3.099625,

4.057811 3.151793,3.885828 3.21457,-1.114172 5.21457,-1.114172 5.21457,-1.258423 5.276699,-1.399225 5.346291,

-1.536192 5.423158,-1.668948 5.507088,-1.79713 5.59785,-1.920386 5.695197,-2.038378 5.798861,-2.150783 5.908558,

-2.257293 6.023987,-2.357616 6.144833,-2.451477 6.270763,-2.538619 6.401434,-2.618802 6.536485,-2.691808 6.675549,

-2.757435 6.818242,-2.815505 6.964175,-2.865857 7.112946,-2.908354 7.264149,-2.94288 7.417369,-2.969339 7.572186,

-2.98766 7.728176,-2.997792 7.88491,-2.999707 8.04196,-2.9934 8.198895,-2.978888 8.355285,-2.956211 8.510701,

-2.925432 8.664717,-2.886634 8.816912,-2.839924 8.966867,-2.78543 9.114172,-2.723301 9.258423,-2.653709 9.399225,

-2.576842 9.536192,-2.492912 9.668948,-2.40215 9.79713,-2.304803 9.920386,-2.201139 10.038378,-2.091442 10.150783,

-1.976013 10.257293,-1.855167 10.357616,-1.729237 10.451477,-1.598566 10.538619,-1.463515 10.618802,-1.324451 10.691808,

-1.181758 10.757435,-1.035825 10.815505,-0.887054 10.865857,-0.735851 10.908354,-0.582631 10.94288,-0.427814 10.969339,

-0.271824 10.98766,-0.11509 10.997792,0.04196 10.999707,0.198895 10.9934,0.355285 10.978888,0.510701 10.956211,

0.664717 10.925432,0.816912 10.886634,0.966867 10.839924,1.114172 10.78543,1.114172 10.78543,5.858436 8.887725))

That’s a pretty detailed Polygon that we calculated. Once again, pretty cool!

And finally, let’s get real fancy. Let’s take this new buffer Polygon and see how many of our objects lie in its path:

SELECT S1.id, S1.geometry

FROM shapes S1, shapes Line

WHERE Line.id = 6 AND S1.id != Line.id

AND Line.geometry.st_buffer(3).st_intersects(S1.geometry) = 1

ORDER BY 1;

Results:

id geometry

2 POINT (8 7)

3 POINT (1 9)

5 POINT (4 4)

7 POLYGON ((1 1,1 5,5 5,5 1,1 1))

8 POLYGON ((2 3,2 9,9 9,9 6,6 3,2 3))

Almost all of them - let’s hope this isn’t a Category 5 hurricane!

I hope you’ve enjoyed this quick pass through Teradata’s Spatial features. In my next article we’ll take a dive into the geo side of Geospatial and use some real world examples. In the meanwhile, continue exploring these spatial features. Here’s a more complete list of the available methods in each category for you to explore:

- ST_Area
- ST_Distance
- ST_SphericalDistance
- ST_SpheroidalDistance
- ST_Perimeter
- ST_Length

- ST_Buffer
- ST_Intersection
- ST_Boundary
- ST_Difference
- ST_Envelope
- ST_ExteriorRing
- ST_GeometryN
- ST_InteriorRingN
- ST_Transform

- ST_Intersects
- ST_Overlaps
- ST_Relate
- ST_Touches
- ST_Within
- ST_Contains
- ST_Disjoint
- ST_Crosses
- ST_Equals

- ST_AsBinary
- ST_AsText
- ST_CoordDim
- ST_Dimension
- ST_GeometryType
- ST_IsEmpty
- ST_IsSimple
- ST_IsClosed
- ST_NumPoints
- ST_SRID

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-01-2009
06:19 PM

11-01-2009
06:19 PM

Re: Fun with Teradata and Geometry

I am trying to load a shapefile in ST_GEOMETRY but getting a Java error. I even tried to fix the classpath but still not able to run the import. Here is my error message. Any help will be appreciated.

Exception in thread "main" java.lang.UnsupportedClassVersionError: com/terad

geo/TDGeoImport (Unsupported major.minor version 49.0)

at java.lang.ClassLoader.defineClass0(Native Method)

at java.lang.ClassLoader.defineClass(Unknown Source)

at java.security.SecureClassLoader.defineClass(Unknown Source)

at java.net.URLClassLoader.defineClass(Unknown Source)

at java.net.URLClassLoader.access$100(Unknown Source)

at java.net.URLClassLoader$1.run(Unknown Source)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(Unknown Source)

at java.lang.ClassLoader.loadClass(Unknown Source)

at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)

at java.lang.ClassLoader.loadClass(Unknown Source)

at java.lang.ClassLoader.loadClassInternal(Unknown Source)

Exception in thread "main" java.lang.UnsupportedClassVersionError: com/terad

geo/TDGeoImport (Unsupported major.minor version 49.0)

at java.lang.ClassLoader.defineClass0(Native Method)

at java.lang.ClassLoader.defineClass(Unknown Source)

at java.security.SecureClassLoader.defineClass(Unknown Source)

at java.net.URLClassLoader.defineClass(Unknown Source)

at java.net.URLClassLoader.access$100(Unknown Source)

at java.net.URLClassLoader$1.run(Unknown Source)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(Unknown Source)

at java.lang.ClassLoader.loadClass(Unknown Source)

at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)

at java.lang.ClassLoader.loadClass(Unknown Source)

at java.lang.ClassLoader.loadClassInternal(Unknown Source)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-02-2009
07:19 AM

11-02-2009
07:19 AM

Re: Fun with Teradata and Geometry

OK! one of my office colleagues fixed the issue but both of us are running into the same issue. As below, any feedback will be helpful. I understand that I did not provide the correct database, file, etc. But I just wanted to run and see if the import apps works.

C:\TdGeoImportExport\bin>java -Xms256m -Xmx512m -classpath .;"c:\TdGeoImportExpo

rt\bin\terajdbc4.jar";"c:\TdGeoImportExport\bin\tdgssconfig.jar";. com.teradata.

geo.TDGeoImport -l geo_system/geo_user,geo_pw -s mydb

java.lang.UnsatisfiedLinkError: C:\TdGeoImportExport\bin\geojni.dll: This applic

ation has failed to start because the application configuration is incorrect. Re

installing the application may fix this problem

at java.lang.ClassLoader$NativeLibrary.load(Native Method)

at java.lang.ClassLoader.loadLibrary0(Unknown Source)

at java.lang.ClassLoader.loadLibrary(Unknown Source)

at java.lang.Runtime.loadLibrary0(Unknown Source)

at java.lang.System.loadLibrary(Unknown Source)

at com.teradata.geo.TDGeoImport.main(TDGeoImport.java:869)

===========================================

Data importing failed!

C:\TdGeoImportExport\bin>java -Xms256m -Xmx512m -classpath .;"c:\TdGeoImportExpo

rt\bin\terajdbc4.jar";"c:\TdGeoImportExport\bin\tdgssconfig.jar";. com.teradata.

geo.TDGeoImport -l geo_system/geo_user,geo_pw -s mydb

java.lang.UnsatisfiedLinkError: C:\TdGeoImportExport\bin\geojni.dll: This applic

ation has failed to start because the application configuration is incorrect. Re

installing the application may fix this problem

at java.lang.ClassLoader$NativeLibrary.load(Native Method)

at java.lang.ClassLoader.loadLibrary0(Unknown Source)

at java.lang.ClassLoader.loadLibrary(Unknown Source)

at java.lang.Runtime.loadLibrary0(Unknown Source)

at java.lang.System.loadLibrary(Unknown Source)

at com.teradata.geo.TDGeoImport.main(TDGeoImport.java:869)

===========================================

Data importing failed!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-19-2009
11:22 AM

11-19-2009
11:22 AM

Re: Fun with Teradata and Geometry

Worked out fine. It was issue with the classpath.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-08-2010
05:16 PM

03-08-2010
05:16 PM

Re: Fun with Teradata and Geometry

Good one

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-30-2012
09:12 AM

05-30-2012
09:12 AM

Re: Fun with Teradata and Geometry

I recently got access to use the geospatial functions in Teradata 13.0, but now i'm getting errors & I'm wondering if you could help -- or point me in the right direction. I searched the web & didn't find anything, so i'm posting it here so that if anyone else encounters the problem - hopefully hey can just google & have the answer handy... (Assuming that you know the answer)

I'm trying to execute this:

CREATE VOLATILE TABLE mygeo (

id INTEGER,

latlon ST_Geometry

)

NO PRIMARY INDEX;

The error I get is this:

7552: Unable to create UDT Instance SYSUDTLIB.ST_GEOMETRY: the library for SYSUDTLIB rev 9 not found - Check message log for possible 7820 errors in library synchronization.

Where is the message log? And is this a permissions issue? or is it something that needs to be installed. The orange book indicates that everything that is needed should be installed, so I really need help figuring out what needs to happen to make it work.

Thanks!

I'm trying to execute this:

CREATE VOLATILE TABLE mygeo (

id INTEGER,

latlon ST_Geometry

)

NO PRIMARY INDEX;

The error I get is this:

7552: Unable to create UDT Instance SYSUDTLIB.ST_GEOMETRY: the library for SYSUDTLIB rev 9 not found - Check message log for possible 7820 errors in library synchronization.

Where is the message log? And is this a permissions issue? or is it something that needs to be installed. The orange book indicates that everything that is needed should be installed, so I really need help figuring out what needs to happen to make it work.

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-20-2015
03:23 PM

04-20-2015
03:23 PM

Re: Fun with Teradata and Geometry

Hi Mike,

Do you know if it is possible to get the area of a polygon that is actually on the earth a real geospatial polygon.

I ask this because for a client I have the Shape File that contains a surface on the earth, its a state of Mexico, is it possible to calculate that area, because I am using the funcion ST_Area and the result is '0.03' which it is obviously wrong.

Thanks for any help!