Matchmaking search SQL query

delgelato

Halo!

My game uses latitude and longitude of the current player, and I'm trying to include that in the matchmaking.
I can use GetDistance from System.Device.Location to get distance from 2 coordinates.
I thought I can just have C0 = latitude, C1 = longitude and make a SQL query using that, but then I have to somehow recreate GetDistance as a SQL query.
Is it possible for the client to also do its own calculation for the matchmaking?
Or if there's another approach?

Cheers!

[Deleted User]

Hi @delgelato,

if you haven't done this already, I would recommend you taking a look at the Matchmaking Guide. It covers a section about SQL Lobbies with an example, which might help you.

JohnTube

Hi @delgelato,

Thank you for choosing Photon!

You probably can use an SQL SELECT clause where you calculate a distance (= the difference between two points, a point has the two coordinates C0 = latitude and C1 = longitude) which should be lower or equal than a value of your choice.
distance = f(<C0, C1>, <X, Y>) (example)
{distance} < V
But this may be overcomplicating things.
I don't know your exact use case.

delgelato

Hi, yes I've read that and there's C0 - C9 variable to be used in a SQL query to be included in JoinRandom.
If only distance from coordinates are simple vectors...
The GetDistance from the System.Device namespace uses some formula that accounts for Earth's curvature and so on so.. I doubt I can recreate that in SQL.


Anyways, I should explain the use case (and apparently it's changed a little bit to compensate things after I wrote this thread.):

There's Activities, and they're in Categories. Each Act has coordinate.

The matchmaking goes like this:
1. User A picks Sport, Movie
User B picks Sport, Music
This means A can be matched with B under "Sport"
I've accomplished this with bitmask (number of category is under 32 for this to work).
(Lets say the index are: Sport (1), Movie (2), Music (3)
So user A can create room with C0 = (1 << 1) + (1 << 2)
User B does JoinRandom with C0 | (1 << 1 + 1 << 3) != 0
This works nicely, but this was before I added the location distance into account...

2. Now it seems impossible to recreate GetDistance in SQL, and apparently it's not even necessary. It's not like A tries to match with B if it's within range. A tries to match with B if there exist an Activity that's within range from both A and B. So..
I can only think of a way to implement this WITHIN a dedicated room to do all the matchmake with existing users. <b class="Bold">This is where I need to know if it's still possible to utilize the SQL query
Lets only consider what's inside category Sport for now, there's Beach soccer and Mountain basketball.
A gets list of all Act under Sport (and Movie, but who cares). Then, locally, filters the result to only list the ones within range. I'll use the GetDistance locally for this whole process. A lives next to the beach. After filter, A's possible Act is only Beach soccer.
B lives between the beach and the mountain, and sets the distance quite high. So in the end:
A: Beach soccer
B: Beach soccer, mountain basketball.

So Category will be less than 32, but total Act per category can be much more than that. So even if I use C0 for the bitmask check, put it in a @variable (SQLite has @variable right?), then compare C1/2/3 (a user can choose max 3 category for the search), the bitmask won't hold more than 32 distance in/valid Acts.
It seems to me, matchmaking in a room and handling each difference/similarities of chosen Cats and valid Acts by combination of PlayerProperties and RPC, then save a matched PhotonID to be used when the 2 player goes and create their own room, is the only way left to do this.

I hope this is clear. Thanks for reading!

delgelato

I'd like to change my question, but still about SQL query.
After further thinking, I've decided to stick with the SQL query and rely on bitmask comparing. The issue of the number of Act will be many, I'll be adding another filter to categorize each Act based on Region, so 32 (or 64) Act per Cat PER Region is a reasonable number.
I'm now having trouble to make an SQL query that can compare in cases where B's query compares his C2 with the room's property, it could be C3, C4 or C5 based on the result of the bitmask compare in C1

So say Room A has
C0 = Category bitmask, 10011 (means Cat #1, 4, 5 are chosen)
C1 = Activity bitmask for Cat #1, say 111
C2 = Act bitmask for Cat #4, 110
C3 = Act bitmask for Cat #5, 100

B's chosen options:
Chosen categories are Cat #2,3,4 = Cat bitmask 01110
Act bitmask for Cat #2, say 111
Act bitmask for Cat #3,110
Act bitmask for Cat #4,100

I'm not sure if declaring @variable is possible just in WHERE (instead of SELECT abc INTO @var)..
and I hope the CASE from the SQLite doc works here. B's query would be something like this?
catMask1 = 1 << 2
catMask2 = 1 << 3
catMask3 = 1 << 4
actMask1 = 111
actMask2 = 110
actMask3 = 100

WHERE
CASE WHEN (C1 | " + catMask1 + ") == catMask1 THEN (C2 | " + actMask1 + ") != 0 END
(and repeat for actMask2 & 3)

And this, if it works, won't even solve the case if the <b class="Bold">existing Cat (#4 in this case) is the 2nd in the Room's option and the 3rd in the searcher's chosen Cat.. This only compares the room's C1 to own's first chosen Cat, room's C2 to 2nd Cat, and so on.

Thanks!