API SQL Downloads – Functions
| This is an experimental feature, and the implementation may change throughout 2024. The feature is currently available for preview on the GBIF test environment, GBIF-UAT.org. |
These custom functions are available when using the SQL download API.
Note the function names are not case-sensitive, gbif_geodistance, GBIF_GEODISTANCE and GBIF_GeoDistance are all equivalent.
Filtering functions
These are the same functions used for predicate-based downloads.
Geospatial distance, GBIF_GeoDistance
Function definition:
BOOLEAN GBIF_GeoDistance(DOUBLE centroidLatitude,
DOUBLE centroidLongitude,
STRING distance,
DOUBLE latitude,
DOUBLE longitude)Parameters:
centroidLatitude-
The latitude of the centroid, in WGS84 decimal degrees
centroidLongitude-
The longitude of the centroid, in WGS84 decimal degrees
distance-
A distance in metres or kilometres including the unit, e.g.
500mor2.5km latitude-
The latitude of the point to filter (usually the
decimalLatitudefield) longitude-
The longitude of the point to filter (usually the
decimalLongitudefield)
Return value:
This function returns TRUE if the point is within distance of the centroid. It is equivalent to the geoDistance predicate.
Within polygon, GBIF_Within
Function definition:
BOOLEAN GBIF_Within(STRING geometryWellKnownText,
DOUBLE latitude,
DOUBLE longitude)Parameters:
geometryWellKnownText-
A polygon specified in Well-known text (WKT) format
latitude-
The latitude of the point to filter (usually the
decimalLatitudefield) longitude-
The longitude of the point to filter (usually the
decimalLongitudefield)
Return value:
This function returns TRUE if the point is within the polygon. It is equivalent to the within predicate, and to the geometry parameter of the occurrence search API.
Notes:
If you have problems, first check your polygon with Wicket — ensure the points are ordered anti-clockwise. (A polygon with clockwise points represents the opposite: the whole world except the polygon.)
Grid functions
These functions are usually used for generating species occurrence cubes. They calculate a grid cell for a point.
EEA Reference Grid cell code, GBIF_EEARGCode
Function definition:
STRING GBIF_EEARGCode(INTEGER gridSize,
DOUBLE latitude,
DOUBLE longitude,
DOUBLE coordinateUncertaintyInMeters)Parameters:
gridSize-
The grid size in metres of the EEA Reference Grid. Must be 25, 100, 250, 1000, 10000 or 100000.
latitude-
The latitude of the point to grid (usually the
decimalLatitudefield) longitude-
The longitude of the point to grid (usually the
decimalLongitudefield) coordinateUncertaintyInMeters-
The uncertainty radius to apply to the point (usually used with a default value, for example
COALESCE(coordinateUncertaintyInMeters, 1000))
The coordinateUncertaintyInMeters value is used to randomize the point within the circle.
Return value:
The code for the cell of the EEA reference grid in which the randomized point falls.
Example arguments and returned values:
Result |
gridSize |
latitude |
longitude |
coordinateUncertaintyInMeters |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Reference: About the EEA reference grid (PDF).
Extended Quarter-Degree Grid cell code, GBIF_EQDGCCode
Function definition:
STRING GBIF_EQDGCCode(INTEGER level,
DOUBLE latitude,
DOUBLE longitude,
DOUBLE coordinateUncertaintyInMeters)Parameters:
level-
The level of the grid; the number of additional divisions applied to a one-degree cell.
latitude-
The latitude of the point to grid (usually the
decimalLatitudefield) longitude-
The longitude of the point to grid (usually the
decimalLongitudefield) coordinateUncertaintyInMeters-
The uncertainty radius to apply to the point (usually used with a default value, for example
COALESCE(coordinateUncertaintyInMeters, 1000))
The coordinateUncertaintyInMeters value is used to randomize the point within the circle.
A level of 0 will give the 1° cell, e.g. S01E010. For quarter-degrees, use level 2, e.g. S01E010AD.
Return value:
The code for the cell of the Extended Quarter-Degree Grid in which the randomized point falls.
Example arguments and returned values:
Result |
level |
latitude |
longitude |
coordinateUncertaintyInMeters |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
See also: Wikipedia: QDGC.
Military Grid Reference System cell code, GBIF_MGRSCode
Function definition:
STRING GBIF_MGRSCode(INTEGER gridSize,
DOUBLE latitude,
DOUBLE longitude,
DOUBLE coordinateUncertaintyInMeters)Parameters:
gridSize-
The grid size in metres. Must be 1, 10, 100, 1000, 10000, 100000 or 0.
latitude-
The latitude of the point to grid (usually the
decimalLatitudefield) longitude-
The longitude of the point to grid (usually the
decimalLongitudefield) coordinateUncertaintyInMeters-
The uncertainty radius to apply to the point (usually used with a default value, for example
COALESCE(coordinateUncertaintyInMeters, 1000))
The coordinateUncertaintyInMeters value is used to randomize the point within the circle.
A gridSize of 0 will give the Grid Zone Junction (GZJ) only, e.g. 32. Other values increase the accuracy of the grid, e.g. 100 (metres) 32UNC686615.
Return value:
The code for the cell of the Military Grid Reference System in which the randomized point falls.
Example arguments and returned values:
Result |
gridSize |
latitude |
longitude |
coordinateUncertaintyInMeters |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Reference: Grids and Reference Systems.
See also: Wikipedia: Military Grid Reference System.
Text output functions
These functions are useful when producing text-type output, e.g. tab-delimited files.
Join Array, GBIF_JoinArray
Function definition:
STRING GBIF_JoinArray(ARRAY array,
STRING separator)Parameters:
array-
An array
separator-
A separator to put between the array values, e.g.
|or `, `
Return value:
This function returns the array elements separated by the separator.