> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Load Geospatial Data in Data Pipelines

export const TimeKey = "TimeKey®";

export const OcientGeo = "OcientGeo™";

export const OcientDataIntelligencePlatform = "OcientAIQ™ Unified Data Platform";

export const Ocient = "Ocient®";

export const AWS = "Amazon® Web Services℠ (AWS℠)";

Data pipeline loading supports the load of geospatial data for use with {OcientGeo} functionality in the {OcientDataIntelligencePlatform}. Use geospatial functions to manipulate this data. For details, see [Geospatial Functions](/geospatial-functions).

## Supported Geospatial Functions

These geospatial functions are supported for data pipeline loading:

* [ST\_FORCECCW](/polygon-constructors#st_forceccw)
* [ST\_POINT](/point-constructors#st_point) or [ST\_MAKEPOINT](/point-constructors#st_makepoint)
* [ST\_POINTFROMTEXT](/point-constructors#st_pointfromtext)
* [ST\_POINTFROMEWKT](/point-constructors#st_pointfromewkt)
* [ST\_LINESTRING](/linestring-constructors#st_linestring)
* [ST\_LINEFROMTEXT](/linestring-constructors#st_linefromtext)
* [ST\_MAKELINE](/linestring-constructors#st_makeline)
* [ST\_LINEFROMEWKT](/linestring-constructors#st_linefromewkt)
* [ST\_POLYGON(char)](/polygon-constructors#st_polygon-char)
* [ST\_POLYGON(geo)](/polygon-constructors#st_polygon-geo)
* [ST\_POLYGON](/polygon-constructors#st_polygon) or [ST\_MAKEPOLYGON](/polygon-constructors#st_makepolygon)
* [ST\_POLYGONFROMTEXT](/polygon-constructors#st_polygonfromtext)
* [ST\_POLYGONFROMEWKT](/polygon-constructors#st_polygonfromewkt)

## 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](/point-constructors), [ST\_LINEFROMTEXT](/linestring-constructors), or [ST\_POLYGONFROMTEXT](/polygon-constructors) 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](/polygon-constructors) function to convert polygons to the standardized convention from systems that interpret polygon rotation differently.

### Size Limit for Geospatial Data Types

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

## Examples of Loading Geospatial Data

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 SQL theme={null}
CREATE TABLE cities (
    id INT,
    name VARCHAR,
    location ST_POINT
);
```

Create the `cities` data pipeline using the `CREATE PIPELINE` SQL statement for an {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](/point-constructors) function with the longitude and latitude coordinates.

```sql SQL theme={null}
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 SQL theme={null}
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](/polygon-constructors) function and then reorient the resulting polygon counter-clockwise for the exterior and clockwise for the interior by using the [ST\_FORCECCW](/polygon-constructors) function.

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

```sql SQL theme={null}
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](/geospatial-functions)

[Load Data](/load-data)