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
LINESTRING	Variable	A sequence of 2 or more points and the lines that connect them. For example: LINESTRING(0 0,1 1,1 2)
MULTIPOLYGON	Variable	A set of one or more polygons. For example:MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))
POINT	Variable	A point described by two coordinates. When the coordinates are longitude and latitude, HEAVY.AI stores longitude first, and then latitude. For example: POINT(0 0)
POLYGON	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: POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
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.

CREATE TABLE simple_geo (
                          name TEXT ENCODING DICT(32), 
                          location GEOMETRY(POINT,4326)
                         );

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:

SELECT ST_DISTANCE(p1, poly1) FROM geo1;

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:

SELECT COUNT(*) FROM geo1 WHERE ST_DISTANCE(p1, `POINT(1 2)`) < 1.0;
SELECT COUNT(*) FROM geo1 WHERE ST_DISTANCE(p1, ST_GeomFromText('POINT(1 2)')) < 1.0;

You can create geospatial literals with a specific SRID. For example:

SELECT ST_CONTAINS(
                     mpoly2, 
                     ST_GeomFromText('POINT(-71.064544 42.28787)', 4326)
                   )
                   FROM geo2;

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:

SELECT a.poly_name, b.pt_name FROM poly a, pt b 
WHERE ST_Distance(
   ST_Transform(b.heavyai_geo, 900913),
   ST_Transform(b.location, 900913))<1000;

See the tables in Geospatial Functions below for examples.

Geospatial Functions

HEAVY.AI supports the functions listed.

Geometry Constructors

Function	Description
ST_Centroid	Computes the geometric center of a geometry as a POINT.
ST_GeomFromText(WKT)	Return a specified geometry value from Well-known Text representation.
ST_GeomFromText(WKT, SRID)	Return a specified geometry value from Well-known Text representation and an SRID.
ST_GeogFromText(WKT)	Return a specified geography value from Well-known Text representation.
ST_GeogFromText(WKT, SRID)	Return a specified geography value from Well-known Text representation and an SRID.
ST_Point(double lon, double lat)	Return a point constructed on the fly from the provided coordinate values. Constant coordinates result in construction of a POINT literal. Example: ST_Contains(poly4326, ST_SetSRID(ST_Point(lon, lat), 4326))

Geometry to String Conversion

Function	Description
ST_AsText(geom) | ST_AsWKT(geom)	Converts a geometry input to a Well-Known-Text (WKT) string
ST_AsBinary(geom) | ST_AsWKB(geom)	Converts a geometry input to a Well-Known-Binary (WKB) string

Geometry Processing

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_CentroidComputes the geometric center of a geometry as a POINT.

Geometry Editors

Geometry Accessors

Overlay Functions

Spatial Relationships and Measurements

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, or MULTIPOLYGON.

• You can use \d table_name to determine if the SRID is set for the geo field:

  heavysql> \d starting_point
  CREATE TABLE starting_point (
                                 name TEXT ENCODING DICT(32),
                                 myPoint GEOMETRY(POINT, 4326) ENCODING COMPRESSED(32)
                               )

  If no SRID is returned, you can set the SRID using ST_SETSRID(column_name, SRID). For example, ST_SETSRID(myPoint, 4326).