Автоматическое определение системы координат при импорте ЕГРН

Для установки модуля запустите скрипт

Common.SCTransform.sql

CREATE SCHEMA if not exists matching_coordinate_systems;

 /*
      Создание таблицы Сопоставления SourceId, ProjectAlias, AdditionalFilter и SkId c Srid, таблицы логов к ней и тригера их заполнения
 */

 CREATE TABLE if not exists matching_coordinate_systems.matches_srs
 (
     source_id varchar(150) NOT NULL,
     project_alias varchar(150) NOT NULL,
     additional_filter varchar(150) NOT NULL,
     sk_id varchar(150) NOT NULL,
     srid INT,
     CONSTRAINT d_matching_name_c_s_pkey PRIMARY KEY (source_id, project_alias, additional_filter, sk_id)
 );
 create index if not exists d_matching_name_c_s_idx on matching_coordinate_systems.matches_srs(source_id, project_alias, sk_id);

 CREATE TABLE if not exists matching_coordinate_systems.l_matches_srs
 (
     source_id varchar(150) NOT NULL,
     project_alias varchar(150) NOT NULL,
     additional_filter varchar(150) NOT NULL,
     sk_id varchar(150) NOT NULL,
     srid INT,
     date_edit timestamp NOT NULL,
     type_edit char(1) NOT NULL
 );

 CREATE OR REPLACE FUNCTION matching_coordinate_systems.f_log_matches_srs()
  RETURNS trigger
  LANGUAGE plpgsql
 AS $function$
 BEGIN
     case TG_OP
     when 'INSERT' then
               begin
                    INSERT INTO matching_coordinate_systems.l_matches_srs (source_id, project_alias, additional_filter, sk_id, srid, date_edit, type_edit) VALUES (NEW.source_id, NEW.project_alias, NEW.additional_filter, NEW.sk_id, NEW.srid, NOW(), 'i');
                    return NEW;
               end;
     when 'UPDATE' then
               begin
                    INSERT INTO matching_coordinate_systems.l_matches_srs (source_id, project_alias, additional_filter, sk_id, srid, date_edit, type_edit) VALUES (NEW.source_id, NEW.project_alias, NEW.additional_filter, NEW.sk_id, NEW.srid, NOW(), 'u');
                    return NEW;
               end;
     when 'DELETE' then
               begin
                    INSERT INTO matching_coordinate_systems.l_matches_srs (source_id, project_alias, additional_filter, sk_id, srid, date_edit, type_edit) VALUES (OLD.source_id, OLD.project_alias, OLD.additional_filter, OLD.sk_id, OLD.srid, NOW(), 'd');
                    return OLD;
               end;
     end  case;
 END;
 $function$
 ;

 DROP TRIGGER IF EXISTS trg_log_matches_srs on matching_coordinate_systems.matches_srs;

 create trigger trg_log_matches_srs before
 insert
 or
 delete
 or
 update
     on
     matching_coordinate_systems.matches_srs for each row execute procedure matching_coordinate_systems.f_log_matches_srs();


 /*
      Создание таблицы ошибок сопоставления
 */


 CREATE TABLE if not exists matching_coordinate_systems.error_matches_srs
 (
   import_id varchar(150) NOT NULL,
   source_id varchar(150) NOT NULL,
   project_alias varchar(150) NOT NULL,
   additional_filter varchar(150) NOT NULL,
   sk_id varchar(150) NOT NULL,
   date_create timestamp NOT NULL,
   CONSTRAINT d_error_matches_srs_pkey PRIMARY KEY (date_create)
   );
 create index if not exists error_matches_srs_idx on matching_coordinate_systems.error_matches_srs(import_id, source_id, project_alias, additional_filter, sk_id);


 /*
      Добавление фичи с её функциональными компонентами
 */

  create or replace function regadm.f_add_global_settings_feature(nameFeature in text, arrComponents in text) returns void
  as
  $$
  declare
      c bigint;
      sType int4:=20;
      rec record;
      tmpJsonFeature json;
      isExists boolean:=false;
      arrayCount integer:=1;
      data jsonb[];
      mainJson json;
      resJson text;
      tmp text;
  begin
      if not exists(select * from information_schema.tables where table_schema='regadm' and table_name='m_global_settings') then
              execute 'create table regadm.m_global_settings(setting_type int4,value text)';
      end if;

      select (value::jsonb)::json into mainJson from regadm.m_global_settings where setting_type=sType;

      if mainJson is not null then

          for rec in(select json_array_elements(((value::jsonb)->>'Features')::json) as arr from regadm.m_global_settings where setting_type=sType)
          loop
                  tmpJsonFeature:=rec.arr;
                  if (rec.arr->>'Name')=nameFeature then
                          isExists:=true;
                          tmpJsonFeature := jsonb_set(tmpJsonFeature::jsonb, '{ComponentsFunctionality}'::text[], arrComponents::jsonb, false);
                          raise info '%',tmpJsonFeature;
                  end if;

                  data[arrayCount]:=tmpJsonFeature::jsonb;
                  arrayCount:=arrayCount+1;
          end loop;

          if not isExists then
                  data[arrayCount] := CONCAT('{"Name":"', nameFeature, '","ComponentsFunctionality":', arrComponents, '}')::jsonb;
          end if;

          raise info '%',array_to_json(data);
          resJson := jsonb_set(mainJson::jsonb, '{Features}'::text[], array_to_json(data)::jsonb, false);
          raise info '%',resJson;

          update regadm.m_global_settings set value=resJson where setting_type=sType;
     else
          resJson:=CONCAT('{"Features":[{"Name":"', nameFeature, '","ComponentsFunctionality":', arrComponents, '}]}')::json;

          insert into regadm.m_global_settings (setting_type, value) values (sType, resJson);
  end if;
 end;
  $$language plpgsql;
 /*
      Удаление ограничения на NOT NULL для тех проектов, на которых был запущен только старая версия скрипта, при первом запуске этого скрипта на проект таблицы уже создадутся без этого ограничения.
 */
 ALTER TABLE matching_coordinate_systems.matches_srs ALTER COLUMN srid DROP NOT NULL;
 ALTER TABLE matching_coordinate_systems.l_matches_srs ALTER COLUMN srid DROP NOT NULL;
 select regadm.f_add_global_settings_feature('TransformCoordinateSystem', '["Gems.EgrnImporter.ConvertGeomBySRS", "Gems.Configurator.EgrnImporter"]');

После запуска скрипта модуль установится для всех проектов.

Выполните перезапуск службы импорта ЕГРН командой:

systemctl restart EgrnImporter

См.также

Как настроить модуль