Geohashing

Enrich a Point-Based Table with Geohashes

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

• Lat/Lon-Based Table
• WKT-Based Table

1
2
3
4
5
6
7
8

CREATE OR REPLACE TABLE example_geospatial.nyctaxi_geohash AS
(
    SELECT
        pickup_latitude,
        pickup_longitude,
        STXY_GEOHASH(pickup_longitude, pickup_latitude, 6) AS geohash
    FROM example_geospatial.nyctaxi_xy
)

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.

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: