Alternate names language codes

[samilyak]

Hi guys,

I need to show in my project city/region names in different languages. I see django-cities-light fetches alternateNames.zip from geonames.org, and I also see there's some multi-language list stored in alternate_names column.

It's very handy when you need to implement e.g. multi-language cities suggest box.

But if I need to show a specific city name in a specific language (e.g. in a language of a page UI), there's no way to get this data from cities_light_city table. There're no language codes preserved for each alternate name.

However in alternateNames.txt I do see language codes. Also there're languages of alternate names in geonames.org UI:

Here's config variables that I've overridden in my tests:

CITIES_LIGHT_TRANSLATION_LANGUAGES = ['en', 'de', 'fr', 'ru', 'es', 'bg', 'zh']
CITIES_LIGHT_INCLUDE_COUNTRIES = ['US']

My questions:

1. Is there a way to make django-cities-light preserve language codes of each alternate name for all cities/regions/countries in my DB (probably in a separate table)?
2. If not, how does django-cities-light use alternateNames.txt file data then?
3. Is there a config to prevent alternateNames.zip from being downloaded? It's pretty big and I can live without alternate names in my dev environment. I tried CITIES_LIGHT_TRANSLATION_SOURCES = [], but it failed of course.

[max-arnold]

Language support in django-cities-light definitely needs some work, and there are multiple ways to do that with different tradeoffs:

• each city in its own (country-specific) language
• one column per language
• separate table(s) with translations

Also this: #145

To answer your questions:

1. It is definitely possible, but not out of the box
2. Right now it is only used to populate alternate_names
3. I'll take a look. In my opinion CITIES_LIGHT_TRANSLATION_SOURCES = [] should work

[samilyak]

@max-arnold OK, I see, thx.

and there are multiple ways to do that with different tradeoffs

I'd go with one separate table, it's the most canonical way for translations IMO. Besides it is a possibility of course that there're more than one translation per language for a specific geonameid, so I'd keep them as separate rows of the same table.

People willing to have one table per language could extract them out of that one.

Would you prefer if I create a separate feature request for that?

1. It is definitely possible, but not out of the box

Some code patching required, you mean?

2. Right now it is only used to populate alternate_names

You didn't want to use just column 4 of cities15000 for that, because it's an uncontrollable mix of different stuff, right?

http://download.geonames.org/export/dump/:

The main 'geoname' table has the following fields :
...
alternatenames    : alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000)

I see e.g. for New York City column 4 is 90-items list of not-always-geographical names :)

3. I'll take a look. In my opinion CITIES_LIGHT_TRANSLATION_SOURCES = [] should work

Here's the exception that I have for v3.3.0 when CITIES_LIGHT_TRANSLATION_SOURCES = []:

$ ./manage.py cities_light --force-all
RAM used: 44488 kB Time: 0:00:00 Done: 100%|#################################################################################################################################################################|
RAM used: 44776 kB Time: 0:00:00 Done: 100%|#################################################################################################################################################################|
RAM used: 49016 kB ETA:  0:00:03 Done:  89%|################################################################################################################################################                 |Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 226, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorvalue
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 217, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 378, in _query
    rowcount = self._do_query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 341, in _do_query
    db.query(q)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1366, "Incorrect string value: '\\xF0\\x90\\x8D\\x83\\xF0\\x90...' for column 'alternate_names' at row 1")

If you have the same, do you want me to create a separate issue for that?

[ton77v]

@samilyak can you please share your solution for this (if any 😀)

I'm facing the same issue, if there are only 2 languages there is a easy (although not the best) one - can use the last alternative_name, seems that it's always in the secondary language. But can't figure out the best way for 3+ languages

Can use model-translations of course, but will have to find the way to get all those translations... probably there is an easier way?

[goromachine]

@samilyak can you share your solution please? I'm issuing the same problem right now and I fount this open thread.

Thank you

[grindnoise]

Maybe someone has a working solution?

[mcosti]

I have the same question unfortunately

[grindnoise]

Not an ideal workaround, but I've managed to add localization this way:

1. I've customized default classes with localized_names property:

#model.py
from django.db import models
from django.core.validators import MaxValueValidator, MinValueValidator
from django.contrib.contenttypes.fields import GenericForeignKey, GenericRelation
from django.contrib.contenttypes.models import ContentType
from cities_light.abstract_models import (AbstractCity, AbstractRegion,
    AbstractCountry, AbstractSubRegion)
from cities_light.receivers import connect_default_signals
import cities_light
from cities_light.settings import ICity
from common import LOCALES

class LocalizedItem(models.Model):
    translation = models.SlugField(max_length=200, allow_unicode=True)
    locale = models.CharField(max_length=20, choices=LOCALES, default = 'ru', null=False, blank=False)
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey('content_type', 'object_id')

    def __str__(self):
        return self.translation

class Country(AbstractCountry):
    localized_names = GenericRelation(LocalizedItem, related_query_name="country")
connect_default_signals(Country)

class SubRegion(AbstractSubRegion):
    localized_names = GenericRelation(LocalizedItem, related_query_name="subregion")
connect_default_signals(SubRegion)

class Region(AbstractRegion):
    localized_names = GenericRelation(LocalizedItem, related_query_name="region")
connect_default_signals(Region)

class City(AbstractCity):
    timezone = models.CharField(max_length=40)
    localized_names = GenericRelation(LocalizedItem, related_query_name="city")
    gmt_offset = models.IntegerField(default=0, validators=[MaxValueValidator(14), MinValueValidator(-12)])
connect_default_signals(City)

def set_city_fields(sender, instance, items, **kwargs):
    instance.timezone = items[ICity.timezone]
cities_light.signals.city_items_post_import.connect(set_city_fields)

2. GET request http://api.geonames.org/getJSON?formatted=true&geonameId=someId&username=yours returns existing localized names for geonameId, so you can work with this data and create LocalizedItem entries

[maqadeersaeed]

@grindnoise Sorry for my lack of knowledge, I am new to this, can you elaborate how does it fill records and names in LocalizedItem.