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
- Compare geospatial records with matching geospatial data types, such as when both records are of geospatial type
- Construct records in geospatial data types, such as
polygonby 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:
IS (NOT) NULL/EMPTY
- 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
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;
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*>
Updated 7 months ago