Load Geospatial Data in Data Pipelines

data pipeline loading supports the load of geospatial data for use with {{ocientgeo}} functionality in the {{ocienthyperscaledatawarehouse}} use geospatial functions to manipulate this data for details, see geospatial functions docid ncku w2ufneog xx3esf supported geospatial functions these geospatial functions are supported for data pipeline loading polygon constructors docid\ bhntklcaesnevkgnxutqb point constructors docid\ c aaugo19qr znvjihgsy or point constructors docid\ c aaugo19qr znvjihgsy point constructors docid\ c aaugo19qr znvjihgsy point constructors docid\ c aaugo19qr znvjihgsy linestring constructors docid 276iafpj50xtsav3j0fte linestring constructors docid 276iafpj50xtsav3j0fte linestring constructors docid 276iafpj50xtsav3j0fte linestring constructors docid 276iafpj50xtsav3j0fte polygon constructors docid\ bhntklcaesnevkgnxutqb polygon constructors docid\ bhntklcaesnevkgnxutqb polygon constructors docid\ bhntklcaesnevkgnxutqb or polygon constructors docid\ bhntklcaesnevkgnxutqb polygon constructors docid\ bhntklcaesnevkgnxutqb polygon constructors docid\ bhntklcaesnevkgnxutqb 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 point constructors docid\ c aaugo19qr znvjihgsy , linestring constructors docid 276iafpj50xtsav3j0fte , or polygon constructors docid\ bhntklcaesnevkgnxutqb 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 polygon constructors docid\ bhntklcaesnevkgnxutqb 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 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 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 point constructors docid\ c aaugo19qr znvjihgsy function with the longitude and latitude coordinates 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 — {{timekey}} with daily time bucket as a timestamp row number — variable length string point — point object linestring — linestring object polygon — polygon object 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 polygon constructors docid\ bhntklcaesnevkgnxutqb function and then reorient the resulting polygon counter clockwise for the exterior and clockwise for the interior by using the polygon constructors docid\ bhntklcaesnevkgnxutqb function during the load, the ocient system automatically casts the wkt formatted data in the three columns to the corresponding geospatial data types 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 docid ncku w2ufneog xx3esf load data docid\ xq0tg7yph vn62uwufibu