How Fivetran Syncs Native PostgreSQL Geometric Data Types

Question

How does Fivetran handle native PostgreSQL geometric data types during sync?

Environment

Connector: PostgreSQL

Answer

Fivetran supports the following native PostgreSQL geometric data types:

• point
• line (infinite line)
• lseg (line segment)
• box
• path
• polygon
• circle

These are built-in PostgreSQL types (not PostGIS types) and are stored in the destination as JSON. Where possible, Fivetran uses the GeoJSON specification for naming and structure. For types not covered by the GeoJSON standard, we use custom extensions to preserve all source information.

Canonical JSON representations

• point: A single two-dimensional location

  {
    "type": "Point",
    "coordinates": [<x>, <y>]
  }
  

• line: An infinite line defined by the equation ax + by + c = 0

  {
   "type": "InfiniteLine",
   "coefficients": [<a>, <b>, <c>]
  }
  

• lseg: Finite line defined by two endpoints

  {
    "type": "LineString",
    "coordinates": [
        [<x1>, <y1>],
        [<x2>, <y2>]
    ]
  }
  

• path: An ordered sequence of points, which may be open or closed

  {
    "type": "MultiLineString",
    "closed": <true|false>,
    "coordinates": [
      [<x1>, <y1>],
      [<x2>, <y2>],
      ...
    ]
  }
  

• box: A rectangular envelope with corners ordered as: top-right, bottom-right, bottom-left, top-left

  {
    "type": "Polygon",
    "coordinates": [
      [<x_tr>, <y_tr>],
      [<x_br>, <y_br>],
      [<x_bl>, <y_bl>],
      [<x_tl>, <y_tl>]
    ]
  }
  

• polygon: A closed shape defined by a sequence of vertices

  {
    "type": "Polygon",
    "coordinates": [
      [
        [<x1>, <y1>],
        [<x2>, <y2>],
        ...
        [<x1>, <y1>]
      ]
    ]
  }
  

• circle: A circle defined by its center point and radius

  {
    "type": "Circle",
    "centerCoordinates": [<x>, <y>],
    "radius": <r>
  }