Last modified: 2013-11-18 15:24:49 UTC
mysql:wikiadmin@db1034 [svwiki]> select * from geo_tags where gt_page_id=1683947\G *************************** 1. row *************************** gt_id: 4310017 gt_page_id: 1683947 gt_globe: earth gt_primary: 0 gt_lat: 50.0833 gt_lon: 2.4 gt_dim: 1000 gt_type: landmark gt_name: gt_country: FR gt_region: NULL *************************** 2. row *************************** gt_id: 4310019 gt_page_id: 1683947 gt_globe: earth gt_primary: 0 gt_lat: 50.0833 gt_lon: 2.4 gt_dim: 1000 gt_type: landmark gt_name: gt_country: FR gt_region: NULL *************************** 3. row *************************** gt_id: 4310016 gt_page_id: 1683947 gt_globe: earth gt_primary: 1 gt_lat: 50.0833 gt_lon: 2.4 gt_dim: 1000 gt_type: landmark gt_name: gt_country: FR gt_region: NULL *************************** 4. row *************************** gt_id: 4310018 gt_page_id: 1683947 gt_globe: earth gt_primary: 1 gt_lat: 50.0833 gt_lon: 2.4 gt_dim: 1000 gt_type: landmark gt_name: gt_country: FR gt_region: NULL The primary ones are completely unacceptable.
[enwiki_p]> select * from geo_tags t1 where gt_primary=1 and exists(select 1 from geo_tags t2 where t2.gt_primary=1 and t1.gt_id <> t2.gt_id and t1.gt_page_id=t2.gt_page_id); +----------+------------+----------+------------+----------+----------+--------+----------+---------+------------+-----------+ | gt_id | gt_page_id | gt_globe | gt_primary | gt_lat | gt_lon | gt_dim | gt_type | gt_name | gt_country | gt_region | +----------+------------+----------+------------+----------+----------+--------+----------+---------+------------+-----------+ | 63027971 | 5236504 | earth | 1 | -23.6167 | -52.6167 | 10000 | city | | BR | NULL | | 63027972 | 5236504 | earth | 1 | -23.6167 | -52.6167 | 10000 | city | | BR | NULL | | 63027974 | 5236504 | earth | 1 | -23.6167 | -52.6167 | 10000 | city | | BR | NULL | | 63595895 | 532228 | earth | 1 | 40.7299 | -73.5922 | 1000 | NULL | | NULL | NULL | | 63595909 | 532228 | earth | 1 | 40.7299 | -73.5922 | 1000 | NULL | | NULL | NULL | | 65479076 | 16161886 | earth | 1 | 42.4953 | 2.74417 | 1000 | landmark | | FR | NULL | | 65479077 | 16161886 | earth | 1 | 42.4953 | 2.74417 | 1000 | landmark | | FR | NULL | | 65479078 | 16161886 | earth | 1 | 42.4953 | 2.74417 | 1000 | landmark | | FR | NULL | | 65479191 | 1448335 | earth | 1 | 7.01083 | 100.497 | 1000 | edu | | TH | NULL | | 65479192 | 1448335 | earth | 1 | 7.01083 | 100.497 | 1000 | edu | | TH | NULL | | 65660805 | 26374590 | earth | 1 | 35.4636 | 7.0825 | 10000 | city | | DZ | NULL | | 65660813 | 26374590 | earth | 1 | 35.4636 | 7.0825 | 10000 | city | | DZ | NULL | | 65937527 | 7811151 | earth | 1 | 48.0225 | -122.08 | 1000 | edu | | US | WA | | 65937529 | 7811151 | earth | 1 | 48.0225 | -122.08 | 1000 | edu | | US | WA | | 65975721 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65975722 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65975723 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65975724 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65975725 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65975726 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65975727 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65975728 | 5482619 | earth | 1 | 40.8215 | -74.2266 | 1000 | edu | | US | NULL | | 65977340 | 38000270 | earth | 1 | 11.5928 | 79.4714 | 1000 | landmark | | IN | NULL | | 65977343 | 38000270 | earth | 1 | 11.5928 | 79.4714 | 1000 | landmark | | IN | NULL | | 65980779 | 19366815 | earth | 1 | 47.5261 | -122.035 | 1000 | edu | | US | WA | | 65980780 | 19366815 | earth | 1 | 47.5261 | -122.035 | 1000 | edu | | US | WA | | 66093030 | 3235827 | earth | 1 | 55.8792 | -3.93253 | 1000 | NULL | | GB | NULL | | 66093032 | 3235827 | earth | 1 | 55.8792 | -3.93253 | 1000 | NULL | | GB | NULL | | 66119660 | 1819156 | earth | 1 | 15.6122 | 32.5422 | 1000 | edu | | SD | 03 | | 66119661 | 1819156 | earth | 1 | 15.6122 | 32.5422 | 1000 | edu | | SD | 03 | | 66122388 | 1243036 | earth | 1 | 44.4383 | 26.0514 | 1000 | edu | | NULL | NULL | | 66122389 | 1243036 | earth | 1 | 44.4383 | 26.0514 | 1000 | edu | | NULL | NULL | | 66325979 | 60142 | earth | 1 | 53.4958 | 20.1344 | 5000 | event | | PL | NULL | | 66325983 | 60142 | earth | 1 | 53.4958 | 20.1344 | 5000 | event | | PL | NULL | | 66329257 | 36636 | earth | 1 | 24.4667 | 39.6 | 10000 | city | | SA | NULL | | 66329265 | 36636 | earth | 1 | 24.4667 | 39.6 | 10000 | city | | SA | NULL | | 66471472 | 3985 | earth | 1 | 42.9047 | -78.8494 | 10000 | city | | US | NY | | 66471477 | 3985 | earth | 1 | 42.9047 | -78.8494 | 10000 | city | | US | NY | | 66505909 | 5180508 | earth | 1 | 7.13667 | -73.1167 | 1000 | NULL | | NULL | NULL | | 66505915 | 5180508 | earth | 1 | 7.13667 | -73.1167 | 1000 | NULL | | NULL | NULL | | 66506631 | 107752 | earth | 1 | 39.4097 | -123.356 | 10000 | city | | US | CA | | 66506634 | 107752 | earth | 1 | 39.4097 | -123.356 | 10000 | city | | US | CA | | 66506642 | 107752 | earth | 1 | 39.4097 | -123.356 | 10000 | city | | US | CA | | 66506654 | 107752 | earth | 1 | 39.4097 | -123.356 | 10000 | city | | US | CA | | 66506655 | 107752 | earth | 1 | 39.4097 | -123.356 | 10000 | city | | US | CA | | 66538807 | 361527 | earth | 1 | 36.9 | 30.6833 | 10000 | city | | TR | NULL | | 66538808 | 361527 | earth | 1 | 36.9 | 30.6833 | 10000 | city | | TR | NULL | | 66567127 | 147954 | earth | 1 | -15.9333 | -50.1333 | 100000 | adm1st | | BR | GO | | 66567152 | 147954 | earth | 1 | -15.9333 | -50.1333 | 100000 | adm1st | | BR | GO | | 66567155 | 147954 | earth | 1 | -15.9333 | -50.1333 | 100000 | adm1st | | BR | GO | | 66574751 | 4443 | earth | 1 | 41.3833 | 2.18333 | 10000 | city | | ES | NULL | | 66574769 | 4443 | earth | 1 | 41.3833 | 2.18333 | 10000 | city | | ES | NULL | | 66574816 | 4443 | earth | 1 | 41.3833 | 2.18333 | 10000 | city | | ES | NULL | | 66574989 | 4443 | earth | 1 | 41.3833 | 2.18333 | 10000 | city | | ES | NULL | | 66575000 | 4443 | earth | 1 | 41.3833 | 2.18333 | 10000 | city | | ES | NULL | | 66575024 | 4443 | earth | 1 | 41.3833 | 2.18333 | 10000 | city | | ES | NULL | | 66637165 | 425258 | earth | 1 | 34.0833 | -118.448 | 10000 | city | | US | NULL | | 66637188 | 425258 | earth | 1 | 34.0833 | -118.448 | 10000 | city | | US | NULL | | 66764555 | 6886 | earth | 1 | 41.8819 | -87.6278 | 10000 | city | | US | IL | | 66764597 | 6886 | earth | 1 | 41.8819 | -87.6278 | 10000 | city | | US | IL | | 66819805 | 378111 | earth | 1 | 38.9217 | -77.02 | 1000 | landmark | | US | DC | | 66819807 | 378111 | earth | 1 | 38.9217 | -77.02 | 1000 | landmark | | US | DC | | 66819809 | 378111 | earth | 1 | 38.9217 | -77.02 | 1000 | landmark | | US | DC | | 66838441 | 160278 | earth | 1 | 10.3167 | 123.75 | 125000 | adm2nd | | PH | NULL | | 66838448 | 160278 | earth | 1 | 10.3167 | 123.75 | 125000 | adm2nd | | PH | NULL | | 66838480 | 160278 | earth | 1 | 10.3167 | 123.75 | 125000 | adm2nd | | PH | NULL | | 66864542 | 236843 | earth | 1 | 13.8333 | 121 | 10000 | city | | PH | NULL | | 66864547 | 236843 | earth | 1 | 13.8333 | 121 | 10000 | city | | PH | NULL | | 66876990 | 2099624 | earth | 1 | 32.6333 | 110.8 | 10000 | city | | CN | 42 | | 66876991 | 2099624 | earth | 1 | 32.6333 | 110.8 | 10000 | city | | CN | 42 | | 66929259 | 13774 | earth | 1 | 29.7628 | -95.3831 | 10000 | city | | US | TX | | 66929275 | 13774 | earth | 1 | 29.7628 | -95.3831 | 10000 | city | | US | TX | +----------+------------+----------+------------+----------+----------+--------+----------+---------+------------+-----------+ 72 rows in set (5.83 sec)
Or: select gt_page_id, count(*) as num from geo_tags where gt_primary=1 group by gt_page_id having num > 1; +------------+-----+ | gt_page_id | num | +------------+-----+ | 3985 | 2 | | 4443 | 6 | | 6886 | 2 | | 13774 | 2 | | 36636 | 2 | | 60142 | 2 | | 107752 | 5 | | 147954 | 3 | | 160278 | 3 | | 236843 | 2 | | 361527 | 2 | | 378111 | 3 | | 425258 | 2 | | 532228 | 2 | | 1243036 | 2 | | 1448335 | 2 | | 1819156 | 2 | | 2099624 | 2 | | 3235827 | 2 | | 5180508 | 2 | | 5236504 | 3 | | 5482619 | 8 | | 7811151 | 2 | | 16161886 | 3 | | 19366815 | 2 | | 26374590 | 2 | | 38000270 | 2 | +------------+-----+ 27 rows in set (1.12 sec)
To quote Max - https://gerrit.wikimedia.org/r/88903 should vastly improve this.
Max, can you describe the specific problem for those of us not particularly familiar with GeoData? That will make it easier to review and evaluate the associated patchset.
Is this fixed (the patch was merged)? Also how do I test.
(In reply to comment #4) There are more changes observed to geo_tags than expected from usual editing which rarely involves coordiante change. I'm attempting to reduce such changes to see if this helps. (In reply to comment #5) > Is this fixed (the patch was merged)? We'll see. > Also how do I test. You can't
