Teradata Express 13 Geospatial Capabilities

Database
Enthusiast

Teradata Express 13 Geospatial Capabilities

Hi,
I am currently trying out Geospatial Capabilities of Teradata 13 on a Windows based version of Teradata. (Express 13).

I am attempting to add in polygons, however its complaining about 31000char limit.
This is the syntax I'm using to insert polygons
INSERT INTO table VALUES ( id, 'POLYGON(x1 y1, x2 y2, x3 y3)');

Except my polygons have a lot of points and is extremely complex, which some of them go into 7million characters. Each of my x and y values is in the following format NNN.NNNNNNN (numbers).

There is no choice in cutting down the decimal, as I require to be as precise as possible.

Is this 31000 char, a limitation of Teradata Express 13 or an actual Limitation of Teradata 13?
Is there any work around to allow me to create such a polygon?

Thanks

NB: The syntax I'm using is taken from page 145 of this document:
http://www.info.teradata.com/eDownload.cfm?itemid=091270004
4 REPLIES
Enthusiast

Re: Teradata Express 13 Geospatial Capabilities

From my research, this is not a limitation of the express version. Its a limitation of VARCHAR.
ST_GEOMETRY appears to accept a CLOB object, anybody know how i can "clobber" the POLYGON part of the insert statement?
Teradata Employee

Re: Teradata Express 13 Geospatial Capabilities

The ST_Geometry type has a number of constructors defined for it, one of which accepts a clob.

The following would insert a VARCHAR

INSERT INTO tt1 values(0, new ST_Geometry('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));

The following would insert a CLOB. The CLOB is a literal, but you could give it a table reference or insert it via JDBC or ODBC as a CLOB.

INSERT INTO tt1 values(0, new ST_Geometry(CAST('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))' AS CLOB)));
Enthusiast

Re: Teradata Express 13 Geospatial Capabilities

I tried casting it as a CLOB but Teradata still doesn't like it being a VARCHAR. Since it first creates varchar then casts it.

Anyways i found my answer by using Assistant's import function, following this guide:
http://developer.teradata.com/applications/articles/large-objects-part-1-loading

The method described there is pretty cumbersome. I really don't understand why they didn't simply defaulted to a CLOB Type like most other Spatial SQL DB's do.

From my calculations, using a VARCHAR won't really give you enough points to really even do something worthwhile.
Teradata Employee

Re: Teradata Express 13 Geospatial Capabilities

The ST_Geometry type will accept two different casts, varchar and clob, so you can use either of the following statements to insert into an st_geometry column.

INSERT INTO tt1 values(0, 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))');

INSERT INTO tt1 values(0, cast('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))' as clob) );

The ST_Geometry type also supports multiple constructor methods. There is one that accepts a varchar and one that accepts a clob, so you can use either of the following two statements.

INSERT INTO tt1 values(0, new ST_Geometry('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));

INSERT INTO tt1 values(0, new ST_Geometry(cast('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))' as clob)) );

The constructor methods also accept WKB via VARBYTE or BLOB.

There is also the ST_GeomFromText UDF that you can use to insert geometry data. You can insert either WKT in varchar or clob or WKB in varbyte or blob form.

For client interaction, the ST_Geometry UDT supports ToSQL and FromSQL transforms in CLOB format, representing the WKT format. If you have a JDBC or ODBC program, you would insert CLOBs into the ST_Geometry object.

All of the above statements work. I tried them out.