CODEWITHSUNDEEP
google-bigquerybigquery-udf
qwwqwwq
qwwqwwq

Reputation: 7329

How to Convert Geohash to Geometry in BigQuery?

PostGIS has this function ST_GeomFromGeoHash to get the bounding box geometry of the geohash area (https://postgis.net/docs/ST_GeomFromGeoHash.html), but it has not been ported to BigQuery yet. Is there any workaround?

Upvotes: 1

Views: 1303

Answers (2)

qwwqwwq
qwwqwwq

Reputation: 7329

I've implemented the following BigQuery UDF that converts a geohash of arbitrary precision to a bounding box geometry:

CREATE OR REPLACE FUNCTION dataset.geohash_to_bbox(geohash STRING) 
RETURNS STRING 
LANGUAGE js AS """
var BASE32_CODES = "0123456789bcdefghjkmnpqrstuvwxyz";
var BASE32_CODES_DICT = {};
for (var i = 0; i < BASE32_CODES.length; i++) {
  BASE32_CODES_DICT[BASE32_CODES.charAt(i)] = i;
}
var ENCODE_AUTO = 'auto';
var MIN_LAT = -90;
var MAX_LAT = 90;
var MIN_LON = -180;
var MAX_LON = 180;
var decode_bbox = function (hash_string) {
  var isLon = true,
    maxLat = MAX_LAT,
    minLat = MIN_LAT,
    maxLon = MAX_LON,
    minLon = MIN_LON,
    mid;
  var hashValue = 0;
  for (var i = 0, l = hash_string.length; i < l; i++) {
    var code = hash_string[i].toLowerCase();
    hashValue = BASE32_CODES_DICT[code];
    for (var bits = 4; bits >= 0; bits--) {
      var bit = (hashValue >> bits) & 1;
      if (isLon) {
        mid = (maxLon + minLon) / 2;
        if (bit === 1) {
          minLon = mid;
        } else {
          maxLon = mid;
        }
      } else {
        mid = (maxLat + minLat) / 2;
        if (bit === 1) {
          minLat = mid;
        } else {
          maxLat = mid;
        }
      }
      isLon = !isLon;
    }
  }
  
  return "POLYGON (( " + minLon + " " + minLat + ", " + maxLon + " " + minLat + ", " + maxLon + " " + maxLat + ", " + minLon + " " + maxLat + ", " + minLon + " " + minLat + "))";
};
return decode_bbox(geohash);
""";

Example usage:

select <dataset>.geohash_to_geom("ttnfv2u");

>> POLYGON((77.2119140625 28.6083984375, 77.2119140625 28.65234375, 77.255859375 28.65234375, 77.255859375 28.6083984375, 77.2119140625 28.6083984375))

Upvotes: 2

Michael Entin
Michael Entin

Reputation: 7744

BigQuery has ST_GEOGPOINTFROMGEOHASH which returns the central point. There is currently no function that returns the box though. The UDF in another answer is often a reasonable workaround, but you should be aware of its usage limitation.

GeoHash normally represents a rectangle on a flat 2D map. BigQuery works with Geography, with geodesic edges, so an edge between two points with same latitude does not follow the parallel, but being geodesic line is a shorter route closer to the pole. So the BigQuery polygon is a bit different from 2D box. You can often ignore the differences, but it might give you wrong results depending on how you use this polygon.

Upvotes: 0

Related Questions