I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages.
Thus, if you search for "Vienna" or "Wien" from Europe it returns the location of Vienna, Austria, but if you do the same search from within the US, next to one of the many cities called "Vienna", that smaller city might appear above the EU one.
Technically, what I understand it needs two requirements from a DB system:
Geo-aware, thus for each search result it should return it's distance from a query point.
Handle the dozens of alternative-names of places in a smart way. For example "isafjo" should match "Ísafjörður" in an autocompliete.
Cache/index in a way that search results can be provided near real-time, thus allowing an autocomplete experience on the client side.
My ideas for selecting the DB system so far only met PostgreSQL and ElasticSearch, both of which supports geo-features.
I have the following questions:
If I go with PostgreSQL + PostGIS, how would you implement the alternative names? Would a table have as many fields as languages on Wikipedia, with mostly NULL values? Or would you put all the alternative names in a JSON field? Or this is exactly the use case for hstore? Or two tables with a join?
Would ElasticSearch or some alternative technology be better for this, compared to using PostgreSQL? How is the performance of PostgreSQL when used only for searching? I wouldn't be interested in full-text search, but only search in one of the alternative-names of a place (a dozen or so short strings).