User GuidesAPI ReferenceRelease Notes
Doc HomeHelp CenterLog In

Working with Transformations for Geospatial Data

Statements, expressions, and functions to use for comparing and converting data with geospatial types.

Transforming Geospatial Data

You can use the following statements, expressions, and functions to transform your geospatial records.

Comparison and Conversion Expressions

You can:

  • Compare geospatial records with matching geospatial data types, such as when both records are of geospatial type point.
  • Construct records in geospatial data types, such as point , lineString, and polygon by taking their coordinates as inputs. For more information, see GIS-POINT and other functions in this list that allow constructing geospatial record types.
  • Calculate the area or the perimeter.

You can run the following comparison operations on geospatial data:

  • EQUALITY
  • IS (NOT) NULL/EMPTY
  • hash
  • Conversion functions

For a complete list of supported functions, see GIS Functions.

Accessing Values in a Nested Data Structure

Geospatial data is represented using a nested or compound structure, identified as the RECORD data type. You can use transformations to access specific sub-columns in a column with compound structure. The syntax for accessing the sub-column uses the -> operator between the name of the column and the sub-column.

In the example that follows, your transformation uses a case expression to fill in the geometry_type unified attribute with a specific type of geometry (point, polygon, and so on).

SELECT *, CASE
    WHEN geometry->point IS NOT NULL THEN 'Point'
    WHEN geometry->multiPoint IS NOT NULL THEN 'MultiPoint'
    WHEN geometry->lineString IS NOT NULL THEN 'LineString'
    WHEN geometry->multiLineString IS NOT NULL THEN 'MultiLineString'
    WHEN geometry->polygon IS NOT NULL THEN 'Polygon'
    WHEN geometry->multiPolygon IS NOT NULL THEN 'MultiPolygon'
    ELSE NULL
END AS geometry_type;

Examples

The following examples show some of the statements you can use on records with geospatial data types.

In this list, geo* denotes one of the supported geospatial data types.

SELECT <geo*> as <myColumn>
GROUP BY <geo*>
GROUP min(<geo*>) as min, max(<geo*>) as max
GROUP top(<geo*>, 5) as <value> by …
WINDOW for all the the GROUP BY cases
WINDOW … ORDER BY <geo*> RANGE/ROWS …
MERGE BY <geo*>
FILTER <geo*> IS (NOT) NULL/EMPTY
ORDER BY <geo*> ASC/DESC NULLS FIRST/LAST
LEFT/RIGHT/OUTER/INNER JOIN WITH <myTable> ON <geo*> = table.<geo*>
PARTITION BY <geo*>