Geospatial Extensions in SonarW

Time/Distance Extensions

Projecting Earth Distance

To project the earth-based distance between two lat/long points use:

"$distance":[ "$lat", "$long","$other_lat","$other_long"]

Calculating Impossible Distance (Speeding)

To determine if a distance is not attainable in a certain time travelling at a maximum velocity, use the $impossibleDistance:

"$impossibleDistance":[ "$lat", "$long","$previous_lat","$previous_long", "$timestamp","$previous_timestamp",40]

This function returns true if it is not possible. No routing is performed; the on-earth distance is computed, i.e. “as the crow flies”.

The timestamps are ISODates - i.e. the difference in milliseconds is computed. The last parameter is the velocity in meters/second - e.g. 40 m/s is 144 km/hr or 89 miles/hr.

As an example, the following window function is used to flag credit card numbers that are used in places/times that are unreachable by car (i.e. used in too distant places and too close to one another to be logical; ignoring air travel):

{"$window": {
      "$analyze":[
                     "previous_timestamp":{"$lag":["$timestamp",1]},
                     "previous_lat":{"$lag":["$lat",1]},
                     "previous_long":{"$lag":["$long",1]},
                     "$over":{"$partitionBy":"$card number","$orderBy":["$timestamp",1]}}
         ],
        "$project":{"timestamp":1,"card number":1,long:1,lat:1},
        "$having":{
                    "$expr":{"$impossibleDistance":[ "$lat", "$long","$previous_lat","$previous_long", "$timestamp","$previous_timestamp",40]}
         }
}}

This function can also be used as a simple indicator for speeding.

Geo Hash

Geohash is a public domain geocoding system which encodes a geographic location into a short string of letters and digits. It is a hierarchical spatial data structure which subdivides space into buckets of grid shape

Given a set of coordinates sonarw operator $geoHash can compute the geohash string until the defined precision.

$geoHash parameteres:

  • “precision”: the length of the returned hash, which also defines the precision of the hash and the size of the bounding box. the precision can be any number from 1 to 12. precision levels corresponding to real world values displayed below.
  • “coordinates”: an expression to [longitude, latitude] coordinate array or geojson point. cannot be used with “latitude” and “longitude” parameters
  • “latitude”: an expression to a number which will be used as the latitude coordinate. can only be used with “longitude” parameter
  • “longitude”: an expression to a number which will be used as the longitude coordinate. can only be used with “latitude” parameter

Syntax:

{
    $geoHash: {
        "coordinates": <expression to [longitude, latitude] coordinate array or geojson point>
        "precision": <int>
    }
}

or

{
    $geoHash: {
        "longitude": <expression to number>
        "longitude": <expression to number>
        "precision": <int>
    }
}

Example usage:

> db.geo_date.find()
{ "_id" : 0, "coordinates": [ -123.129825, 49.287201 ] }

> db.geo_date.aggregate( {$project: {geo_hash: {$geoHash: {coordinates: "$coordinates", precision: 7}}}} )
{ "_id" : 0, "geo_hash" : "c2b2qh7" }

precision levels:

Precision Bounding box
1 ~ 5,004km x 5,004km
2 ~ 1,251km x 625km
3 ~ 156km x 156km
4 ~ 39km x 19.5km
5 ~ 4.9km x 4.9km
6 ~ 1.2km x 0.61km
7 ~ 152.8m x 152.8m
8 ~ 38.2m x 19.1m
9 ~ 4.78m x 4.78m
10 ~ 1.19m x 0.60m
11 ~ 14.9cm x 14.9cm
12 ~ 3.7cm x 1.8cm