Do Virtual Tables support ST_POINT or ST_GEOMETRY

[ozmike]

Hi 

I have a Virtual Tables connection from one hana data base to another. I am getting the error ST_POINT or ST_GEOMETRY is not supported when I use the the create virtual table statement. Are these types supported in Virtual Tables ? If not what is the work around . I am trying to migrate some data form one hana database to another using virtual tables. 

Thanks

[mfath]

Hej, Virtual Tables do support spatial datatypes, but you can not create a virtual table AND the remote table with a spatial datatype in one statement.

This works if the reomte table contains a spatial datatype:
CREATE VIRTUAL TABLE "schema"."virtual table" AT "remotesys"."<NULL>"."schema"."remote table";

This fails:
CREATE VIRTUAL TABLE "schema"."virtual table" ("ID" BIGINT, "GEO" ST_GEOMETRY(4326))
AT "remotesys"."<NULL>"."schema"."remote table" WITH REMOTE;

So, I guess you need "revrese" your data migration - connecting from the target system to your (remote) source system. The source systems contains the geo data. In the target system, you create a virtual table and select from it.

[ozmike]

Hi I'm using method 1 connecting without - using the column specifications. I'm connecting from the remote to the source which contains the GEO data. CREATE VIRTUAL TABLE "XXX"."VT_HXP_LGA_2017_ZZZ" AT "HANA EXPRESS"."XX"."YYY"."LGA_2017_ZZZ"; Method 1 does no work as you suggest. Error : invalid remote object name: Datatype [ST_GEOMETRY] of column [SHAPE] in table ["XXX"."LGA_2017_ZZZ"] is not supported. Not sure what your suggesting about reversing i'm connecting form the traget to source.

[mfath]

oh, I see you are using a HANA Express (=HANA on-prem) as a remote source. Then you are right - spatial datatypes are not supported and you can workaround as you suggested via converting it to TEXT/WKT and back.