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*>