search

airbytehq / airbyte

The leading data integration platform for ETL / ELT data pipelines from APIs, databases & files to data warehouses, data lakes & data lakehouses. Both self-hosted and Cloud-hosted.
https://airbyte.com
Other
15.41k stars 3.98k forks source link

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

Open kevinob11 opened 3 years ago

kevinob11 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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.