[wp-meta] [Making WordPress.org] #2823: Improve IP Geolocation Results
noreply at wordpress.org
Wed May 17 10:28:35 UTC 2017
#2823: Improve IP Geolocation Results
Reporter: iandunn | Owner: iandunn
Type: defect | Status: accepted
Priority: normal | Milestone:
Component: API | Resolution:
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
* `get_valid_country_codes()` wasn't actually being used (though was
* `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
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
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/>
More information about the wp-meta