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 docid ncku w2ufneog xx3esf supported geospatial functions these geospatial functions are supported for data pipeline loading docid\ bhntklcaesnevkgnxutqb docid\ c aaugo19qr znvjihgsy or docid\ c aaugo19qr znvjihgsy docid\ c aaugo19qr znvjihgsy docid\ c aaugo19qr znvjihgsy docid 276iafpj50xtsav3j0fte docid 276iafpj50xtsav3j0fte docid 276iafpj50xtsav3j0fte docid 276iafpj50xtsav3j0fte docid\ bhntklcaesnevkgnxutqb docid\ bhntklcaesnevkgnxutqb docid\ bhntklcaesnevkgnxutqb or docid\ bhntklcaesnevkgnxutqb docid\ bhntklcaesnevkgnxutqb 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 docid\ c aaugo19qr znvjihgsy , docid 276iafpj50xtsav3j0fte , or 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 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 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 docid\ bhntklcaesnevkgnxutqb function and then reorient the resulting polygon counter clockwise for the exterior and clockwise for the interior by using the 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 docid ncku w2ufneog xx3esf docid\ xq0tg7yph vn62uwufibu