Geo Functions

Geospatial functions for distance calculations and location-based queries.

Geospatial Functions

SDBQL provides geospatial functions for calculating distances between coordinates using the Haversine formula, which accounts for Earth's curvature.

Distance Calculations

DISTANCE(lat1, lon1, lat2, lon2)

Calculates distance in meters between two coordinate pairs using the Haversine formula.

-- Distance from Paris to London
RETURN DISTANCE(48.8566, 2.3522, 51.5074, -0.1278)
-- ~343,563 meters (343 km)

GEO_DISTANCE(p1, p2)

Distance in meters between GeoPoint objects (with lat/lon properties).

-- Using GeoPoint objects
LET paris = {lat: 48.8566, lon: 2.3522}
LET london = {lat: 51.5074, lon: -0.1278}
RETURN GEO_DISTANCE(paris, london)

Geometric Tests

GEO_WITHIN(point, polygon)

Returns true if the point is inside the specified polygon (ray casting). Polygon is an array of points (LinearRing).

-- Check if point is inside a zone
LET point = {lat: 48.8566, lon: 2.3522}
-- Polygon defined as array of [lon, lat] or objects
LET zone = [[2.3, 48.8], [2.4, 48.8], [2.4, 48.9], [2.3, 48.9]] 
RETURN GEO_WITHIN(point, zone)

Haversine Formula

The Haversine formula calculates the great-circle distance between two points on a sphere using their latitude and longitude coordinates. This provides accurate distance calculations for most use cases on Earth.

Practical Examples

-- Find places within 10km of a location
FOR place IN places
  LET dist = DISTANCE(place.lat, place.lon, 48.8566, 2.3522)
  FILTER dist <= 10000
  SORT dist ASC
  RETURN { name: place.name, distance: ROUND(dist) }

-- Find the nearest restaurant to user
FOR restaurant IN restaurants
  LET dist = DISTANCE(restaurant.lat, restaurant.lon, @userLat, @userLon)
  SORT dist ASC
  LIMIT 1
  RETURN { name: restaurant.name, distance: ROUND(dist) }

-- Find all stores within 5km, sorted by distance
FOR store IN stores
  LET dist = DISTANCE(store.location.lat, store.location.lon, @lat, @lon)
  FILTER dist <= 5000
  SORT dist ASC
  RETURN {
    name: store.name,
    address: store.address,
    distance_km: ROUND(dist / 1000, 2)
  }

-- Group locations by distance ranges
FOR loc IN locations
  LET dist = DISTANCE(loc.lat, loc.lon, @centerLat, @centerLon)
  COLLECT range = CASE
    WHEN dist < 1000 THEN "< 1km"
    WHEN dist < 5000 THEN "1-5km"
    WHEN dist < 10000 THEN "5-10km"
    ELSE "> 10km"
  END
  WITH COUNT INTO count
  RETURN { range, count }

-- Calculate delivery zone
FOR order IN orders
  LET customerLoc = (FOR c IN customers FILTER c._key == order.customer_key RETURN c)[0]
  LET warehouseLoc = (FOR w IN warehouses FILTER w._key == order.warehouse_key RETURN w)[0]
  LET dist = DISTANCE(
    customerLoc.lat, customerLoc.lon,
    warehouseLoc.lat, warehouseLoc.lon
  )
  RETURN {
    order_id: order._key,
    distance_km: ROUND(dist / 1000, 1),
    delivery_zone: dist < 10000 ? "local" : dist < 50000 ? "regional" : "national"
  }

-- Find locations within a bounding box (approximate)
LET centerLat = 48.8566
LET centerLon = 2.3522
LET radiusKm = 10
LET latDelta = radiusKm / 111  -- approx km per degree lat
LET lonDelta = radiusKm / (111 * COS(centerLat * PI() / 180))

FOR place IN places
  FILTER place.lat >= centerLat - latDelta
  FILTER place.lat <= centerLat + latDelta
  FILTER place.lon >= centerLon - lonDelta
  FILTER place.lon <= centerLon + lonDelta
  LET exactDist = DISTANCE(place.lat, place.lon, centerLat, centerLon)
  FILTER exactDist <= radiusKm * 1000
  SORT exactDist
  RETURN { name: place.name, distance_m: ROUND(exactDist) }

Tips for Geo Queries

Performance

Create geo indexes on lat/lon fields
Use bounding box pre-filtering for large datasets
Store coordinates in a consistent format

Best Practices

Use decimal degrees (not DMS)
Validate coordinates are within valid ranges
Consider timezone for location-based features

Numeric Functions Search Functions