A grid is a way of dividing the surface of the earth into contiguous cells with no gaps in between.
This makes grids very useful for spatial indexing and aggregating.
Spectrum™Location Intelligence for Big Data provides Hive user defined functions (UDF) for hashing that allow you to manage grid cells for a variety of use cases. Hashing is a way of encoding and decoding the grid cell using the cell boundary and a unique identifier.
We provide three types of UDFs for processing three grid cell shapes:
• rectangular (geohash)
• hexagon (hexagon hash)
• square (square hash)
Hashes are useful for analysis and interoperability with other systems.
Square hash is similar to GeoHash but has the advantage that when displayed in Popular Mercator, the cells appear as squares.
Hexagons are often used in telecommunication solutions as they approximate circles while covering the surface of the earth without gaps.
The following Grid index functions are available:
• GeoHashBoundary:
The GeoHashBoundary function returns a WritableGeometry that defines the boundary of a cell in a grid if given a unique ID for the location. It also can return the boundary of the cell that contains the given point at the specified precision. The shape of the cell is rectangular.
Function Registration
create function GeoHashBoundary as 'com.pb.bigdata.spatial.hive.grid.GeoHashBoundary';
Syntax
GeoHashBoundary(String UNIQUE_ID)
Parameters
Description Type
Parameter
The unique geohash identifier of a cell in a grid.
A representation of the boundary of a cell in a grid.
WritableGeometry
Examples
SELECT GeoHashBoundary(hashStringId) FROM hivetable;
SELECT GeoHashBoundary("ebvnk");
Syntax
GeoHashBoundary(Number|String X, Number|String Y, Number precision)
Parameters
Description Type
Parameter
The longitude value of the point.
Number or String X
The latitude value of the point.
Number or String Y
The length of the string key to be returned.
The precision determines how large the grid cells are (longer strings means higher precision and smaller grid cells).
Number precision
Return Values
Description Return Type
The boundary of the grid cell at the given precision that the point falls into.
WritableGeometry
Examples
SELECT GeoHashBoundary(x, y, precision) FROM hivetable;
SELECT GeoHashBoundary("-73.750333", "42.736103", 3);
SELECT GeoHashBoundary(-73.750333, 42.736103, 3);
GeoHashID
Description
The GeoHashID function returns a unique well-known string ID for the grid cell. The ID then is sortable and searchable that corresponds to the specified X, Y, and precision.
Function Registration
create function GeoHashID as 'com.pb.bigdata.spatial.hive.grid.GeoHashID';
Syntax
GeoHashID(Number|String X, Number|String Y, Number precision)
Parameters
Description Type
Parameter
The longitude value of the point.
Number or String X
The latitude value of the point.
Number or String Y
Description Type
Parameter
The length of the string key to be returned.
The precision determines how large the grid cells are (longer strings means higher precision and smaller grid cells).
Number precision
Return Values
Description Return Type
The geohash ID of the grid cell at the specified precision that contains the point.
String
Examples
SELECT GeoHashID(x, y, precision) FROM hivetable;
SELECT GeoHashID("-73.750333", "42.736103", 3);
SELECT GeoHashID(-73.750333, 42.736103, 3);
CREATE TEMPORARY TABLE tmptbl AS
SELECT *, (GeoHashID(x, y, 10)) AS hashID FROM coordinates ORDER BY hashID;
INSERT INTO TABLE coordinates_with_hash SELECT *, (GeoHashID(x, y, 10)) AS hashID FROM coordinates ORDER BY hashID;
SELECT c.hashID, ToWKT(GeoHashBoundary (c.hashID)), count (*) as quantity FROM (SELECT GeoHashID(x, y, 10) AS hashID FROM coordinates) c
GROUP BY c.hashID;
HexagonBoundary
Description
The HexagonBoundary function returns a WritableGeometry that defines the boundary of a cell in a grid if given a unique ID for the location. It also can return the boundary of the cell that contains the given point at the specified precision. The shape of the cell is a hexagon.
Function Registration
create function HexagonBoundary as 'com.pb.bigdata.spatial.hive.grid.HexagonBoundary';
Syntax
HexagonBoundary(String UNIQUE_ID)
Parameters
Description Type
Parameter
The unique geohash identifier of a cell in a grid.
String UNIQUE_ID
Return Values
Description Return Type
A representation of the boundary of a cell in a grid.
WritableGeometry
Examples
SELECT HexagonBoundary(hashStringId) FROM hivetable;
SELECT HexagonBoundary("PF625028642");
Syntax
HexagonBoundary(Number|String X, Number|String Y, Number precision)
Parameters
Description Type
Parameter
The longitude value of the point.
Number or String X
The latitude value of the point.
Number or String Y
The length of the string key to be returned.
The precision determines how large the grid cells are (longer strings means higher precision and smaller grid cells).
Number precision
Return Values
Description Return Type
The boundary of the grid cell at the given precision that the point falls into.
WritableGeometry
Examples
SELECT HexagonBoundary(x, y, precision) FROM hivetable;
SELECT HexagonBoundary(-73.750333, 42.736103, 3);
HexagonID
Description
The HexagonID function returns a unique well-known string ID for the grid cell. The ID then is sortable and searchable that corresponds to the specified X, Y, and precision.
Function Registration
create function HexagonID as 'com.pb.bigdata.spatial.hive.grid.HexagonID';
Syntax
HexagonID(Number|String X, Number|String Y, Number precision)
Parameters
Description Type
Parameter
The longitude value of the point.
Number or String X
The latitude value of the point.
Number or String Y
The length of the string key to be returned.
The precision determines how large the grid cells are (longer strings means higher precision and smaller grid cells).
Number precision
Return Values
Description Return Type
The ID of the grid cell at the specified precision that contains the point.
String
Examples
SELECT HexagonID(x, y, precision) FROM hivetable;
SELECT HexagonID(-73.750333, 42.736103, 3);
CREATE TEMPORARY TABLE tmptbl AS
SELECT *, (HexagonID(x, y, 10)) AS hashID FROM coordinates ORDER BY hashID;
INSERT INTO TABLE coordinates_with_hash SELECT *, (HexagonID(x, y, 10)) AS hashID FROM coordinates ORDER BY hashID;
SELECT c.hashID, ToWKT(HexagonBoundary(c.hashID)),
count (*) AS quantity FROM (SELECT HexagonID(x, y, 10) AS hashID FROM coordinates)
c GROUP BY c.hashID;
SquareHashBoundary
Description
The SquareHashBoundary function returns a WritableGeometry that defines the boundary of a cell in a grid if given a unique ID for the location. It also can return the boundary of the cell that contains the given point at the specified precision. Square hash cells appear square when displayed on a Popular Mercator map.
Function Registration
create function SquareHashBoundary as 'com.pb.bigdata.spatial.hive.grid.SquareHashBoundary';
Syntax
SquareHashBoundary(String UNIQUE_ID)
Parameters
Description Type
Parameter
The unique geohash identifier of a cell in a grid.
String UNIQUE_ID
Return Values
Description Return Type
A representation of the boundary of a cell in a grid.
WritableGeometry
Examples
SELECT SquareHashBoundary(hashStringId) FROM hivetable;
SELECT SquareHashBoundary("03332");
Syntax
SquareHashBoundary(Number|String X, Number|String Y, Number precision)
Parameters
Description Type
Parameter
The longitude value of the point.
Number or String X
The latitude value of the point.
Number or String Y
The length of the string key to be returned.
The precision determines how large the grid cells are (longer strings means higher precision and smaller grid cells).
Number precision
Return Values
Description Return Type
The boundary of the grid cell at the given precision that the point falls into.
WritableGeometry
Examples
SELECT SquareHashBoundary(x, y, precision) FROM hivetable;
SELECT SquareHashBoundary("-73.750333", "42.736103", 3);
SELECT SquareHashBoundary(-73.750333, 42.736103, 3);
SquareHashID
Description
The SquareHashID function takes a longitude, latitude (in WGS 84) and a precision. The precision determines how large the grid cells are (higher precision means smaller grid cells). It returns the
string ID of the grid cell at the specified precision that contains the point. Square hash cells appear square when displayed on a Popular Mercator map.
Function Registration
create function SquareHashID as 'com.pb.bigdata.spatial.hive.grid.SquareHashID';
Syntax
SquareHashID(Number|String X, Number|String Y, Number precision)
Parameters
Description Type
Parameter
The longitude value of the point.
Number or String X
The latitude value of the point.
Number or String Y
The length of the string key to be returned.
The precision determines how large the grid cells are (longer strings means higher precision and smaller grid cells).
Number precision
Return Values
Description Return Type
The ID of the grid cell at the specified precision that contains the point.
String
Examples
SELECT SquareHashID(x, y, precision) FROM hivetable;
SELECT SquareHashID("-73.750333", "42.736103", 3);
SELECT SquareHashID(-73.750333, 42.736103, 3);
CREATE TEMPORARY TABLE tmptbl AS
SELECT *, (SquareHashID(x, y, 10)) AS hashID FROM coordinates ORDER BY hashID;
INSERT INTO TABLE coordinates_with_hash
SELECT *, (SquareHashID(x, y, 10)) AS hashID FROM coordinates ORDER BY hashID;
SELECT c.hashID, ToWKT(SquareHashBoundary (c.hashID)),
count (*) AS quantity FROM (SELECT SquareHashID(x, y, 10) AS hashID FROM coordinates)
c GROUP BY c.hashID;