[Maposmatic-dev] Re: [OSM-dev] How to support name:* in MapOSMatic?

[Brian Quinion]

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