[destination-postgres] postgis doesn't maintain geometry data

[kevinob11]

Expected Behavior

Syncing MSSQL geometry column to a postgres database with postgis should keep the geometry column type

Current Behavior

The geometry column type is switched to varchar and the content is mangled
e.g. POINT (1118476.380824917 747196.45540725021) -> 6F0B0000010CE4BD7D610C1131419C232BE978CD2641

Steps to Reproduce

1. Set up and run sync from geometry column mssql to postgres with postgis

Severity of the bug for you

Medium

Airbyte Version

0.20.0-alpha

Connector Version (if applicable)

MSSQL - 0.2.3
Postgres - 0.2.4

┆Issue is synchronized with this Asana task by Unito

[marcosmarxm]

hey @kevinob11 thanks for reporting this. Another user had a similar problem using geometry data type.
Airbyte uses JDBC data types to map the source types, but I think this can be extended to support specific database types. Right now the fallback is trying to cast the value to Java String and probably convert the wrongly the POINT type.

[kevinob11]

Thanks. For anyone else dealing with this you can convert to VARCHAR cast(Shape.STAsText() as varchar(max)) in a view then sync that view instead, converting back to geometry on the postgres server. I actually did mine in a foreign data wrapper, but I expect this would work with Airbyte as well.