[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Maposmatic-dev] Re: [OSM-dev] How to support name:* in MapOSMatic?
From: |
Brian Quinion |
Subject: |
[Maposmatic-dev] Re: [OSM-dev] How to support name:* in MapOSMatic? |
Date: |
Fri, 8 Jan 2010 13:57:22 +0000 |
On Fri, Jan 8, 2010 at 12:57 PM, David MENTRE <address@hidden> 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