Please update geo_tags per https://gerrit.wikimedia.org/r/#/c/180704/ (extensions/GeoData/sql/float-to-decimal.sql). This change is definitely not urgent, so can be done along with something else.
Description
Description
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Invalid | MaxSem | T96189 Geo coords still rounded if generator is used. | |||
| Resolved | jcrespo | T89986 Update GeoData schema |
Event Timeline
Comment Actions
Max is correct that this is not urgent, but it is causing the Wikipedia app to sometimes point users in the wrong direction when they get close to items in Nearby. An update on the ETA would be nice.
Comment Actions
I didn't even know this was pending. It is now on the backlog and will be done soon(TM).
Comment Actions
I got the following error when applying the changes on eswiki:
Copying rows caused a MySQL error 1265: Data truncated for column 'gt_lat' at row 1
I think this should be expected due to the change from float to decimal, but I would like you to give me the ok to proceed.
Comment Actions
This is a summary of the differences, the format change changes the value due to floats being inexact.
Left is after the schema change, right is 24 hours ago. Is this ok to continue?
gt_id gt_page_id gt_globe gt_primary gt_la gt_id gt_page_id gt_globe gt_primary gt_la 4179 5154 earth 0 -62.16249847 -58.46250153 | 4179 5154 earth 0 -62.1625 -58.4625 4196 5695 earth 0 39.50500107 -0.38361111 | 4196 5695 earth 0 39.505 -0.383611 1000 4200 5695 earth 0 39.54750061 -0.41749999 | 4200 5695 earth 0 39.5475 -0.4175 1000 NULL 4204 5695 earth 0 39.50500107 -0.38361111 | 4204 5695 earth 0 39.505 -0.383611 1000 4205 5695 earth 0 39.38249969 -0.33250001 | 4205 5695 earth 0 39.3825 -0.3325 1000 NULL 4237 8411 earth 0 38.63000107 -90.19999695 | 4237 8411 earth 0 38.63 -90.2 10000 city 4268 10123 earth 1 22.00000000 96.00000000 | 4268 10123 earth 1 22 96 1000000 count 4277 10643 earth 0 1.85000002 9.75000000 | 4277 10643 earth 0 1.85 9.75 1000 NULL 4278 10643 earth 0 3.50000000 8.69999981 | 4278 10643 earth 0 3.5 8.7 1000 NULL 4281 10643 earth 0 0.98333335 9.50000000 | 4281 10643 earth 0 0.983333 9.5 1000 4322 11606 earth 0 50.42309952 6.20170021 | 4322 11606 earth 0 50.4231 6.2017 1000 landm 4352 13681 earth 1 39.95220184 -75.19049835 | 4352 13681 earth 1 39.9522 -75.1905 1000 4361 14148 earth 0 35.50000000 -90.50000000 | 4361 14148 earth 0 35.5 -90.5 1000 NULL 4364 16075 earth 1 39.00000000 32.00000000 | 4364 16075 earth 1 39 32 1000 NULL 4371 16891 earth 0 90.00000000 0.00000000 | 4371 16891 earth 0 90 0 10000 water 4372 16891 earth 0 81.65000153 0.00000000 | 4372 16891 earth 0 81.65 0 10000 water 4374 16891 earth 0 61.00000000 0.00000000 | 4374 16891 earth 0 61 0 10000 water 4375 16891 earth 0 53.75000000 0.00000000 | 4375 16891 earth 0 53.75 0 1000000 count 4385 16891 earth 0 11.10000038 0.00000000 | 4385 16891 earth 0 11.1 0 1000000 count 4386 16891 earth 0 11.10000038 0.00000000 | 4386 16891 earth 0 11.1 0 1000000 count 4387 16891 earth 0 10.94999981 0.00000000 | 4387 16891 earth 0 10.95 0 1000000 count 4388 16891 earth 0 10.60000038 0.00000000 | 4388 16891 earth 0 10.6 0 1000000 count 4389 16891 earth 0 7.80000019 0.00000000 | 4389 16891 earth 0 7.8 0 10000 water 4391 16891 earth 0 0.00000000 0.00000000 | 4391 16891 earth 0 0 0 1000 landm 4392 16891 earth 0 -60.00000000 0.00000000 | 4392 16891 earth 0 -60 0 10000 water 4393 16891 earth 0 -68.90000153 0.00000000 | 4393 16891 earth 0 -68.9 0 1000000 count 4394 16891 earth 0 -90.00000000 0.00000000 | 4394 16891 earth 0 -90 0 1000 landm 4398 17144 earth 0 51.76110077 -1.25339997 | 4398 17144 earth 0 51.7611 -1.2534 1000 edu 4455 26732 earth 0 36.18999863 -5.92000008 | 4455 26732 earth 0 36.19 -5.92 1000 NULL 4582 1669926 earth 0 19.70000076 -101.19999695 | 4582 1669926 earth 0 19.7 -101.2 1000 NULL 4587 33180 earth 1 33.09999847 44.09999847 | 4587 33180 earth 1 33.1 44.1 1000 NULL 4697 37293 earth 1 4.64900017 -74.10859680 | 4697 37293 earth 1 4.649 -74.1086 1000 4722 38173 earth 1 41.65000153 -0.89999998 | 4722 38173 earth 1 41.65 -0.9 10000 city 4759 40152 earth 1 37.88800049 -4.86700010 | 4759 40152 earth 1 37.888 -4.867 1000 NULL
Comment Actions
Yes, this is expected - for example, in the first line, -62.1625 is not actually changed to -62.16249847 - rather, MySQL was rounding the latter to the former which caused T78715 (smart rounding being too smart with no way to work around it). So while the schema change will make the values "ugly", after it's done I will refresh them by null-editing coordinate templates on large wikis at which point -62.1625 will be stored precisely.
Comment Actions
All wikis on dblists have been updated. New schema:
mysql -A -h s1-master.eqiad.wmnet enwiki -e "SHOW CREATE TABLE geo_tags\G"
*************************** 1. row ***************************
Table: geo_tags
Create Table: CREATE TABLE `geo_tags` (
`gt_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`gt_page_id` int(10) unsigned NOT NULL,
`gt_globe` varbinary(32) NOT NULL,
`gt_primary` tinyint(1) NOT NULL,
`gt_lat` decimal(11,8) DEFAULT NULL,
`gt_lon` decimal(11,8) DEFAULT NULL,
`gt_dim` int(11) DEFAULT NULL,
`gt_type` varbinary(32) DEFAULT NULL,
`gt_name` varbinary(255) DEFAULT NULL,
`gt_country` binary(2) DEFAULT NULL,
`gt_region` varbinary(3) DEFAULT NULL,
PRIMARY KEY (`gt_id`),
KEY `gt_page_primary` (`gt_page_id`,`gt_primary`),
KEY `gt_page_id_id` (`gt_page_id`,`gt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=209082858 DEFAULT CHARSET=binary