Load Data
Load Geospatial Data in Data Pipelines

Data pipeline loading supports the load of geospatial data for use with ﻿ functionality in the ﻿. Use geospatial functions to manipulate this data. For details, see Geospatial Functions﻿.
Supported Geospatial Functions 
These geospatial functions are supported for data pipeline loading:
﻿ST_FORCECCW﻿  
﻿ST_POINT﻿ or ST_MAKEPOINT﻿ 
﻿ST_POINTFROMTEXT﻿ 
﻿ST_POINTFROMEWKT﻿﻿
﻿ST_LINESTRING﻿﻿
﻿ST_LINEFROMTEXT﻿﻿
﻿ST_MAKELINE﻿ 
﻿ST_LINEFROMEWKT﻿﻿
﻿ST_POLYGON(char)﻿﻿
﻿ST_POLYGON(geo)﻿﻿
﻿ST_POLYGON﻿ or ST_MAKEPOLYGON﻿﻿
﻿ST_POLYGONFROMTEXT﻿﻿
﻿ST_POLYGONFROMEWKT﻿﻿
Data Pipeline Loading of Geospatial Data Considerations
Auto-Casting Behavior of Source Data Types
The ﻿ System automatically casts geospatial data from the CHAR type to POINT, LINESTRING, or POLYGON types using the ST_POINTFROMTEXT﻿, ST_LINEFROMTEXT﻿, or ST_POLYGONFROMTEXT﻿ 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﻿ 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
CREATE TABLE cities (
    id INT,
    name VARCHAR,
    location ST_POINT
);
CREATE TABLE cities (
    id INT,
    name VARCHAR,
    location ST_POINT
);
﻿
Create the cities data pipeline using the CREATE PIPELINE SQL statement for an ﻿ S3 source with bucket ocient-examples, 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﻿ function with the longitude and latitude coordinates.
SQL
CREATE PIPELINE cities
    SOURCE S3
        BUCKET 'ocient-examples'
        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;
CREATE PIPELINE cities
    SOURCE S3
        BUCKET 'ocient-examples'
        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 — ﻿ 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 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-examples, 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﻿ function and then reorient the resulting polygon counter-clockwise for the exterior and clockwise for the interior by using the ST_FORCECCW﻿ 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-examples'
        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;
CREATE PIPELINE geospatial_pipeline
    SOURCE s3
        BUCKET 'ocient-examples'
        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﻿ 
﻿Load Data﻿ 
Data Types for User-Defined Data Pipeline Functions
Load Metadata Values in Data Pipelines