[Top][All Lists]
[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
- Re: [Gnumed-devel] Updating dem listings for Brazil, (continued)
Re: [Gnumed-devel] Updating dem listings for Brazil, Karsten Hilbert, 2011/11/15
- Re: [Gnumed-devel] Updating dem listings for Brazil, Busser, Jim, 2011/11/15
- Re: [Gnumed-devel] Updating dem listings for Brazil, Busser, Jim, 2011/11/16
- Re: [Gnumed-devel] Updating dem listings for Brazil, Karsten Hilbert, 2011/11/16
- Re: [Gnumed-devel] Updating dem listings for Brazil, Busser, Jim, 2011/11/16
- Re: [Gnumed-devel] Updating dem listings for Brazil, Busser, Jim, 2011/11/16
- Re: [Gnumed-devel] Updating dem listings for Brazil, Karsten Hilbert, 2011/11/16
- Re: [Gnumed-devel] Updating dem listings for Brazil,
Busser, Jim <=
- Re: [Gnumed-devel] Updating dem listings for Brazil, Karsten Hilbert, 2011/11/16
- Re: [Gnumed-devel] Updating dem listings for Brazil, Busser, Jim, 2011/11/16
- Re: [Gnumed-devel] Updating dem listings for Brazil, Karsten Hilbert, 2011/11/17
Re: [Gnumed-devel] Updating dem listings for Brazil, Karsten Hilbert, 2011/11/17