Geohashing

Copy-paste examples of geohashing data with SQL

Enrich a Point-Based Table with Geohashes

Create a geohash for pickup locations in the NYC taxi data set.

Lat/Lon-Based Table
1
2
3
4
5
6
7
8
CREATE OR REPLACE TABLE example_geospatial.nyctaxi_geohash AS
(
      SELECT
            pickup_latitude,
            pickup_longitude,
            GEOHASH_ENCODE(pickup_latitude, pickup_longitude, 6) AS geohash
      FROM example_geospatial.nyctaxi_xy
)
WKT-Based Table
1
2
3
4
5
6
7
CREATE OR REPLACE TABLE example_geospatial.nyctaxi_geohash AS
(
      SELECT
            pickup_location,
            ST_GEOHASH(pickup_location, 6) AS geohash
      FROM example_geospatial.nyctaxi_wkt
)

Create an Aggregated View with WKT Geometries

Use the table created above to generate a geohash grid view with the counts of pickups within each cell.

Aggregated Pick-Up Counts per Geohash Cell
1
2
3
4
5
6
SELECT
      geohash,
      ST_GEOMFROMGEOHASH(geohash) AS geohash_cell,
      COUNT(*) AS total_pickups
FROM example_geospatial.nyctaxi_geohash
GROUP BY geohash

The geohash grid output as a class break render:

geohash.png