wkt geometry in cvs file

[michelespinella]

Hi all,

   I have a csv file in this format:

WKT;X;Y;gml_id;

POINT(18.167615 40.353684);18.167615;40.353684;IN.NUMERICIVICI.2012.5091276

POINT(18.160047 40.350344);18.160047;40.350344;IN.NUMERICIVICI.2012.5091295

(this is only a small set)

When I have created the viewmodel I set type geometry for field "WKT", then I have deployed the vdb but when I query the "WKT" field, there is this error:

java.sql.SQLException: org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30176 Could not convert value for column "WKT" in the row ending on text line 1

The wkt format is correct I have to something particular? I am using teiid 9.3.7, designer 11, eclipse neon, jdk 1.8

Thank in advance

Michele Spinella

[shawkins]

Does your texttable indicate that there is a header line?  It looks like the header is being processed as data.

[michelespinella]

I have deleted the header, but I get the same error. Using the WKT format is correct or I have to use a binary format such as WKB? The geometry format in teiid is based on Base64Binary.

Thank you in advance

[shawkins]

Please show the texttable sql that you are using.

[michelespinella]

Here the texttable

SELECT

A.WKT, A.X, A.Y, A.gml_id

FROM

(EXEC CsvSourceModel.getTextFiles('civicilecceogc.csv')) AS f, TEXTTABLE(f.file COLUMNS WKT geometry, X string, Y string, gml_id string DELIMITER ';') AS A

Thank you

Michele

[shawkins]

I should have realized this initially - since there is not built-in string to geometry cast this requires the use of a function:

SELECT

ST_GEOMFROMTEXT(A.WKT), A.X, A.Y, A.gml_id

FROM

(EXEC CsvSourceModel.getTextFiles('civicilecceogc.csv')) AS f, TEXTTABLE(f.file COLUMNS WKT string, X string, Y string, gml_id string DELIMITER ';') AS A

It would be better if the text table logic checked to see if there is a valid cast from string to the given type in column list and produced a better validation warning.  Please log an issue to improve that: - JBoss Issue Tracker