Parse error using ST_GeomFromText

Molly

Hi all,

 

I've recently exported some spatial data from SQL using WKT for the shape and copied it into a table in vertica. The table consists of an ID, LocationName, and LocationWKT fields with LocationWKT as a LongVarchar.

 

I tried to run the following:

Select LocationName, ST_GeomFromText(LocationWKT) as SpatialMeasure from dev.AdminLocations;

I got this error:

[VP001][5861] [Vertica][VJDBC](5861) ERROR: Error calling processBlock() in User Function ST_GeomFromText at [src/GeomFromText_1arg.cpp:80], error code: 0, message: ParseException: Expected word but encountered end of stream java.lang.RuntimeException: com.vertica.support.exceptions.NonTransientException: [Vertica][VJDBC](5861) ERROR: Error calling processBlock() in User Function ST_GeomFromText at [src/GeomFromText_1arg.cpp:80], error code: 0, message: ParseException: Expected word but encountered end of stream

 

I'm new to Vertica but not SQL and from looking at the documentation on ST_GeomFromText, my syntax is right. But it seems like there's something I'm missing. Any ideas? I haven't found any other info on this error to help me figure out what I need to change. I have looked through the 7.2 documentation (we're running 7.1 right now) and every example of this function is in an insert statement. I know the geometry data type can't change once it's been set, so could this be causing issues with a select? Does Vertica see my script as trying to change the data type? Thanks for any thoughts on this!

 

Molly

Sunil_V

Hi Molly,

 

Geometry and Geography are data types in Vertica. It seems you are trying to access them using JDBC. Geometry and Geography data types of designed to execute spatial functions in Vertica, to access them in a client we recommend you to access them in text format. Also we recommend storing the data types as Geometry/Geography rather than as WKT directly, which seems to be the approach you have taken.

 

For example, you can refer to following document that describes loading data to Geometry/Geography data type from source files. http://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/Place/LoadingSpatialDataIntoTablesUsingCOPY.htm

 

Thanks

Sunil

Casey_S

Hi Molly,

 

+1 to Sunil's explaination.

 

Just as an add-on I thought I'd provide this option to quickly get all of your WKT data into a new table with a GEOMETRY column:

 

=> CREATE TABLE dev.AdminLocations_geom as SELECT LocationName, ST_GeomFromText(LocationWKT) FROM dev.AdminLocations; 

This way you don't have to reload all of your data. I'm not sure if this helps, but I thought I'd offer it.

 

 

-C