[OSM-dev] How to support name:* in MapOSMatic?
Brian Quinion
openstreetmap at brian.quinion.co.uk
Fri Jan 8 13:57:22 GMT 2010
On Fri, Jan 8, 2010 at 12:57 PM, David MENTRE <dmentre at linux-france.org> wrote:
> The ideal configuration would be to have all names (name:fr, name:ar,
> ...) into a single "name" column and use it. Or have a kind of couple
> (name, country_code) that would store all different names for a
> street. But I don't know how to do this.
That is how the gazetteer output from osm2pgsql handles it already.
It creates a type of keyvalue as:
CREATE TYPE keyvalue AS (
key TEXT,
value TEXT
);
and then has a column of type keyvalue[] (keyvalue array)
All name values are then merged into this field during the osm2pgsql
import. It ends up with a field value like:
{"(name:en,Peschanka)","(name,Песчанка)","(alt_name,Pesnanka)"}
Then a function is used to retrieve the name in the required value by
specifying a preference order:
CREATE OR REPLACE FUNCTION get_name_by_language(name keyvalue[],
languagepref TEXT[]) RETURNS TEXT
AS $$
DECLARE
search TEXT[];
found BOOLEAN;
BEGIN
IF (array_upper(name, 1) is null) THEN
return null;
END IF;
search := languagepref;
FOR j IN 1..array_upper(search, 1) LOOP
FOR k IN 1..array_upper(name, 1) LOOP
IF (name[k].key = search[j] AND trim(name[k].value) != '') THEN
return trim(name[k].value);
END IF;
END LOOP;
END LOOP;
RETURN null;
END;
$$
LANGUAGE plpgsql IMMUTABLE;
select get_name_by_language(namearray,
ARRAY['name:en','name:de','name']) from table;
--
Brian
More information about the dev
mailing list