Query to find account-sharing violations (superman radar)

Question

I'm trying to implement a tracking system for a commercial product, to help with detecting account-sharing violations. To that end, the system has the following tracking table:

• created - date/time when an HTTP request was made
• user_id - id of the user that made the request
• lat - latitude of the IP address from which the request came
• lng - longitude of the IP address from which the request came

I'm completely stuck trying to implement a PostgreSQL (+Postgis) query that would give me a list of entries in the last N hours, grouped by user_id, for which there is at least one violation record - request that was made within M minutes from the original, while at a distance farther than can be crossed with direct line, moving at the speed of S km/h.

It should reveal all users that managed to make requests from such distance from each other that they could not cross at such speed and time interval, unless they are a superman.

For example, a typical set of parameters would be: Locate and group all users who in the last 24 hours managed to use the system from two or more locations within 10-minute interval, while at such distance it could not be crossed by moving in direct line at 120km/h.

Answer 1

I'd add a proper geometry(Point,3857) column so that you don't have to litter your query with geometry construction.

alter table tracking 
  add column point geometry (Point,3857) 
  generated always as (st_setsrid(st_makepoint(lat,lng),3857)) stored;

With that, you can use window functions to get the earlier/later location, st_distance() between them and time it took to move, st_makeline() to also see the path. Demo with examples:

with 
 distance_and_time as (
    select created, user_id, st_astext(point),
      st_distance(       point,  lag(point)  over w1)/1000 as move_dist_km,
      st_makeline(       point,  lag(point)  over w1)      as move_path,
      extract(epoch from created-lag(created)over w1)/3600 as move_time_h
    from tracking 
    where created >= now() - '1 hour'::interval * 24 --last N hours
    window w1 as (partition by user_id order by created)
    order by user_id,created  )
,speeds as (
    select *, move_dist_km/move_time_h as move_speed_kmph 
    from distance_and_time  )
,violations as (
    select user_id, created from speeds
    where   move_time_h * '1 hour'::interval 
            <= '1 minute'::interval * 10     --M minutes from the original
    and   move_speed_kmph > 120  )           --moving at the speed of S km/h
select *, (s.user_id,s.created)=(v.user_id,v.created) as is_violation 
from speeds s inner join violations v using (user_id);

1. st_distancespheroid() will typically provide higher precision, which you don't necessarily need.
2. Make sure what units and order lat and lng are in and that you're aware of how SRS work.
3. It might be useful to later use QGIS to inspect in the suspicious users' paths, plotting them with st_makeline(point order by created), or by building line segments out of their consecutive locations, like above.
4. Depending on what system this is and how you react to violations, I'd expect someone to probe you by spoofing different speeds in different time periods to try and figure out your parameters.