Uber H3 is an open-source geospatial system created by Uber Technologies . H3 provides a hierarchical grid system that divides the Earth's surface into hexagons of varying sizes, allowing for easy location-based indexing, search, and analysis.
Hexagons can be created at a single scale, for instance to fill an arbitrary polygon at one resolution (see below). They can also be used to generate a much-smaller number of hexagons at multiple scales. In general, operating on h3 hexagons is much faster than on raw arbitrary geometries, at a cost of some precision. Because each hexagon is exactly the same size, this is particularly advantageous for GPU-accelerated workflows.
A principal advantage of the system is that for a given scale, hexagons are approximately-equal area. This stands in contrast to other subdivision schemes based on longitudes and latitudes or web Mercator map projections.
A second advantage is that with hexagons, neighbors in all directions are equidistant. This is not true for rectangular subdivisions like pixels, whose 8 neighbors are at different distances.
The exact amount of precision lost can be tightly bounded, with the smallest sized hexagons supported being about 1m2. That’s more accurate than most current available data sources, short of survey data.
There are some disadvantages to be aware of. The first is that the world can not actually be divided up completely cleanly into hexagons. It turns out that a few pentagons are needed, and this introduces discontinuities. However the system has cleverly placed those pentagons far away from any land masses, so this is only practically a concern for specific maritime operations.
The second issue is that hexagons at adjacent scales do not nest exactly:
This doesn’t much affect practical operations at any single given scale. But if you look carefully at the California multiscale plot above you will discover tiny discontinuities in the form of gaps or overlaps. These don’t amount to a large percentage of the total area, but nonetheless mean this method is not appropriate when exact counts are required.
Encodes columnar point geometry into a globally-unique h3 cell ID for the specified h3_scale. Scales run from 0 to 15 inclusive, where 0 represents the coarsest resolution and 15 the finest. For details on h3 scales, please see the base library documentation.
This can be applied to literal values:
Or to columnar geographic points:
Note that if you have geographic point data rather than columnar latitude and longitude, you can use the ST_X and ST_Y functions. Also, if you wish to encode the centroids of polygons, such as for building footprints, you can combine this with the ST_CENTROID function.
To retrieve geometric coordinates from an H3 code, two functions are available.
h3ToLat and h3ToLon extract the latitude and longitude respectively, for example:
Given an H3 code, the function h3ToParent is available to find cells above that cell at any hierarchical level. This means that once codes are computed at high resolution, they can be compared to codes at other scales.
Uber's h3 python library provides a wider range of functions than those available above (although at significantly slower performance). The library defaults to generating h3 codes as hexadecimal strings, but can be configured to support BIGINT codes. Please see Uber's documentation for details.
H3 codes can be used in regular joins, including joins in Immerse. They can also be used as aggregators, such as in Immerse custom dimensions. For points which are exactly aligned, such as imports from raster data bands of the same source, aggregating on H3 codes is faster than the exact geographic overlaps function ST_EQUALS
HEAVY.AI supports a subset of object types and functions for storing and writing queries for geospatial definitions.
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 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:
HEAVY.AI provides support for geography objects and geodesic distance calculations, with some limitations.
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.
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.
HEAVY.AI supports the functions listed.
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.
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:
If no SRID is returned, you can set the SRID using ST_SETSRID(column_name, SRID). For example, ST_SETSRID(myPoint, 4326).
| Function | Description |
|---|---|
| Function | Description |
|---|---|
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))
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))
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
ST_Buffer
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:
SELECT ST_Buffer('LINESTRING(0 0, 10 0, 10 10)', 1.0);
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.
Function
Description
ST_TRANSFORM
Returns a geometry with its coordinates transformed to a different spatial reference. Currently, WGS84 to Web Mercator transform is supported. For example:ST_DISTANCE(
ST_TRANSFORM(ST_GeomFromText('POINT(-71.064544 42.28787)', 4326), 900913),
ST_GeomFromText('POINT(-13189665.9329505 3960189.38265416)', 900913)
)
ST_TRANSFORM is not currently supported in projections. It can be used only to transform geo inputs to other functions, such as ST_DISTANCE.
ST_SETSRID
Set the SRID to a specific integer value. For example:
ST_TRANSFORM(
ST_SETSRID(ST_GeomFromText('POINT(-71.064544 42.28787)'), 4326), 900913 )
Function
Description
ST_X
Returns the X value from a POINT column.
ST_Y
Returns the Y value from a POINT column.
ST_XMIN
Returns X minima of a geometry.
ST_XMAX
Returns X maxima of a geometry.
ST_YMIN
Returns Y minima of a geometry.
ST_YMAX
Returns Y maxima of a geometry.
ST_STARTPOINT
Returns the first point of a LINESTRING as a POINT.
ST_ENDPOINT
Returns the last point of a LINESTRING as a POINT.
ST_POINTN
Return the Nth point of a LINESTRING as a POINT.
ST_NPOINTS
Returns the number of points in a geometry.
ST_NRINGS
Returns the number of rings in a POLYGON or a MULTIPOLYGON.
ST_SRID
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.
Function
Description
ST_INTERSECTION
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 ST_INTERSECTION can potentially return points, lines, and polygons from a single intersection operation.
Lower-dimension intersecting features such as points and line strings are returned as very small buffers around those features. If needed, true points can be recovered by applying the ST_CENTROID method to point intersection results. In addition, ST_PERIMETER/2 of resulting line intersection polygons can be used to approximate line length.
Empty/NULL geometry outputs are not currently supported.
Examples:
SELECT ST_Intersection('POLYGON((0 0,3 0,3 3,0 3))', 'POLYGON((1 1,4 1,4 4,1 4))');
SELECT ST_Area(ST_Intersection(poly, 'POLYGON((1 1,3 1,3 3,1 3,1 1))')) FROM tbl;
ST_DIFFERENCE
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 ST_INTERSECTION. Similar post-processing methods can be applied if needed.
Empty/NULL geometry outputs are not currently supported.
Examples:
SELECT ST_Difference('POLYGON((0 0,3 0,3 3,0 3))', 'POLYGON((1 1,4 1,4 4,1 4))');
SELECT ST_Area(ST_Difference(poly, 'POLYGON((1 1,3 1,3 3,1 3,1 1))')) FROM tbl;
ST_UNION
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 ST_INTERSECTION. Similar post-processing methods can be applied if needed.
Empty/NULL geometry outputs are not currently supported.
Examples:
SELECT ST_UNION('POLYGON((0 0,3 0,3 3,0 3))', 'POLYGON((1 1,4 1,4 4,1 4))');
SELECT ST_AREA(ST_UNION(poly, 'POLYGON((1 1,3 1,3 3,1 3,1 1))')) FROM tbl;
Function
Description
ST_DISTANCE
Returns shortest planar distance between geometries. For example:
ST_DISTANCE(poly1, ST_GeomFromText('POINT(0 0)'))
Returns shortest geodesic distance between two points, in meters, if given two point geographies. Point geographies can be specified through casts from point geometries or as literals. For example:
ST_DISTANCE(
CastToGeography(p2),
ST_GeogFromText('POINT(2.5559 49.0083)', 4326)
)
SELECT a.name,
ST_DISTANCE(
CAST(a.pt AS GEOGRAPHY),
CAST(b.pt AS GEOGRAPHY)
) AS dist_meters
FROM starting_point a, destination_points b;
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.
SELECT a.poly_name FROM poly a, point b WHERE ST_DISTANCE(
ST_TRANSFORM(b.location,900913),
ST_TRANSFORM(a.heavyai_geo,900913)
) < 1000;
ST_EQUALS
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 SRID=4326 are compressed by default.
Examples:
SELECT COUNT(*) FROM tbl WHERE ST_EQUALS('POINT(2 2)', pt);
SELECT ST_EQUALS('POLYGON ((0 0,1 0,0 1))', 'POLYGON ((0 0,0 0.5,0 1,1 0,0 0))');
ST_MAXDISTANCE
Returns longest planar distance between geometries. In effect, this is the diameter of a circle that encloses both geometries.For example:
Currently supported variants:
ST_CONTAINS
Returns true if the first geometry object contains the second object. For example:
You can also use ST_CONTAINS to:
Return the count of polys that contain the point (here as WKT):
SELECT count(*) FROM geo1 WHERE ST_CONTAINS(poly1, 'POINT(0 0)');
Return names from a polys table that contain points in a points table:
SELECT a.name FROM polys a, points b WHERE ST_CONTAINS(a.heavyai_geo, b.location);
Return names from a polys table that contain points in a points table, using a single point in WKT instead of a field in another table:
SELECT name FROM poly WHERE ST_CONTAINS(
heavyai_geo, ST_GeomFromText('POINT(-98.4886935 29.4260508)', 4326)
);
ST_INTERSECTS
Returns true if two geometries intersect spatially, false if they do not share space. For example:
SELECT ST_INTERSECTS(
'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))',
'POINT(1 1)'
) FROM tbl;
ST_AREA
Returns the area of planar areas covered by POLYGON and MULTIPOLYGON geometries. For example:
SELECT ST_AREA(
'POLYGON((1 0, 0 1, -1 0, 0 -1, 1 0),(0.1 0, 0 0.1, -0.1 0, 0 -0.1, 0.1 0))'
) FROM tbl;
ST_AREA does not support calculation of geographic areas, but rather uses planar coordinates. Geographies must first be projected in order to use ST_AREA. You can do this ahead of time before import or at runtime, ideally using an equal area projection (for example, a national equal-area Lambert projection). The area is calculated in the projection's units. For example, you might use Web Mercator runtime projection to get the area of a polygon in square meters:
ST_AREA(
ST_TRANSFORM(
ST_GeomFromText(
'POLYGON((-76.6168198439371 39.9703199555959,
-80.5189990254673 40.6493554919257,
-82.5189990254673 42.6493554919257,
-76.6168198439371 39.9703199555959)
)', 4326
),
900913)
)
<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.
ST_PERIMETER
Returns the cartesian perimeter of POLYGON and MULTIPOLYGON geometries. For example:
SELECT ST_PERIMETER('POLYGON(
(1 0, 0 1, -1 0, 0 -1, 1 0),
(0.1 0, 0 0.1, -0.1 0, 0 -0.1, 0.1 0)
)'
)
from tbl;
It will also return the geodesic perimeter of POLYGON and MULTIPOLYGON geometries. For example:
SELECT ST_PERIMETER(
ST_GeogFromText(
'POLYGON(
(-76.6168198439371 39.9703199555959,
-80.5189990254673 40.6493554919257,
-82.5189990254673 42.6493554919257,
-76.6168198439371 39.9703199555959)
)',
4326)
)
from tbl;
ST_LENGTH
Returns the cartesian length of LINESTRING geometries. For example:
SELECT ST_LENGTH('LINESTRING(1 0, 0 1, -1 0, 0 -1, 1 0)') FROM tbl;
It also returns the geodesic length of LINESTRING geographies. For example:
SELECT ST_LENGTH(
ST_GeogFromText('LINESTRING(
-76.6168198439371 39.9703199555959,
-80.5189990254673 40.6493554919257,
-82.5189990254673 42.6493554919257)',
4326)
) FROM tbl;
ST_WITHIN
Returns true if geometry A is completely within geometry B. For example the following SELECT statement returns true:
SELECT ST_WITHIN(
'POLYGON ((1 1, 1 2, 2 2, 2 1))',
'POLYGON ((0 0, 0 3, 3 3, 3 0))'
) FROM tbl;
ST_DWITHIN
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:
SELECT ST_DWITHIN(
'POINT(1 1)',
'LINESTRING (1 2,10 10,3 3)', 2.0
) FROM tbl;
ST_DWITHIN supports geodesic distances between geographies, currently limited to geographic points. For example, you can check whether Los Angeles and Paris, specified as WGS84 geographic point literals, are within 10,000km of one another.
SELECT ST_DWITHIN(
ST_GeogFromText(
'POINT(-118.4079 33.9434)', 4326),
ST_GeogFromText('POINT(2.5559 49.0083)',
4326 ),
10000000.0) FROM tbl;
ST_DFULLYWITHIN
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:
SELECT ST_DFULLYWITHIN(
'POINT(1 1)',
'LINESTRING (1 2,10 10,3 3)',
10.0) FROM tbl;
This function supports:
ST_DFULLYWITHIN(POINT, LINESTRING, distance)
ST_DFULLYWITHIN(LINESTRING, POINT, distance)
ST_DISJOINT
Returns true if the geometries are spatially disjoint (that is, the geometries do not overlap or touch. For example:
SELECT ST_DISJOINT(
'POINT(1 1)',
'LINESTRING (0 0,3 3)'
) FROM tbl;
