Geospatial Data

-- ❌ This is NOT possible in Cassandra: SELECT * FROM drivers WHERE ST_Distance(location, '37.7749,-122.4194') < 5000; -- ERROR: ST_Distance doesn't exist! SELECT * FROM restaurants WHERE latitude BETWEEN 37.7 AND 37.8 AND longitude BETWEEN -122.5 AND -122.4; -- ERROR: Range queries require clustering column! -- ❌ Can't query by coordinate ranges without partition key

-- Table design with GeoHash partition key CREATE TABLE drivers_by_location ( geohash text, ← Partition key (5 chars = ~2.4km) driver_id uuid, ← Clustering key latitude double, longitude double, driver_name text, vehicle_type text, available boolean, last_updated timestamp, PRIMARY KEY ((geohash), driver_id) ); -- Index for quick status filtering CREATE INDEX ON drivers_by_location (available);

import pygeohash as pgh from cassandra.cluster import Cluster # Connect to Cassandra cluster = Cluster(['localhost']) session = cluster.connect('rideshare') # 1. INSERT: Update driver location def update_driver_location(driver_id, lat, lon): # Encode lat/lon to geohash (5 chars = ~2.4km grid) geohash = pgh.encode(lat, lon, precision=5) session.execute(""" INSERT INTO drivers_by_location (geohash, driver_id, latitude, longitude, available, last_updated) VALUES (%s, %s, %s, %s, %s, toTimestamp(now())) """, (geohash, driver_id, lat, lon, True)) print(f"Driver {driver_id} at GeoHash: {geohash}") # Example: Driver in San Francisco update_driver_location( driver_id='550e8400-e29b-41d4-a716-446655440000', lat=37.7749, lon=-122.4194 ) # Output: Driver ... at GeoHash: 9q8yy # 2. QUERY: Find drivers near user def find_nearby_drivers(user_lat, user_lon, radius_km=5): # Get user's geohash user_geohash = pgh.encode(user_lat, user_lon, precision=5) # Get neighboring geohashes (covers edge cases) neighbors = pgh.get_adjacent(user_geohash) geohashes_to_check = [user_geohash] + neighbors drivers = [] for gh in geohashes_to_check: # Query Cassandra for each geohash result = session.execute(""" SELECT * FROM drivers_by_location WHERE geohash = %s AND available = true """, (gh,)) for row in result: # Calculate actual distance distance = haversine_distance( user_lat, user_lon, row.latitude, row.longitude ) if distance <= radius_km: drivers.append({ 'driver_id': row.driver_id, 'distance_km': distance, 'location': (row.latitude, row.longitude) }) # Sort by distance drivers.sort(key=lambda d: d['distance_km']) return drivers # Haversine formula for distance def haversine_distance(lat1, lon1, lat2, lon2): from math import radians, sin, cos, sqrt, atan2 R = 6371 # Earth radius in km lat1, lon1, lat2, lon2 = map(radians, [lat1, lon1, lat2, lon2]) dlat = lat2 - lat1 dlon = lon2 - lon1 a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2 c = 2 * atan2(sqrt(a), sqrt(1-a)) return R * c

import pyquadkey2 as pqk # Convert lat/lon to quadkey quadkey = pqk.from_geo((37.7749, -122.4194), level=12) print(quadkey) # Output: '023010121330' # Cassandra table with QuadKey """ CREATE TABLE drivers_by_quadkey ( quadkey text, driver_id uuid, latitude double, longitude double, PRIMARY KEY ((quadkey), driver_id) ); """

CREATE TABLE places_by_location ( geohash text, ← Partition by grid cell place_id uuid, name text, category text, ← restaurant, cafe, etc latitude double, longitude double, rating decimal, PRIMARY KEY ((geohash), place_id) ); -- Pro: Simple, fast proximity queries -- Con: Hot partitions in dense areas

CREATE TABLE places_by_precision ( precision int, ← 4, 5, 6, 7 char geohash geohash text, place_id uuid, name text, latitude double, longitude double, PRIMARY KEY ((precision, geohash), place_id) ); -- Use different precisions for different zoom levels: -- Zoom out (country view) → precision 4 -- City view → precision 5 -- Street view → precision 7

CREATE TABLE drivers_location ( city text, ← Coarse bucket (san_francisco) geohash text, ← Fine-grained grid (5 chars) driver_id uuid, latitude double, longitude double, available boolean, last_updated timestamp, PRIMARY KEY ((city, geohash), driver_id) ) WITH CLUSTERING ORDER BY (driver_id ASC); -- Benefits: -- 1. Prevents global hot partitions -- 2. City-level distribution -- 3. Easy to add/remove cities

# Python - Find restaurants within 3km def find_nearby_restaurants(user_lat, user_lon, radius_km=3): # 1. Calculate user's geohash precision = 6 # 610m cells user_gh = pgh.encode(user_lat, user_lon, precision) # 2. Get geohash + 8 neighbors (3x3 grid) search_area = [user_gh] + pgh.get_adjacent(user_gh) # 3. Query each geohash restaurants = [] for gh in search_area: rows = session.execute(""" SELECT * FROM places_by_location WHERE geohash = %s AND category = 'restaurant' """, (gh,)) for row in rows: dist = haversine_distance( user_lat, user_lon, row.latitude, row.longitude ) if dist <= radius_km: restaurants.append({ 'name': row.name, 'distance': dist, 'rating': row.rating }) # 4. Sort by distance return sorted(restaurants, key=lambda r: r['distance'])[:10]

# Find all places in map viewport def get_places_in_bounds(north, south, east, west): # Get all geohashes covering bounding box geohashes = get_geohashes_in_bbox(north, south, east, west, precision=5) places = [] for gh in geohashes: rows = session.execute(""" SELECT * FROM places_by_location WHERE geohash = %s """, (gh,)) places.extend(rows) return places

# Find 10 closest coffee shops def find_nearest_coffee(user_lat, user_lon, k=10): precision = 6 user_gh = pgh.encode(user_lat, user_lon, precision) # Start with immediate area, expand if needed candidates = [] search_radius = 1 # geohash radius while len(candidates) < k * 2: # Get 2x for filtering geohashes = get_geohashes_at_radius(user_gh, search_radius) for gh in geohashes: rows = session.execute(""" SELECT * FROM places_by_location WHERE geohash = %s AND category = 'coffee' """, (gh,)) candidates.extend(rows) search_radius += 1 if search_radius > 5: # Safety limit break # Calculate distances and return k nearest for c in candidates: c.distance = haversine_distance(user_lat, user_lon, c.latitude, c.longitude) return sorted(candidates, key=lambda c: c.distance)[:k]

-- DoorDash/UberEats delivery zones CREATE TABLE delivery_zones ( city text, geohash text, ← 5-char grid zone_id text, restaurants list, ← Restaurants in this zone drivers list, ← Assigned drivers avg_delivery_time int, ← Minutes surge_multiplier decimal, PRIMARY KEY ((city, geohash)) ); -- Benefits: -- • Automatic zone assignment by location -- • Easy to calculate coverage gaps -- • Dynamic surge pricing per zone

-- Snapchat/Instagram location sharing CREATE TABLE user_locations ( geohash text, ← 6-char for privacy (~600m) user_id uuid, latitude double, longitude double, shared_with set, ← Friends who can see expires_at timestamp, PRIMARY KEY ((geohash), user_id) ) WITH default_time_to_live = 3600; ← 1 hour expiry -- Query: Show nearby friends -- 1. Get user's geohash -- 2. Query geohash + neighbors -- 3. Filter by shared_with (privacy!) -- 4. Return friends within 5km