maxxer location data in sanitised dump is not usable.

[corbosman]

The maxxer locations in the sanitised dump have faulty lat/lon values.

This would fix it if you want to fix it:

mysql> update maxxer_locations set x = ((RAND() * (70 + 70 )) - 70);
mysql> update maxxer_locations set y = ((RAND() * (180 + 180 )) - 180);

[numist]

Do you know if the gmap location rows in production are all valid?

[corbosman]

Yes, they seem valid. https://thismight.be/offensive/map/get_coordinates.php

The sanitised dump had some really funky data

0.203052,0.97733
-179.913,-89.4475

A lat of -179 doesnt exist.

The sql above should generate some correct random lat/lon pairs for the sanitised dump.

I double checked by taking my own coordinate from the above URL, and adding that to my dev db row. Showed my marker in the correct position.

[numist]

I wrote the sanitizer blindly thinking that lat/longs were ±180 (x/long) and ±90 (y/lat), I guess that's not true? What should the bounds be?

The decimal ones should have a mapversion of NULL, holdovers from the ancient maxxer world map.

[corbosman]

Ah, i see what happened. x/y got swapped in the maxxer_locations table. That probably has been that way since day 1. So x=lat, y=lon. Not as you'd want it, but changing that may be a hassle.

We could try to fix this. We'd have to fix the column names, and fix the code in a few spots to swap x/y back. Probably only in content/map/*.php

Or we leave it like this and fix the sanitised dump :)

[corbosman]

Numist, can you see what the production table says for x/y in maxxer_locations for userid 3871 (me)

Maybe it's right on production, but my own tables got mixed up. In which case I need to sort out my own database and make sure the code is ok.

[corbosman]

Ahha! I think i see the problem (please still double check the x/y on production)

My columns are not in order on my dev db. No idea why. Ive got y before x, so the dump reads in x as y and vice versa. I probably didnt really think about it and mistakenly swapped the x/y back in code instead of realising my db is wrong.

So, if thats the case, I need to fix the code and send another PR.

[numist]

mysql> SELECT * FROM maxxer_locations WHERE userid = 3871;
+------+--------+---------+---------+------------+---------------------+
| id   | userid | y       | x       | mapversion | timestamp           |
+------+--------+---------+---------+------------+---------------------+
| 8134 |   3871 | 4.89115 | 52.4236 | google     | 2009-06-05 08:51:08 |
+------+--------+---------+---------+------------+---------------------+
1 row in set (0.00 sec)

[corbosman]

Ok, so it's actually also wrong on the production db of tmbo. Amsterdam is 4 lon, 52 lat. So the current code should work. In the code I swap the 2 back, so lat=x, lon=y. The only thing is that for some reason the dump is wrong, probably because you (like me) assumed the 2 coordinates to be x/y, not y/x.

I guess we just never noticed this.

What to do? Leave it as is? Try to fix the tables?

[numist]

May as well fix it, since the map is currently broken anyway. I'll look into this tomorrow.

[corbosman]

Any news? We could also do this in 2 stages. As far as I can tell, you should be able to just bring my code online and it'll work. If you ever want to swap the 2 columns around, that can be done at any time. Should be relatively trivial. Rename the column names in mysql, and in my code I think you just need to change it in 1 function.

I just want to prevent this pr from hanging around too long that it wont apply anymore :)

[numist]

Yeah, that's a good plan. I'm in Winnipeg next week which means there's a higher likelihood than normal that I'll have time to do server stuff.