---
title: Load Geospatial Data in Data Pipelines
slug: 5mTe-load-geospatial-data-in-data-pipelines
description: Data pipeline loading in Ocient supports geospatial data for use with the OcientGeo functionality. It includes functions like `ST_POINT`, `ST_LINESTRING`, and `ST_POLYGON`.
docTags: 
createdAt: Tue Nov 05 2024 15:12:02 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:42 GMT+0000 (Coordinated Universal Time)
---

Data pipeline loading supports the load of geospatial data for use with OcientGeo™ functionality in the Ocient Data Intelligence Platform™. Use geospatial functions to manipulate this data. For details, see [Geospatial Functions](<././SQL Reference/Geospatial Functions.md>).

# Supported Geospatial Functions&#x20;

These geospatial functions are supported for data pipeline loading:

- [ST\_FORCECCW](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>) &#x20;
- [ST\_POINT](<././SQL Reference/Geospatial Functions/Point Constructors.md>) or [ST\_MAKEPOINT](<././SQL Reference/Geospatial Functions/Point Constructors.md>)&#x20;
- [ST\_POINTFROMTEXT](<././SQL Reference/Geospatial Functions/Point Constructors.md>)&#x20;
- [ST\_POINTFROMEWKT](<././SQL Reference/Geospatial Functions/Point Constructors.md>)
- [ST\_LINESTRING](<././SQL Reference/Geospatial Functions/Linestring Constructors.md>)
- [ST\_LINEFROMTEXT](<././SQL Reference/Geospatial Functions/Linestring Constructors.md>)
- [ST\_MAKELINE](<././SQL Reference/Geospatial Functions/Linestring Constructors.md>)&#x20;
- [ST\_LINEFROMEWKT](<././SQL Reference/Geospatial Functions/Linestring Constructors.md>)
- [ST\_POLYGON(char)](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>)
- [ST\_POLYGON(geo)](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>)
- [ST\_POLYGON](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>) or [ST\_MAKEPOLYGON](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>)
- [ST\_POLYGONFROMTEXT](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>)
- [ST\_POLYGONFROMEWKT](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>)

# Data Pipeline Loading of Geospatial Data Considerations

## Auto-Casting Behavior of Source Data Types

The Ocient® System automatically casts geospatial data from the `CHAR` type to `POINT`, `LINESTRING`, or `POLYGON` types using the [ST\_POINTFROMTEXT](<././SQL Reference/Geospatial Functions/Point Constructors.md>), [ST\_LINEFROMTEXT](<././SQL Reference/Geospatial Functions/Linestring Constructors.md>), or [ST\_POLYGONFROMTEXT](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>) functions, respectively. The data must be in WKT format for the casting to work. If your data is not in the WKT format, use one of the supported geospatial functions to transform your data to the required type.

## Point Data Normalization During Data Pipeline Loading

During loading, the Ocient System automatically performs normalization of point data into a regular format used within Ocient. The Ocient System performs these operations on point data during the load:

- Constrain longitude to \[-180, 180] and latitude to \[-90, 90].
- Wrap around invalid coordinates using correct geographical handling.
- Snap points near the pole to the pole.
- Set the longitude of points on the pole to 0.
- Remove signed zeros from coordinates, so -0 becomes 0.

## Polygon Rotation Convention

The Ocient System has a standardized polygon orientation convention. The standard convention has the polygon with a counterclockwise rotation of the outer polygon ring and a clockwise rotation of the inner polygon ring. Use the [ST\_FORCECCW](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>) function to convert polygons to the standardized convention from systems that interpret polygon rotation differently.&#x20;

## Size Limit for Geospatial Data Types

The maximum allowed size of geospatial data types is 512 MiB.

# Examples of Loading Geospatial Data&#x20;

Use these examples to understand how to create a data pipeline to load geospatial data for different formats (point and WKT data).

## Load Geospatial Data Using Point Data

Create the target table `cities` using the `CREATE TABLE` SQL statement with these columns:

- `id` — Integer
- `name` — Variable-length string
- `location` — Numeric point

```sql
CREATE TABLE cities (
    id INT,
    name VARCHAR,
    location ST_POINT
);
```

Create the `cities` data pipeline using the `CREATE PIPELINE` SQL statement for an Amazon® Web Services℠ (AWS℠) S3 source with bucket `ocient-docs`, filter for the folder `metabase_samples/jsonl/cities.jsonl`, and region `us-east-1`. Use the JSON format to load a JSON file into the `cities` table. Specify a numeric point using the [ST\_POINT](<././SQL Reference/Geospatial Functions/Point Constructors.md>) function with the longitude and latitude coordinates.

```sql
CREATE PIPELINE cities
    SOURCE S3
        BUCKET 'ocient-docs'
        FILTER 'metabase_samples/jsonl/cities.jsonl'
        REGION 'us-east-1'
    EXTRACT
        FORMAT json
INTO cities
SELECT
    $id AS id,
    $name AS name,
    ST_POINT($longitude, $latitude) AS location;
```

## Load Geospatial Data in WKT Format

Create the target table `geospatial_table` using the `CREATE TABLE` SQL statement with these non-nullable columns:

- `ts` — TimeKey® with daily time bucket as a timestamp
- `row_number` — Variable-length string
- `point` — POINT object
- `linestring` — LINESTRING object
- `polygon` — POLYGON object

```sql
CREATE TABLE geospatial_table(
    ts TIMESTAMP TIME KEY BUCKET(1, DAY) NOT NULL,
    row_number INT NOT NULL,
    point ST_POINT NOT NULL,
    linestring ST_LINESTRING NOT NULL,
    polygon ST_POLYGON NOT NULL
);
```

Create the `geospatial_pipeline` data pipeline using the `CREATE PIPELINE` SQL statement for an S3 source with bucket `ocient-docs`, filter for the folder `gis_small/gis_types/*.csv`, and region `us-east-1`. Use the CSV format to load a CSV file into the `geospatial_table` table. The file contains geospatial data in WKT format in the sixth, ninth, and twelfth columns with POLYGON, LINESTRING, and POINT data, respectively. Specify the polygon data in WKT format by using the [ST\_POLYGONFROMTEXT](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>) function and then reorient the resulting polygon counter-clockwise for the exterior and clockwise for the interior by using the [ST\_FORCECCW](<././SQL Reference/Geospatial Functions/Polygon Constructors.md>) function.

During the load, the Ocient System automatically casts the WKT-formatted data in the three columns to the corresponding geospatial data types.

```sql
CREATE PIPELINE geospatial_pipeline
    SOURCE s3
        BUCKET 'ocient-docs'
        FILTER 'gis_small/gis_types/*.csv'
        REGION 'us-east-1'
    EXTRACT
        FORMAT csv
INTO geospatial_table
SELECT
    $1 AS ts,
    $2 AS row_number,
    $12 AS point,
    $9 AS linestring,
    ST_FORCECCW(ST_POLYGONFROMTEXT($6)) AS polygon;
```

# Related Links

[Geospatial Functions](<././SQL Reference/Geospatial Functions.md>)&#x20;

[Load Data](<././Load Data.md>)&#x20;

