Skip to main content

​
Enrich a Point-Based Table with Geohashes

Create a geohash for pickup locations in the NYC taxi data set. 
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
)

​
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
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