You can use the following statements, expressions, and functions to transform your geospatial records.
- 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.
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 over 1 year ago