API SQL Downloads – Functions :: Technical Documentation (original) (raw)

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

Figure 1. EEA Reference Grid with 50km resolution

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, 50000 or 100000.

latitude

The latitude of the point to grid (usually the decimalLatitude field)

longitude

The longitude of the point to grid (usually the decimalLongitude field)

coordinateUncertaintyInMeters

The uncertainty radius to apply to the point (usually used with a default value, for example COALESCE(coordinateUncertaintyInMeters, 1000)). Set to 0 to disable randomization.

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.

Supported grid resolutions and example values:

Example query:

SELECT
  kingdom,
  scientificName,
  COUNT(*),
  GBIF_EEARGCode(
    10000, (1)
    decimalLatitude,
    decimalLongitude,
    COALESCE(coordinateUncertaintyInMeters, 1000) (2)
  )
FROM occurrence
WHERE country = 'PL'
GROUP BY kingdom, scientificName;

Additional resources

GeoPackage and ShapeFile downloads of the grids at several resolutions are available for download at the European Environment Agency Datahub. (See the 2013 and 2011 datasets for alternative resolutions.)

Extended Quarter-Degree Grid-cell code, GBIF_EQDGCode

Figure 2. Extended Quarter-Degree Grid-cells at level 2 on northern Madagascar

Function definition:

STRING GBIF_EQDGCode(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 decimalLatitude field)

longitude

The longitude of the point to grid (usually the decimalLongitude field)

coordinateUncertaintyInMeters

The uncertainty radius to apply to the point (usually used with a default value, for example COALESCE(coordinateUncertaintyInMeters, 1000)). Set to 0 to disable randomization.

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.

Supported grid resolutions and example values:

Example query:

SELECT
  kingdom,
  scientificName,
  COUNT(*),
  GBIF_EQDGCode(
    2, (1)
    decimalLatitude,
    decimalLongitude,
    COALESCE(coordinateUncertaintyInMeters, 1000) (2)
  )
FROM occurrence
WHERE country = 'ZA'
GROUP BY kingdom, scientificName;

Additional resources

Degree-Minute-Second Grid-cell code, GBIF_DMSGCode

Function definition:

STRING GBIF_DMSGCode(INTEGER resolution,
                     DOUBLE latitude,
                     DOUBLE longitude,
                     DOUBLE coordinateUncertaintyInMeters)

Parameters:

resolution

The resolution of the grid in seconds

latitude

The latitude of the point to grid (usually the decimalLatitude field)

longitude

The longitude of the point to grid (usually the decimalLongitude field)

coordinateUncertaintyInMeters

The uncertainty radius to apply to the point (usually used with a default value, for example COALESCE(coordinateUncertaintyInMeters, 1000)). Set to 0 to disable randomization.

The coordinateUncertaintyInMeters value is used to randomize the point within the circle.

A resolution of 3600 will give the 1° cell, e.g. S01E010 — this is the same as Extended Quarter-Degree Grid-cell code, GBIF_EQDGCode with level 0.

The resolution must be a divisor of 3600. Useful values are 3600 (1°), 900 (15′), 600 (10′), 300 (5′), 150 (2′30″), 60 (1′) and 30 (30″).

Return value:

A code for the cell in the format EXXXxxxxNYYyyyy where E is either E or W and N is either N or S. XXX is a value between 0 and 179, YY is between 0 and 89. The fractional parts xxxx and yyyy, if present, are the coordinate of the corner of the square nearest to the origin. The point 0°, 0° is defined to be E000N00.

Example grid resolutions and example values:

10° 48′ 45.6″ N = 10.8127

Example query:

SELECT
  kingdom,
  scientificName,
  COUNT(*),
  GBIF_DMSGCode(
    900, (1)
    decimalLatitude,
    decimalLongitude,
    COALESCE(coordinateUncertaintyInMeters, 1000) (2)
  )
FROM occurrence
WHERE country = 'ZA'
GROUP BY kingdom, scientificName;

ISEA3H Grid-cell code GBIF_ISEA3HCode

Function definition:

STRING GBIF_ISEA3HCode(INTEGER resolution,
                       DOUBLE latitude,
                       DOUBLE longitude,
                       DOUBLE coordinateUncertaintyInMeters)

Parameters:

resolution

The resolution of the grid; the number of division steps applied to the initial dodecahedron. Valid values are 1 to 22.

latitude

The latitude of the point to grid (usually the decimalLatitude field)

longitude

The longitude of the point to grid (usually the decimalLongitude field)

coordinateUncertaintyInMeters

The uncertainty radius to apply to the point (usually used with a default value, for example COALESCE(coordinateUncertaintyInMeters, 1000)). Set to 0 to disable randomization.

The coordinateUncertaintyInMeters value is used to randomize the point within the circle.

See ISEA3H details for the number and size of cells at each resolution.

Return value:

The code for the cell of the ISEA3H grid-cell in which the randomized point falls.

Supported grid resolutions and example values:

Example query:

SELECT
  kingdom,
  scientificName,
  COUNT(*),
  GBIF_ISEA3HCode(
    6, (1)
    decimalLatitude,
    decimalLongitude,
    COALESCE(coordinateUncertaintyInMeters, 1000) (2)
  )
FROM occurrence
WHERE continent = 'AFRICA'
GROUP BY kingdom, scientificName;

Additional resources

References:

• Discrete Global Grid Systems Abstract Specification
• Geodesic discrete global grid systems.
• A novel identifier scheme for the ISEA Aperture 3 Hexagon Discrete Global Grid System

See also:

• geogrid, the Java library used by GBIF to calculate the grid.
• dggridR, an R package to calculate the grid (though not using the same identifiers).

Military Grid Reference System Grid-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 decimalLatitude field)

longitude

The longitude of the point to grid (usually the decimalLongitude field)

coordinateUncertaintyInMeters

The uncertainty radius to apply to the point (usually used with a default value, for example COALESCE(coordinateUncertaintyInMeters, 1000)). Set to 0 to disable randomization.

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.

Supported grid resolutions and example values:

Example query:

SELECT
  kingdom,
  scientificName,
  COUNT(*),
  GBIF_MGRSCode(
    10000, (1)
    decimalLatitude,
    decimalLongitude,
    COALESCE(coordinateUncertaintyInMeters, 1000) (2)
  )
FROM occurrence
WHERE country = 'CG'
GROUP BY kingdom, scientificName;

Additional resources