Форумы GIS-Lab.info

Код: Выделить всё

CREATE TABLE public.gis_object_areas
(
  objectid integer NOT NULL DEFAULT nextval('gis_object_areas_objectid_seq'::regclass),
  object_id integer NOT NULL,
  area_date date NOT NULL,
  geometry geometry(Polygon,4284),
  name character varying(50)
);

Код: Выделить всё

CREATE OR REPLACE FUNCTION public.get_date_geometry(obj_id integer, test_date date) RETURNS geometry AS
DECLARE
  res public.geometry;
BEGIN
  SELECT geometry INTO res FROM gis_object_areas WHERE area_date=(SELECT MAX(area_date) FROM gis_object_areas WHERE (object_id = obj_id) AND (area_date <= test_date));
  RETURN res;
END;

Код: Выделить всё

CREATE OR REPLACE VIEW public.view_current_object_areas AS 
 SELECT o.id AS objectid,
    o.id AS object_id,
    get_date_geometry(o.id, date(now())) AS geometry,
    o.short_name,
    o.name,
    o.kind_id,
    o.region_id,
    get_date_status(o.id, date(now())) AS status
   FROM object_main o;