Geospatial Capabilities
HEAVY.AI supports a subset of object types and functions for storing and writing queries for geospatial definitions.
Geospatial Datatypes
Type | Size | Example |
---|---|---|
| Variable | A sequence of 2 or more points and the lines that connect them. For example: |
| Variable | A set of one or more polygons. For example: |
| Variable | A point described by two coordinates. When the coordinates are longitude and latitude, HEAVY.AI stores longitude first, and then latitude. For example: |
| Variable | A set of one or more rings (closed line strings), with the first representing the shape (external ring) and the rest representing holes in that shape (internal rings). For example: |
MULTIPOINT | Variable | A set of one or more points. For example: MULTIPOINT((0 0), (1 1), (2 2)) |
MULTILINESTRING | Variable | A set of one or more associated lines, each of two or more points. For example: MULTILINESTRING((0 0, 1 0, 2 0), (0 1, 1 1, 2 1)) |
For information about geospatial datatype sizes, see Storage and Compression in Datatypes.
For more information on WKT primitives, see Wikipedia: Well-known Text: Geometric objects.
HEAVY.AI supports SRID 4326 (WGS 84) and 900913 (Google Web Mercator), and 32601-32660,32701-32760 (Universal Transverse Mercator (UTM) Zones). When using geospatial fields, you set the SRID to determine which reference system to use. HEAVY.AI does not assign a default SRID.
If you do not set the SRID of the geo field in the table, you can set it in a SQL query using ST_SETSRID(column_name, SRID)
. For example, ST_SETSRID(a.pt,4326)
.
When representing longitude and latitude, the first coordinate is assumed to be longitude in HEAVY.AI geospatial primitives.
You create geospatial objects as geometries (planar spatial data types), which are supported by the planar geometry engine at run time. When you call ST_DISTANCE
on two geometry objects, the engine returns the shortest straight-line planar distance, in degrees, between those points. For example, the following query returns the shortest distance between the point(s) in p1
and the polygon(s) in poly1
:
For information about importing data, see Importing Geospatial Data.
Geospatial Literals
Geospatial functions that expect geospatial object arguments accept geospatial columns, geospatial objects returned by other functions, or string literals containing WKT representations of geospatial objects. Supplying a WKT string is equivalent to calling a geometry constructor. For example, these two queries are identical:
You can create geospatial literals with a specific SRID. For example:
Support for Geography
HEAVY.AI provides support for geography objects and geodesic distance calculations, with some limitations.
Exporting Coordinates from Immerse
HeavyDB supports import from any coordinate system supported by the Geospatial Data Abstraction Library (GDAL). On import, HeavyDB will convert to and store in WGS84 encoding, and rendering is accurate in Immerse.
However, no built-in way to reference the original coordinates currently exists in Immerse, and coordinates exported from Immerse will be WGS84 coordinates. You can work around this limitation by adding to the dataset a column or columns in non-geo format that could be included for display in Immerse (for example, in a popup) or on export.
Distance Calculation
Currently, HEAVY.AI supports spheroidal distance calculation between:
Two points using either SRID 4326 or 900913.
A point and a polygon/multipolygon using SRID 900913.
Using SRID 900913 results in variance compared to SRID 4326 as polygons approach the North and South Poles.
The following query returns the points and polygons within 1,000 meters of each other:
See the tables in Geospatial Functions below for examples.
Geospatial Functions
HEAVY.AI supports the functions listed.
Geometry Constructors
Function | Description |
---|---|
| Computes the geometric center of a geometry as a POINT. |
| Return a specified geometry value from Well-known Text representation. |
| Return a specified geometry value from Well-known Text representation and an SRID. |
| Return a specified geography value from Well-known Text representation. |
| Return a specified geography value from Well-known Text representation and an SRID. |
| Return a point constructed on the fly from the provided coordinate values. Constant coordinates result in construction of a POINT literal. Example: |
Geometry to String Conversion
Function | Description |
---|---|
| Converts a geometry input to a Well-Known-Text (WKT) string |
| Converts a geometry input to a Well-Known-Binary (WKB) string |
Geometry Processing
Function | Description |
---|---|
| Returns a geometry covering all points within a specified distance from the input geometry. Performed by the GEOS module. The output is currently limited to the MULTIPOLYGON type. Calculations are in the units of the input geometry’s SRID. Buffer distance is expressed in the same units. Example:
Special processing is automatically applied to WGS84 input geometries (SRID=4326) to limit buffer distortion:
Example: Build 10-meter buffer geometries (SRID=4326) with limited distortion:
|
| Computes the geometric center of a geometry as a POINT. |
FunctionDescription Special processing is automatically applied to WGS84 input geometries (SRID=4326) to limit buffer distortion:
Implementation first determines the best planar SRID to which to project the 4326 input geometry.
Preferred SRIDs are UTM and Lambert (LAEA) North/South zones, with Mercator used as a fallback.
Buffer distance is interpreted as distance in meters (units of all planar SRIDs being considered).
The input geometry is transformed to the best planar SRID and handed to GEOS, along with buffer distance.
The buffer geometry built by GEOS is then transformed back to SRID=4326 and returned.
Example: Build 10-meter buffer geometries (SRID=4326) with limited distortion:SELECT ST_Buffer(poly4326, 10.0) FROM tbl;
.ST_Centroid
Computes the geometric center of a geometry as a POINT.
Geometry Editors
Function | Description |
---|---|
| Returns a geometry with its coordinates transformed to a different spatial reference. Currently, WGS84 to Web Mercator transform is supported. For example:
|
| Set the SRID to a specific integer value. For example:
|
Geometry Accessors
Function | Description |
---|---|
| Returns the X value from a POINT column. |
| Returns the Y value from a POINT column. |
| Returns X minima of a geometry. |
| Returns X maxima of a geometry. |
| Returns Y minima of a geometry. |
| Returns Y maxima of a geometry. |
| Returns the first point of a LINESTRING as a POINT. |
| Returns the last point of a LINESTRING as a POINT. |
| Return the Nth point of a LINESTRING as a POINT. |
| Returns the number of points in a geometry. |
| Returns the number of rings in a POLYGON or a MULTIPOLYGON. |
| Returns the spatial reference identifier for the underlying object. |
ST_NUMGEOMETRIES | Returns the MULTI count of MULTIPOINT, MULTILINESTRING or MULTIPOLYGON. Returns 1 for non-MULTI geometry. |
Overlay Functions
Function | Description |
---|---|
| Returns a geometry representing an intersection of two geometries; that is, the section that is shared between the two input geometries. Performed by the GEOS module. The output is currently limited to MULTIPOLYGON type, because HEAVY.AI does not support mixed geometry types within a geometry column, and Examples:
|
| Returns a geometry representing the portion of the first input geometry that does not intersect with the second input geometry. Performed by the GEOS module. Input order is important; the return geometry is always a section of the first input geometry. The output is currently limited to MULTIPOLYGON type, for the same reasons described in Examples:
|
| Returns a geometry representing the union (or combination) of the two input geometries. Performed by the GEOS module. The output is currently limited to MULTIPOLYGON type for the same reasons described in Examples:
|
Spatial Relationships and Measurements
Function | Description |
---|---|
| Returns shortest planar distance between geometries. For example:
You can also calculate the distance between a POLYGON and a POINT. If both fields use SRID 4326, then the calculated distance is in 4326 units (degrees). If both fields use SRID 4326, and both are transformed into 900913, then the results are in 900913 units (meters). The following SQL code returns the names of polygons where the distance between the point and polygon is less than 1,000 meters.
|
| Returns TRUE if the first input geometry and the second input geometry are spatially equal; that is, they occupy the same space. Different orderings of points can be accepted as equal if they represent the same geometry structure. POINTs comparison is performed natively. All other geometry comparisons are performed by GEOS. If input geometries are both uncompressed or compressed, all comparisons to identify equality are precise. For mixed combinations, the comparisons are performed with a compression-specific tolerance that allows recognition of equality despite subtle precision losses that the compression may introduce. Note: Geo columns and literals with Examples:
|
| Returns longest planar distance between geometries. In effect, this is the diameter of a circle that encloses both geometries.For example: Currently supported variants: |
| Returns true if the first geometry object contains the second object. For example: You can also use
|
| Returns true if two geometries intersect spatially, false if they do not share space. For example:
|
| Returns the area of planar areas covered by POLYGON and MULTIPOLYGON geometries. For example:
<code></code> Web Mercator is not an equal area projection, however. Unless compensated by a scaling factor, Web Mercator areas can vary considerably by latitude. |
| Returns the cartesian perimeter of POLYGON and MULTIPOLYGON geometries. For example:
|
| Returns the cartesian length of LINESTRING geometries. For example:
|
| Returns true if geometry A is completely within geometry B. For example the following
|
| Returns true if the geometries are within the specified distance of each one another. Distance is specified in units defined by the spatial reference system of the geometries. For example:
|
| Returns true if the geometries are fully within the specified distance of one another. Distance is specified in units defined by the spatial reference system of the geometries. For example:
|
| Returns true if the geometries are spatially disjoint (that is, the geometries do not overlap or touch. For example:
|
Additional Geo Notes
You can use SQL code similar to the examples in this topic as global filters in Immerse.
CREATE TABLE AS SELECT
is not currently supported for geo data types in distributed mode.GROUP BY
is not supported for geo types (POINT
,MULTIPOINT
,LINESTRING
,MULTILINESTRING
,POLYGON
, orMULTIPOLYGON
.You can use
\d table_name
to determine if the SRID is set for the geo field:If no SRID is returned, you can set the SRID using
ST_SETSRID(column_name, SRID)
. For example,ST_SETSRID(myPoint, 4326)
.
Last updated