Can't export Teradata Geospatial ST_Geometry to KML

Extensibility
Teradata Employee

Can't export Teradata Geospatial ST_Geometry to KML

I have a problem using the TDGeoExport tool V1.6 with Teradata express 13.0. I am trying to export some locations (ST_Geometry points) from a Teradata database to KML format for visualising on Google Earth.

The TDGeoExport running script is as follows:-

java -Xms256m -Xmx1024m  -classpath .;"c:\Program Files\Teradata\Tdat\LTDBMS\bin\terajdbc4.jar";"c:\Program Files\Teradata\Tdat\LTDBMS\bin\tdgssjava.jar";
"c:\Program Files\Teradata\Tdat\LTDBMS\bin\tdgssconfig.jar";. com.teradata.geo.TDGeoExport -l localTD/tduser,tduser -s geospatial
-t buses_sample -f "KML" -o d:\data\buses

I have already granted all privilages to the tduser on the buses_sample table as follows:-

GRANT UDTUSAGE ON SYSUDTLIB TO tduser WITH GRANT OPTION
; GRANT EXECUTE FUNCTION ON SYSSPATIAL TO tduser WITH GRANT OPTION
; GRANT SELECT ON SYSSPATIAL TO tduser WITH GRANT OPTION
; GRANT EXECUTE PROCEDURE ON SYSSPATIAL to tduser WITH GRANT OPTION
;

The buses_sample is a table as follows: -

CREATE SET TABLE Geospatial.buses_sample ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID_COL INTEGER GENERATED ALWAYS AS IDENTITY
           (START WITH 1
            INCREMENT BY 1
            MINVALUE -2147483647
            MAXVALUE 2147483647
            NO CYCLE),
      object_id SMALLINT NOT NULL,
      trajectory_id SMALLINT,
      mov_date DATE FORMAT 'dd/mm/yy',
      mov_time TIME(6) FORMAT 'HH:MI:SS',
      mov_lat DECIMAL(38,6),
      mov_long DECIMAL(38,6),
      mov_x DECIMAL(38,6),
      mov_y DECIMAL(38,6),
      geo_location SYSUDTLIB.ST_Geometry)
UNIQUE PRIMARY INDEX ( ID_COL );

There dump I'm getting from the geospatial export application is...

Coordinate System defaulting to WGS84.
Logon        = localTD/tduser,tduser
DatabaseName = GEOSPATIAL
Table Name   = BUSES_SAMPLE
Export to    = D:\DATA\BUSES\BUSES_SAMPLE.kml\BUSES_SAMPLE.kml
File Format  = KML
-------------------------------------------
connecting to localTD ... connected!
-------------------------------------------
exporting table BUSES_SAMPLE to layer BUSES_SAMPLE ...
java.sql.SQLException: GEOSPATIAL.BUSES_SAMPLE has below un-supported data types
:
 Column geo_location is OTHER

        at com.teradata.geo.TabDef.getOFTTypes(TabDef.java:201)
        at com.teradata.geo.TabDef.<init>(TabDef.java:86)
        at com.teradata.geo.TDGeoExport.Processtable(TDGeoExport.java:468)
        at com.teradata.geo.TDGeoExport.main(TDGeoExport.java:718)
Exporting data failed.
===========================================
Data exporting failed!

Any clue why the export tool does not recognise my column as a normal ST_Geometry and considers it as "Other" data-type?

3 REPLIES

Re: Can't export Teradata Geospatial ST_Geometry to KML

Hi,

I am running exactly into the above issue, can any one plesae provide the solution. Thank you.

Teradata Employee

Re: Can't export Teradata Geospatial ST_Geometry to KML

I looked into the issue, but I could able to load data successfully..

I have created table as shown in this question and inserted one row as follows.

 CREATE SET TABLE buses_sample ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      ID_COL INTEGER GENERATED ALWAYS AS IDENTITY

           (START WITH 1

            INCREMENT BY 1

            MINVALUE -2147483647

            MAXVALUE 2147483647

            NO CYCLE),

      object_id SMALLINT NOT NULL,

      trajectory_id SMALLINT,

      mov_date DATE FORMAT 'dd/mm/yy',

      mov_time TIME(6) FORMAT 'HH:MI:SS',

      mov_lat DECIMAL(38,6),

      mov_long DECIMAL(38,6),

      mov_x DECIMAL(38,6),

      mov_y DECIMAL(38,6),

      geo_location SYSUDTLIB.ST_Geometry)

UNIQUE PRIMARY INDEX ( ID_COL );

 *** Table has been created.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

ins into buses_sample values(1,1,1,'21/02/2014','14:10:10',100.0,200.0,300.0,400

.0,new ST_Geometry('POINT(21.45 88.12)'))

;

ins into buses_sample values(1,1,1,'21/02/2014','14:10:10',100.0,200.0,300.

0,400.0,new ST_Geometry('POINT(21.45 88.12)'))

;

 *** Insert completed. One row added.

 *** Warning: 5789 Value for Identity Column is replaced by a system-generated n

umbe

r.

 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

sel * from buses_sample;

sel * from buses_sample;

 *** Query completed. One row found. 10 columns returned.

 *** Total elapsed time was 1 second.

     ID_COL object_id trajectory_id mov_date mov_time

----------- --------- ------------- -------- -------- ---------------------

          1         1             1 21/02/14 14:10:10

 BTEQ -- Enter your SQL request or BTEQ command:

I ran the export sample as follows and data is exported properly as follows:

D:\anuguraju\anuguraju\GeoSpatial\TestEnv>java -Xms256m -Xmx1024m  -classpath .;

terajdbc4.jar;tdgssjava.jar;tdgssconfig.jar; com.teradata.geo.TDGeoExport -l hyv

m20/geouser,geouser -t buses_sample -f "KML" -o D:\anuguraju\anuguraju\GeoSpatia

l\TestEnv\bus

Coordinate System defaulting to WGS84.

Logon        = hyvm20/geouser,****

DatabaseName = geouser

Table Name   = BUSES_SAMPLE

Export to    = D:\ANUGURAJU\ANUGURAJU\GEOSPATIAL\TESTENV\BUS\BUSES_SAMPLE.kml\BU

SES_SAMPLE.kml

File Format  = KML

-------------------------------------------

connecting to hyvm20 ... connected!

-------------------------------------------

exporting table BUSES_SAMPLE to layer BUSES_SAMPLE ...

(1/1)

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

Data exporting finished successfully!

Exporting time is 20.058

D:\anuguraju\anuguraju\GeoSpatial\TestEnv>dir bus

 Volume in drive D has no label.

 Volume Serial Number is 7AD8-A861

 Directory of D:\anuguraju\anuguraju\GeoSpatial\TestEnv\bus

02/21/2014  03:15 PM    <DIR>          .

02/21/2014  03:15 PM    <DIR>          ..

02/21/2014  03:15 PM             1,476 BUSES_SAMPLE.kml

               1 File(s)          1,476 bytes

               2 Dir(s)  61,979,557,888 bytes free

Contents of the data file are as follows (BUSES_SAMPLE.kml):

<?xml version="1.0" encoding="utf-8" ?>

<kml xmlns="http://www.opengis.net/kml/2.2">

<Document><Folder><name>BUSES_SAMPLE</name>

<Schema name="BUSES_SAMPLE" id="BUSES_SAMPLE">

                <SimpleField name="Name" type="string"></SimpleField>

                <SimpleField name="Description" type="string"></SimpleField>

                <SimpleField name="ID_COL" type="int"></SimpleField>

                <SimpleField name="object_id" type="int"></SimpleField>

                <SimpleField name="trajectory_id" type="int"></SimpleField>

                <SimpleField name="mov_date" type="string"></SimpleField>

                <SimpleField name="mov_time" type="string"></SimpleField>

                <SimpleField name="mov_lat" type="string"></SimpleField>

                <SimpleField name="mov_long" type="string"></SimpleField>

                <SimpleField name="mov_x" type="string"></SimpleField>

                <SimpleField name="mov_y" type="string"></SimpleField>

</Schema>

  <Placemark>

                <ExtendedData><SchemaData schemaUrl="#BUSES_SAMPLE">

                                <SimpleData name="ID_COL">1</SimpleData>

                                <SimpleData name="object_id">1</SimpleData>

                                <SimpleData name="trajectory_id">1</SimpleData>

                                <SimpleData name="mov_date">2014/02/21</SimpleData>

                                <SimpleData name="mov_time">14:10:10</SimpleData>

                                <SimpleData name="mov_lat">100.000000</SimpleData>

                                <SimpleData name="mov_long">200.000000</SimpleData>

                                <SimpleData name="mov_x">300.000000</SimpleData>

                                <SimpleData name="mov_y">400.000000</SimpleData>

                </SchemaData></ExtendedData>

      <Point><coordinates>21.45,88.12</coordinates></Point>

  </Placemark>

</Folder></Document></kml>

Teradata Employee

Re: Can't export Teradata Geospatial ST_Geometry to KML

I used 15 TD database.

sel * from dbc.dbcinfo;

 *** Query completed. 3 rows found. 2 columns returned.

 *** Total elapsed time was 1 second.

InfoKey                        InfoData

------------------------------ --------------------------------------------

VERSION                        15i.00.00.17

LANGUAGE SUPPORT MODE          Standard

RELEASE                        15i.00.00.17