How long has anyone else in the Swarm been in my vicinity?

Some rough SQL to keep track of when pairs of players in a group first came within range of one and other. Mostly played about with in Access SQL but not very well tested…

There are 2 tables. The players table can be keyed and indexed on the Player field.

CREATE TABLE Players (
  Player INT NOT NULL,
  Name TEXT NOT NULL,
  Xpos DOUBLE NOT NULL,
  Ypos DOUBLE NOT NULL
);

The connections table contains the connections between all the pairs of players (so has n*(n-1)/2 records). It is only added to or deleted from when players join or leave the game. When players are not within range the FirstContact field is set to null, otherwise it is the time they first came within range. Indexing on Player1 and Player2 should give fast lookups. Note that Player1 and Player2 fields are in arbitrary order - so the connection between players 17 and 23 may be (17,23,null) or (23,17,null) but not both. This depends on the order they were created.

CREATE TABLE Connections (
  Player1 INT NOT NULL,
  Player2 INT NOT NULL,
  FirstContact DATETIME
);

Add a player just once, when a player joins the game.

INSERT INTO Players ( Player, Name, Xpos, Ypos )
SELECT [Player# ?] AS Player, [Name?] AS Name,
       [Initial X POSITION?] AS Xpos, [Initial Y POSITION?] AS Ypos;
 
INSERT INTO Connections ( Player1, Player2 )
SELECT [Player# ?] AS NewPlayer, Players.Player
FROM Players
WHERE Players.Player <> [Player# ?];

Only delete a player when they leave the game altogether.

DELETE Players.Player
FROM Players
WHERE Players.Player = [Player# ?];
 
DELETE Connections.Player1, Connections.Player2
FROM Connections
WHERE Connections.Player1 = [Player# ?] OR
      Connections.Player2 = [Player# ?];

Update a player's connections whenever you get a new set of co-ordinates.

First, define a function which returns true when they are in range. This compares to the square of the range so it doesn't have to recalculate that each time. It takes a parameter, Strangers, which is true if they are currently out of range. Compare to InnerRangeSquared when they are Strangers and OuterRangeSquared when they are connected - this is so they won't wobble in and out when they are standing on the boundary.

Const InnerRangeSquared As Double
Const OuterRangeSquared As Double
 
Function CloseEncounter(x1 As Double, y1 As Double,
                        x2 As Double, y2 As Double,
                        Strangers As Boolean) As Boolean
CloseEncounter = (x1 - x2) ^ 2 + (y1 - y2) ^ 2
                  < IIf(Strangers, InnerRangeSquared, OuterRangeSquared)
End Function

Update the field FirstContact in the Connections table only when the player crosses the boundary. This happens when CloseEncounter() and IsNull(FirstContact) are either both true (now in range, previously out of range) or both false (now out of range, previously in range).

UPDATE (Connections
  INNER JOIN Players AS Players_1 ON
    Connections.Player1 = Players_1.Player)
  INNER JOIN Players AS Players_2 ON
    Connections.Player2 = Players_2.Player
  SET
    Connections.FirstContact = IIf(IsNull(FirstContact), NOW(), NULL)
  WHERE
    ([Player# ?] = Connections.Player1 Or
     [Player# ?] = Connections.Player2)
  AND
    CloseEncounter(Players_1.Xpos, Players_1.Ypos,
                   Players_2.Xpos, Players_2.Ypos,
                   IsNull(FirstContact))
      = IsNull(FirstContact);

vicinity php code

Tarim / April 2008 / for Swarm MediaSandbox project