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.