gnumed-devel
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Gnumed-devel] Updating dem listings for Brazil


From: Busser, Jim
Subject: Re: [Gnumed-devel] Updating dem listings for Brazil
Date: Wed, 16 Nov 2011 22:00:20 +0000

As a follow-on to that last post on

        Country zones and i18n

and recapping the specific case of Brazil, the source from which I am working 
offers

(1) a table of states (accented) -- but already taken care of that i18n

(2) an additional table that provides -- in the case of *cities* -- a table of 
unaccented and accented names, suitable to pass through

        i18n.i18n
        i18n.upd_tx

(3) lastly, a main set of tables supplying *accented* streets, cities, 
districts, states

So now, when I get to the insert of streets, I can try a subselect through the 
earlier-created i18n translations :

    SELECT d_u.id
    FROM staging.street s_s
    INNER JOIN dem.urb d_u
    ON UPPER(
        (SELECT DISTINCT i_t.orig
        FROM staging.street s_s
        INNER JOIN i18n.translations i_t
        ON UPPER(s_s.city) = UPPER(i_t.trans)
        WHERE i_t.lang = 'pt_BR'
        )
                )
        = UPPER(d_u.name)
    INNER JOIN dem.state d_s
    ON d_u.id_state = d_s.id
    INNER JOIN dem.country d_c
    ON d_s.country = d_c.code
    WHERE s_s.state = d_s.code AND d_c.code = 'BR' ; 

which depends on the availability of a 'translation' whether or not the string 
is accented, or unaccented. As it happens, the Brazilian source provided its 
unaccented/accented values as name pairs where -- if the case where even the 
proper name was unaccented -- the unaccented value was simply repeated in the 
position used for the accented name. If it is ok to accept that approach, I 
think I can finish this data pack :-)

-- Jim


reply via email to

[Prev in Thread] Current Thread [Next in Thread]