Fi:Maastotietokanta/Työkaluja

Karttalehtijakomuunnos

CREATE OR REPLACE FUNCTION point2mml(geometry(point))
RETURNS character varying(7) as $$
   SELECT (ARRAY['K','L','M','N', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X'])
                [cast (floor((ST_YMin($1)-6474000)/96000) as integer)] ||
          (ARRAY['2','3','4','5','6'])
                [cast (floor((ST_XMin($1)+268000)/192000) as integer)]

          || 1 + cast(floor(mod((ST_YMin($1)-6474000)::numeric,
                                96000) / 48000) as integer) +
             2 * cast(floor(mod((ST_XMin($1)+268000)::numeric, 
                                192000) / 96000) as integer)
          || 1 + cast(floor(mod((ST_YMin($1)-6474000)::numeric,
                                48000) / 24000) as integer) +
             2 * cast(floor(mod((ST_XMin($1)+268000)::numeric,
                                96000) / 48000) as integer)
          || 1 + cast(floor(mod((ST_YMin($1)-6474000)::numeric,
                                24000) / 12000) as integer) + 
             2 * cast(floor(mod((ST_XMin($1)+268000)::numeric,
                                48000) / 24000) as integer)
          ||
          (ARRAY['A','B','C','D','E','F','G','H'])[
             1 + cast(floor(mod((ST_YMin($1)-6474000)::numeric,
                                12000) / 6000) as integer) +   
             2 * cast(floor(mod((ST_XMin($1)+268000)::numeric, 
                                24000) / 6000) as integer)
          ]
          || 1 + cast(floor(mod((ST_YMin($1)-6474000)::numeric,
                                6000) / 3000) as integer) +
             2 * cast(floor(mod((ST_XMin($1)+268000)::numeric,
                                6000) / 3000) as integer)
$$
LANGUAGE SQL
IMMUTABLE   
RETURNS NULL ON NULL INPUT;

Lähteet

Karttalehtijako: [1]