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)
          || 1 +
             cast(floor(mod((ST_XMin($1)+268000)::numeric, 
                                24000) / 12000) 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;

CREATE OR REPLACE FUNCTION point2mml_6x6(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)
          || 1 +
             cast(floor(mod((ST_XMin($1)+268000)::numeric, 
                                24000) / 12000) 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;

CREATE OR REPLACE FUNCTION mmlto3067(character varying(7))
RETURNS geometry as $$
select case when $1 similar to '[K-NP-X][2-6][1-4][1-4][1-4][12]' then
         ST_SetSRID(ST_MakeEnvelope(x, y, x+12000, y+12000), 3067)
       else
         null
       end
from
(select
        substring($1 from 2 for 1)::int*192000-460000 +
        (substring($1 from 3 for 1)::int-1)/2*96000 +
        (substring($1 from 4 for 1)::int-1)/2*48000 +
        (substring($1 from 5 for 1)::int-1)/2*24000
        + (substring($1 from 6 for 1)::int-1)/2*12000
--      + (position(substring($1 from 6 for 1) in
--                 'ABCDEFGH')-1)::int/2*6000 +
--        (substring($1 from 7 for 1)::int-1)/2*3000
        as x
       ,
        position(substring($1 from 1 for 1) in
                 'KLMNPQRSTUVWX')*96000+6474000 +
        ((substring($1 from 3 for 1)::int-1)::bit & b'1')::int*48000 +
        ((substring($1 from 4 for 1)::int-1)::bit & b'1')::int*24000 +
        ((substring($1 from 5 for 1)::int-1)::bit & b'1')::int*12000
--      + (position(substring($1 from 6 for 1) in
--                  'ACEGBDFH')-1)::int/4*6000 +
--        ((substring($1 from 7 for 1)::int-1)::bit & b'1')::int*3000
        as y
       ) as pt
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION mmlto3067_6x6(character varying(6))
RETURNS geometry as $$
select case when $1 similar to '[K-NP-X][2-6][1-4][1-4][1-4][A-H]' then
         ST_SetSRID(ST_MakeEnvelope(x, y, x+6000, y+6000), 3067)
       else
         null
       end
from
(select
        substring($1 from 2 for 1)::int*192000-460000 +
        (substring($1 from 3 for 1)::int-1)/2*96000 +
        (substring($1 from 4 for 1)::int-1)/2*48000 +
        (substring($1 from 5 for 1)::int-1)/2*24000 +
        (position(substring($1 from 6 for 1) in
                 'ABCDEFGH')-1)::int/2*6000
--      +  (substring($1 from 7 for 1)::int-1)/2*3000
        as x
       ,
        position(substring($1 from 1 for 1) in
                 'KLMNPQRSTUVWX')*96000+6474000 +
        ((substring($1 from 3 for 1)::int-1)::bit & b'1')::int*48000 +
        ((substring($1 from 4 for 1)::int-1)::bit & b'1')::int*24000 +
        ((substring($1 from 5 for 1)::int-1)::bit & b'1')::int*12000 +
        (position(substring($1 from 6 for 1) in
                  'ACEGBDFH')-1)::int/4*6000
--      +  ((substring($1 from 7 for 1)::int-1)::bit & b'1')::int*3000
        as y
       ) as pt
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;


-- select ST_AsText(mmlto3067('K23442'));
-- select ST_AsText(ST_Transform(ST_SetSRID(ST_Point(27.1, 60), 4326), 3067)),
--       point2mml(ST_Transform(ST_SetSRID(ST_Point(27.1, 60), 4326), 3067)),
--       mmlto3067(point2mml(ST_Transform(ST_SetSRID(ST_Point(27.1, 60), 4326), 3067)));

select ST_AsText(pt.column1),
       point2mml(pt.column1),
       ST_AsText(mmlto3067(point2mml(pt.column1)))
from
   (values (ST_SetSRID(ST_Point(500000, 6666000), 3067)),
           (ST_SetSRID(ST_Point(691000, 6666000), 3067)),
           (ST_SetSRID(ST_Point(691000, 6761000), 3067)),
           (ST_SetSRID(ST_Point(500000, 6761000), 3067))
          ) pt;
