How Does Fivetran Sync PostGIS Data Types?
Question
How does Fivetran handle PostGIS data types during sync?
Environment
Connector: PostgreSQL
Answer
Fivetran syncs PostGIS data types (geometry and geography) as JSON objects in the destination, transforming them according to the GeoJSON specification.
geometry
Fivetran supports the primary geometry data type. The following table lists the sub-geometry data types that we support. Fivetran transforms these data types according to the GeoJSON specification and stores them in destinations as JSON types.
| Geometry Type | Fivetran Type |
|---|---|
| Point | JSON |
| LineString | JSON |
| Polygon | JSON |
| MultiPoint | JSON |
| MultiLineString | JSON |
| MultiPolygon | JSON |
| GeometryCollection | JSON |
| CircularString | JSON |
| CompoundCurve | JSON |
| PolyhedralSurface | JSON |
| CurvePolygon | JSON |
| Tin | JSON |
| Triangle | JSON |
JSON representation in destination
The following examples show how geometry subtype values are synced and represented as JSON in the destination.
Point
Source value in PostgreSQL:
ST_GeomFromText('POINT (1 1)', 4326)
Synced JSON value in destination:
{
"type": "Point",
"coordinates": [1.0, 1.0]
}
LineString
Source value in PostgreSQL:
ST_GeomFromText(
'LINESTRING (0 0, 10 10)',
4326
)
Synced JSON value in destination:
{
"type": "LineString",
"coordinates": [
[0.0, 0.0],
[10.0, 10.0]
]
}
Polygon
Source value in PostgreSQL:
ST_GeomFromText(
'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))',
4326
)
Synced JSON value in destination:
{
"type": "Polygon",
"coordinates": [
[
[0.0, 0.0],
[10.0, 0.0],
[10.0, 10.0],
[0.0, 10.0],
[0.0, 0.0]
]
]
}
MultiPoint
Source value in PostgreSQL:
ST_GeomFromText(
'MULTIPOINT ((1 1), (2 2))',
4326
)
Synced JSON value in destination:
{
"type": "MultiPoint",
"coordinates": [
[1.0, 1.0],
[2.0, 2.0]
]
}
MultiLineString
Source value in PostgreSQL:
ST_GeomFromText(
'MULTILINESTRING ((0 0, 5 5), (5 5, 10 10))',
4326
)
Synced JSON value in destination:
{
"type": "MultiLineString",
"coordinates": [
[
[0.0, 0.0],
[5.0, 5.0]
],
[
[5.0, 5.0],
[10.0, 10.0]
]
]
}
MultiPolygon
Source value in PostgreSQL:
ST_GeomFromText(
'MULTIPOLYGON (
((0 0, 5 0, 5 5, 0 5, 0 0))
)',
4326
)
Synced JSON value in destination:
{
"type": "MultiPolygon",
"coordinates": [
[
[
[0.0, 0.0],
[5.0, 0.0],
[5.0, 5.0],
[0.0, 5.0],
[0.0, 0.0]
]
]
]
}
GeometryCollection
Source value in PostgreSQL:
ST_GeomFromText(
'GEOMETRYCOLLECTION (
POINT (1 1),
LINESTRING (0 0, 1 1)
)',
4326
)
Synced JSON value in destination:
{
"type": "GeometryCollection",
"geometries": [
{
"type": "Point",
"coordinates": [1.0, 1.0]
},
{
"type": "LineString",
"coordinates": [
[0.0, 0.0],
[1.0, 1.0]
]
}
]
}
CircularString
Source value in PostgreSQL:
ST_GeomFromText(
'CIRCULARSTRING (0 0, 5 5, 10 0)',
4326
)
Synced JSON value in destination:
{
"type": "CircularString",
"coordinates": [
[0.0, 0.0],
[5.0, 5.0],
[10.0, 0.0]
]
}
CompoundCurve
Source value in PostgreSQL:
ST_GeomFromText(
'COMPOUNDCURVE (
CIRCULARSTRING (0 0, 5 5, 10 0),
LINESTRING (10 0, 15 5)
)',
4326
)
Synced JSON value in destination:
{
"type": "CompoundCurve",
"coordinates": [
[
[0.0, 0.0],
[5.0, 5.0],
[10.0, 0.0]
],
[
[10.0, 0.0],
[15.0, 5.0]
]
]
}
PolyhedralSurface
Source value in PostgreSQL:
ST_GeomFromText(
'CURVEPOLYGON (
CIRCULARSTRING (0 0, 5 5, 10 0, 5 -5, 0 0)
)',
4326
)
Synced JSON value in destination:
{
"type": "PolyhedralSurface",
"coordinates": [
[
[
[0.0, 0.0],
[10.0, 0.0],
[10.0, 10.0],
[0.0, 10.0],
[0.0, 0.0]
]
]
]
}
CurvePolygon
Source value in PostgreSQL:
ST_GeomFromText(
'CURVEPOLYGON (
CIRCULARSTRING (0 0, 5 5, 10 0, 5 -5, 0 0)
)',
4326
)
Synced JSON value in destination:
{
"type": "CurvePolygon",
"coordinates": [
[
[0.0, 0.0],
[5.0, 5.0],
[10.0, 0.0],
[5.0, -5.0],
[0.0, 0.0]
]
]
}
Tin
Source value in PostgreSQL:
ST_GeomFromText(
'TIN (
((0 0, 10 0, 0 10, 0 0))
)',
4326
)
Synced JSON value in destination:
{
"type": "Tin",
"coordinates": [
[
[
[0.0, 0.0],
[10.0, 0.0],
[0.0, 10.0],
[0.0, 0.0]
]
]
]
}
Triangle
Source value in PostgreSQL:
ST_GeomFromText(
'TRIANGLE (
(0 0, 10 0, 0 10, 0 0)
)',
4326
)
Synced JSON value in destination:
{
"type": "Triangle",
"coordinates": [
[
[0.0, 0.0],
[10.0, 0.0],
[0.0, 10.0],
[0.0, 0.0]
]
]
}
geography
Fivetran supports the primary geography data type. The following table lists the sub-geography data types that we support. Fivetran transforms these data types according to the GeoJSON specification and stores them in destinations as JSON types.
| Geography Type | Fivetran Type |
|---|---|
| Point | JSON |
| LineString | JSON |
| Polygon | JSON |
| MultiPoint | JSON |
| MultiLineString | JSON |
| MultiPolygon | JSON |
| GeometryCollection | JSON |
JSON representation in destination
The following examples show how geography subtype values are synced and represented as JSON in the destination.
Point
Source value in PostgreSQL:
ST_GeogFromText(
'SRID=4326;
POINT(-73.9857 40.7484)'
)
Synced JSON value in destination:
{
"type": "Point",
"coordinates": [-73.9857, 40.7484]
}
LineString
Source value in PostgreSQL:
ST_GeogFromText(
'SRID=4326;
LINESTRING(-73.98 40.75, -73.99 40.74, -73.97 40.76)'
)
Synced JSON value in destination:
{
"type": "LineString",
"coordinates": [
[-73.98, 40.75],
[-73.99, 40.74],
[-73.97, 40.76]
]
}
Polygon
Source value in PostgreSQL:
ST_GeogFromText(
'SRID=4326;
POLYGON(
(-73.99 40.75, -73.98 40.75, -73.98 40.74, -73.99 40.74, -73.99 40.75)
)'
)
Synced JSON value in destination:
{
"type": "Polygon",
"coordinates": [
[
[-73.99, 40.75],
[-73.98, 40.75],
[-73.98, 40.74],
[-73.99, 40.74],
[-73.99, 40.75]
]
]
}
MultiPoint
Source value in PostgreSQL:
ST_GeogFromText(
'SRID=4326;
MULTIPOINT(
(-73.9857 40.7484),
(-73.9840 40.7490)
)'
)
Synced JSON value in destination:
{
"type": "MultiPoint",
"coordinates": [
[-73.9857, 40.7484],
[-73.984, 40.749]
]
}
MultiLineString
Source value in PostgreSQL:
ST_GeogFromText(
'SRID=4326;
MULTILINESTRING(
(-73.98 40.75, -73.99 40.74),
(-73.97 40.76, -73.96 40.77)
)'
)
Synced JSON value in destination:
{
"type": "MultiLineString",
"coordinates": [
[
[-73.98, 40.75],
[-73.99, 40.74]
],
[
[-73.97, 40.76],
[-73.96, 40.77]
]
]
}
MultiPolygon
Source value in PostgreSQL:
ST_GeogFromText(
'SRID=4326;
MULTIPOLYGON(
((-73.99 40.75, -73.98 40.75, -73.98 40.74, -73.99 40.74, -73.99 40.75)),
((-73.97 40.76, -73.96 40.76, -73.96 40.75, -73.97 40.75, -73.97 40.76))
)'
)
Synced JSON value in destination:
{
"type": "MultiPolygon",
"coordinates": [
[
[
[-73.99, 40.75],
[-73.98, 40.75],
[-73.98, 40.74],
[-73.99, 40.74],
[-73.99, 40.75]
]
],
[
[
[-73.97, 40.76],
[-73.96, 40.76],
[-73.96, 40.75],
[-73.97, 40.75],
[-73.97, 40.76]
]
]
]
}
GeographyCollection
Source value in PostgreSQL:
ST_GeogFromText(
'SRID=4326;
GEOMETRYCOLLECTION(
POINT(-73.9857 40.7484),
LINESTRING(-73.98 40.75, -73.99 40.74)
)'
)
Synced JSON value in destination:
{
"type": "GeometryCollection",
"geometries": [
{
"type": "Point",
"coordinates": [-73.9857, 40.7484]
},
{
"type": "LineString",
"coordinates": [
[-73.98, 40.75],
[-73.99, 40.74]
]
}
]
}