[wp-meta] [Making WordPress.org] #2823: Improve IP Geolocation Results

[Making WordPress.org]

#2823: Improve IP Geolocation Results
---------------------+-----------------------
 Reporter:  iandunn  |       Owner:  iandunn
     Type:  defect   |      Status:  accepted
 Priority:  normal   |   Milestone:
Component:  API      |  Resolution:
 Keywords:           |
---------------------+-----------------------

Comment (by dd32):

 [attachment:2823.diff] takes a pretty harsh attack on some of the code in
 order to attempt to get the performance better for location searches.

  * `get_city_from_coordinates()` is gone - It's just not performant enough
 and I couldn't find an easy way to achieve decent performance for such an
 edgecase
  * `get_valid_country_codes()` wasn't actually being used (though was
 querying)
  * `guess_location_from_geonames_fallback()` and as a result
 `get_ideographic_counties()` is gone - this at first seems like a step
 backwards, however, I think what it's attempting to achieve was going
 about things the wrong way.
   * `REPLACE( alternatenames, CONCAT( asciiname, ' - ' ), '' ) LIKE`
 wasn't performant and there wasn't much of a chance of it being so
   * using a `alternatenames LIKE '%,Blah,%'` was working, but wasn't
 really fast

 In order to speed things up, I created a new table based off the `geoname`
 data, `geoname_summary` which expands the `(name, asciiname,
 alternatenames)` out into a single `name` column, and also adds variants
 (for example the city name without dashes).

 One major change in this is that if you type in, for example, `The Big
 Apple` the location returned will now be `The Big Apple` instead of `New
 York City`, similarly, if you search for `Wien` you'll get just that
 `Wien` not `Vienna`.
 Unfortunately typo's will stay as typo's (for example, `New Yorke` or
 `Lunden`).

 This might also make it easier to return multiple results in the future
 (`Springfield, MO` vs `Springfield, MA` for example, or allowing an en_AU
 locale to pull up `Wellington, NZ` instead of `Wellington, AU`).

 All the test cases that passed are still passing albeit with the new
 return data, but instead of the ~100 queries taking 4-6 seconds, it now
 takes 0.04s for a slightly fewer number of queries.

--
Ticket URL: <https://meta.trac.wordpress.org/ticket/2823#comment:28>
Making WordPress.org <https://meta.trac.wordpress.org/>
Making WordPress.org