How to add array and hstore columns type in properties

[zachtrong]

I have following table in PostGIS, how can these columns like text[] and hstore mapping to properties in config.yml file?

create table if not exists public.real_estate
(
    id               serial primary key,
    address          text,
    description      text,
    images           text[],
    properties       text[],
    title            text,
    latitude         double precision,
    longitude        double precision,
    geom             geometry(Point, 4326),
    attributes       hstore,
    meta_info        hstore
);

alter table public.real_estate
    owner to gis;

create index if not exists real_estate_geom_gist
    on public.real_estate using gist (geom);

[nyurik]

There is currently no special handling of the table column types. During the discovery, all column types are are retrieved as a jsonb object. Later, the list of column names are passed to the ST_AsMVT postgis function, as is, without any special handling. I do not see any special documentation in ST_AsMVT, but I guess it mostly ignores unknown properties? No idea, would have to test.

You have two options:

• propose a clear predictable reusable way to handle every column type, and possibly introduce expressions into tables, e.g. map some prop A -> SQL expression. This might prove to be a powerful but rarely used feature
• just write your own function - if you know SQL well enough to write a config expression, might as well use a much more powerful approach

The second route is already available, whereas the first one would require a lot of discussion on how to do that best, and when agreed, someone would have to implement it :)

[zachtrong]

Well, the second solution works for me.

CREATE OR REPLACE
    FUNCTION function_zxy_real_estate(z integer, x integer, y integer)
    RETURNS bytea AS $$
DECLARE
  mvt bytea;
BEGIN
  SELECT INTO mvt ST_AsMVT(tile, 'function_zxy_real_estate', 4096, 'geom') FROM (
    SELECT
      ST_AsMVTGeom(
          ST_Transform(ST_CurveToLine(geom), 3857),
          ST_TileEnvelope(z, x, y),
          4096, 64, true) AS geom, title, address, description, array_to_json(images) as images, hstore_to_json(meta_info) as meta_info
    FROM public.real_estate
    WHERE geom && ST_Transform(ST_TileEnvelope(z, x, y), 4326)
  ) as tile WHERE geom IS NOT NULL;

  RETURN mvt;
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;