Issues with Import of GeoSpatial Data into HANA

[JWiseman]

hello all, we are trying to import a delimited file which contains point data. i've gone through a bunch of other blogs and discussions and the tips and info there were not enough to resolve our issue. apologies in advance if a solution has been posted elsewhere that i missed.

we've done the following on both rev 70 and rev 73 in a CAL / AWS environment. hana studio revs match the corresponding hana rev.

A) this works...using a create & insert statements...however, the file that we need to load is about 330,000 records so this is really not an option.

     create column table TEST01 (ROW_ID SMALLINT, LL_POINT ST_POINT(4326));

     insert into TEST01 values (1, new ST_POINT(-160.1533502,60.9289163));
     insert into TEST01 values (2, new ST_POINT(-146.5998672,64.6908321));
     insert into TEST01 values (3, new ST_POINT(-131.0855253,55.4118524));
     insert into TEST01 values (4, new ST_POINT(-135.3326235,57.1425084));
     insert into TEST01 values (5, new ST_POINT(-132.0403254,56.2791205));

     select ROW_ID, LL_POINT.ST_AsGeoJSON() from TEST01; -- select statement is okay as well

B) import to a new table fails.

tried importing the following data, in tab delimited format, into a new table TEST02 and using ST_POINT as the Data Type for the 2nd column. 2nd column should be the wkt format as per the spatial ref guide.

ROW_ID LL_POINT

1 POINT(-160.1533502 60.9289163)

2 POINT(-146.5998672 64.6908321)

3 POINT(-131.0855253 55.4118524)

4 POINT(-135.3326235 57.1425084)

5 POINT(-132.0403254 56.2791205)

6 POINT(-87.7460665 30.6592183)

the error that is produced at the end of the import workflow is the following.

Could not create table:SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "null": line 3 col 22 (at pos 90): SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "null": line 3 col 22 (at pos 90): SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "null": line 3 col 22 (at pos 90)

com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "null": line 3 col 22 (at pos 90)

the data preview did look okay in this case.

C) import to an existing table fails

if i create a new table via the create syntax in method (A) above and try to import the tab delimited data to that existing TEST03 table, mapping One to One, an error is produced right after pressing Next in the Define Import Properties button.

An error has occurred. See error log for more details.

java.lang.NullPointerException

there are errors after each step in the import workflow and another is

com.sap.ndb.studio.bi.filedataupload.exceptions.InvalidTargetColumnInformationDataException

in the data preview at the end of the import workflow, only data from the ROW_ID field shows up. this differs from method (B) above in which both fields displayed okay.

question...does anyone have any steps that they have found to be successful for an Import of Point data? and / or, is there something that i'm doing wrong in the above process?

thanks,

jamie

[former_member223209 · Accepted solution]

Hi Jamie,

I am also facing same issue,

if you got some solution kindly advice,

Thanks

Bhupender

[JWiseman · Accepted solution]

hi Bhupender,

i spoke to someone in sap hana product group (geo spatial) about this issue. you have to use an import script, importing the data from files located on the hana server.

the hana studio > file menu > import method will not work for importing geo-spatial data. this is the method that i was trying.

if you haven't used the above mentioned import of files located on the hana server, this video on the hana academy should help.

-jamie

[former_member223209 · Accepted solution]

Hi Jamie,

Thanks for quick reply, Ya i was trying from studio > file menu > import.

i will try importing using ctl file.

Thanks

Bhupender

[former_member223209 · Accepted solution]

Hi Jamie,

I tried importing ST_POINT data using ctl file, I am still facing problem in importing, The error which i am getting is as shown below (in .err file)

inconsistent datatype: Well-known Text deserialization failed. Reason: No Geometry type specified!Position: 4. Of input: 63.0, 14.1

"CS0003",9500545458,"DS1","Pantaloons","63.0, 14.1","PRM0001","28:00.0","You have  30% off on jeans @ pantaloons",

in place of "63.0,14.1" I tried using "new ST_POINT(63.0,14.1) " but still i am getting error,

Kindly suggest how to write ST_POINT points in .csv files.

Thanks

Bhupender

[JWiseman · Accepted solution]

hi Bhupender,

here's what worked for me. first my data in the csv file looks like this...note that there is no header row in my csv as the field names are created in the script.

1	POINT(-160.1533502 60.9289163)
2	POINT(-146.5998672 64.6908321)
3	POINT(-131.0855253 55.4118524)
4	POINT(-135.3326235 57.1425084)
5	POINT(-132.0403254 56.2791205)
6	POINT(-87.7460665 30.6592183)

my ctl file is as follows...

import data

into table "GEO"."TEST"

from 'TEST.csv'

  record delimited by '\n'

  field delimited by '\t'

  optionally enclosed by '"'

error log 'GEO_TEST.err'

and the script to run in studio is below...there is no SRID specified in the st_point definition so it will default to 0.

CREATE COLUMN TABLE "GEO"."TEST" (

  "ROW_ID" INTEGER CS_INT

  "COORDINATES_POINT", ST_POINT,

  PRIMARY KEY ("ROW_ID")

);

IMPORT FROM '/usr/sap/MV1/HDB00/work/GEO/GEO_TEST.ctl';

the Open Data Preview from studio is this...

i hope that this helps,

jamie

[former_member223209 · Accepted solution]

Hi Jamie,

thank you so much sir it worked.

thanks

Bhupender