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

[ozmike · Accepted solution]

ok create a view in source system to convert the GEO to text.

create or replace view MYSCHEMA.MYVIEW as ( select
GEOFIELD.ST_AsText() as GEOFIELDTEXT
from "MYSCHEMA.TABLE_WITH_GEO" ) ;

in target system create VT to connect to view

Once the VT is created

Then select GEOFIELDTEXT.ST_GeomFromText() as GEOFIELD  from VT 

The Geo types have been converted back.

[ozmike]

There are a number of functions which might be of use.

select ll.ST_AsText() as back2text , ll, toText from (
select ST_GeomFromText( totext ) as ll , toText from (
SELECT NEW ST_Point( 1.0, 2.0 ).ST_AsText() as toText FROM dummy));

Not sure of whether accuracy is maintained during the conversion.

So in the source system maybe set up a view (or table ) of the transformed data to text and then connect a VT to this view and then convert back to GEO.

I'll give it a go - answering my own question yet again. 

[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.