Este assunto é composto por dois artigos. O primeiro trata da importação de arquivos ENC S57 para um banco de dados Postgresql/postgis. O segundo trata da configuração de uma simbologia automática equivalente a cartas náuticas. Você pode fazer download de todos os scripts deste artigo em https://www.nasca.ovh/downloads/fichiers_enc_postgis1.7z ou vá diretamente para o GItHub: https://github.com/SigEtTerritoires/enc_postgis
Recomendamos que descompacte todos os arquivos sql (.sql) e batch (.bat) em um diretório de sua escolha, tendo em mente que, para usá-los, será necessário alterar o diretório usado nos scripts para o nome do seu diretório.
Para a simbologia, como você verá mais adiante, você terá a opção de criar um diretório padrão: C:/nautical (recomendado) ou um diretório de sua escolha.
O formato S57 e o GIS
Se você vai começar a trabalhar com arquivos S57 em um GIS, há alguns aspectos que você precisa conhecer para navegar sem problemas.
Em primeiro lugar, a estrutura dos arquivos S57 não corresponde às estruturas adotadas no GIS.
Em um GIS, você tem um objeto geográfico que é representado por uma tabela com dois tipos de informações: a geometria das entidades no objeto e os atributos dessas entidades.
Se você tiver outros objetos com geometrias idênticas, as informações geométricas serão duplicadas, uma vez em cada tabela.
No formato S57, o principal objetivo é otimizar o armazenamento de informações e, portanto, não duplicar informações. Se um objeto tiver uma entidade de ponto, será criado um ponto. Se outros objetos tiverem entidades localizadas nesse ponto, será usada a referência do ponto que já foi criado. Dessa forma, um ponto é descrito apenas uma vez no arquivo. O mesmo se aplica a polilinhas e superfícies. Portanto, um arquivo S57 terá uma série de tabelas contendo informações geométricas, conhecidas como “primitivas”:
- IsolatedNode (pontos)
- ConnectedNode (pontos)
- Edge (polilinhas)
- Face (polígonos)
A tabela de atributos para os vários objetos S57 contém apenas os atributos do objeto.
Tipos de geometria
S57 “camadas” são classes de objetos. Por exemplo, as diferentes áreas de terra são codificadas na classe de objeto LNDARE.
A definição dessa classe de objeto é :
Objeto geográfico: Área de terra (LNDARE) (P,L,A)
Atributos: CONDTN OBJNAM NOBJNM STATUS INFORM NINFOM
Embora todos os objetos LNDARE tenham os mesmos atributos, o mesmo não pode ser dito sobre o tipo de geometria. As informações (P, L, A) indicam que pontos, linhas e polígonos podem ser encontrados nessa classe de objetos. Diferentemente dos padrões GIS, os três tipos de geometria coexistem na mesma classe de objeto.
Para passar do formato S57 para um banco de dados Postgresql/Postgis, usaremos a biblioteca GDAL, incluída no QGis.
As operações com a GDAL serão realizadas na linha de comando em uma janela do OSGeo4W.
A sintaxe básica para processar um arquivo S57 (extensão .000) e importá-lo para um banco de dados Postgtresql/Postgis é a seguinte
ogr2ogr -f PostgreSQL PG: “dbname=‘postgis_34_sample’ host=‘localhost’ port=‘5434’ user=‘postgres’ password=‘psw’ active_schema=‘schema’” arquivo .000
Veremos as diferentes opções a serem usadas e suas funções, mas, por enquanto, adicionaremos as opções -skipfailures -append -update.
ogr2ogr -skipfailures -append –update -f PostgreSQL PG:"dbname='postgis_34_sample' host='localhost' port='5434' user='postgres' password='psw' active_schema='schema'" fichier.000A primeira permite que o processamento continue mesmo se forem detectados erros. Os próximos dois permitem que não sobrescrevamos as tabelas postgresql de saída, se elas existirem, mas que as atualizemos adicionando os dados do arquivo S57 de entrada.
Qualquer que seja o formato escolhido para integração no QGis, precisaremos criar uma camada para cada tipo de geometria. Se não fizermos isso, o GDAL criará o tipo de camada com base na primeira entidade encontrada durante a conversão. Se ela for do tipo ponto, a camada criada será do tipo ponto e as entidades linhas e polígonos serão ignoradas. Da mesma forma, se a primeira entidade for do tipo linha, os pontos e polígonos serão ignorados.
Também deve ser observado que o formato S57 não tem restrições sobre classes de objetos: se não houver nenhuma classe de objeto na área coberta pelo arquivo S57, não haverá nada sobre ela no arquivo (nenhuma camada vazia). Da mesma forma, se houver apenas um tipo de geometria presente, mesmo que todos os três tipos sejam possíveis, não haverá nenhum vestígio dos outros tipos de geometria.
O processamento de um arquivo S57 com ogr2ogr deve, portanto, ser dividido em três estágios, um para cada tipo de geometria. As opções a seguir permitem que você processe cada classe de objeto S57 selecionando apenas um tipo de geometria::
-where « OGR_GEOMETRY=’POINT’ or OGR_GEOMETRY=’MULTIPOINT’ »-where « OGR_GEOMETRY=’LINESTRING’ or OGR_GEOMETRY=’MULTILINESTRING’ »-where « OGR_GEOMETRY=’POLYGON’ or OGR_GEOMETRY=’MULTIPOLYGON’ »Para determinados tipos de driver, o GDAL permite que você crie prefixos nas tabelas de saída. Nesse caso, você poderia criar todas as tabelas (pontos, linhas, polígonos) em um único esquema, prefixando-as com pt_, li_ e pl_, por exemplo. O problema é que o driver S57 do GDAL não permite essa opção. Portanto, precisamos criar três esquemas separados, nos quais as tabelas serão criadas de acordo com o tipo de geometria. Cada esquema conterá uma tabela com o mesmo nome, mas com uma geometria diferente.
Aqui, usaremos três esquemas de importação para comandos ogr2ogr, para os quais importaremos tabelas de um arquivo S57. Nós os chamaremos de pointsenc, linesenc e polysenc. Também criaremos um esquema chamado ENC para o banco de dados completo. Para cada mapa ENC, importaremos seu conteúdo para os três esquemas de importação com o ogr2ogr e, em seguida, executaremos consultas SQL para atualizar o banco de dados do esquema ENC com as novas tabelas importadas. Quer você tenha um único arquivo S57 para carregar ou um lote de arquivos S57 para carregar simultaneamente, o processo é o seguinte:
- Carregamento de classes de objeto Point no esquema pointsENC com o ogr2ogr
- Carregar classes de objeto Lines no esquema LinesENC usando ogr2ogr
- Carregar classes de objeto Polygon no esquema PolysENC usando ogr2ogr
- Remoção de tabelas vazias dos três esquemas
- Atualizar as tabelas existentes no esquema ENC dos três esquemas de importação
- Clonagem de tabelas dos três esquemas de importação que não estavam presentes no esquema ENC,
- Exclusão de tabelas dos três esquemas de importação
Processamento de sondas batimétricas
As sondas batimétricas apresentam vários problemas durante a conversão de formato. O primeiro é que o valor da profundidade não está contido em um atributo, mas como Z na geometria (XYZ). O segundo é que as sondas não são do tipo Ponto, mas do tipo Multiponto. Para obter o valor das sondas diretamente em um campo de atributo, é necessário adicionar dois parâmetros à linha de comando do ogr2ogr:
-oo SPLIT_MULTIPOINT=ON -oo ADD_SOUNDG_DEPTH=ON
O primeiro converte entidades multiponto em pontos individuais, e o segundo adiciona um campo de atributo “DEPTH” à tabela de saída com o valor Z da geometria.
Recuperação de identificadores “pai”
Alguns objetos podem ser agrupados por um objeto pai não geográfico. Por exemplo, os setores de incêndio são codificados com um setor por registro na tabela “LIGHTS”. Os diferentes setores do mesmo incêndio não têm nenhum atributo específico que possa indicar que eles correspondem à mesma entidade. Essa informação está contida em um registro “pai”. Para recuperar esse identificador como um atributo da tabela LIGTHS, é necessário adicionar uma opção à linha de comando:
-oo RETURN_LINKAGES=O
Essa opção cria um atributo name_rcid que é comum a todos os setores no mesmo semáforo, mas também cria uma série de campos (name_rcnm,ORNT,USAG,MASK).
Processamento do tipo “Lista
Além dos tipos de campo tradicionais (inteiro, real, texto), o formato S57 usa um tipo especial: listas de cadeias de caracteres ou inteiros. Esses tipos de campo são encontrados no PostgreSQL, mas não nos shapefiles e no geopackage. Portanto, não é necessário transformar as listas S57 em cadeias de caracteres da mesma forma que para esses formatos. De fato, isso é fortemente desencorajado, pois complicaria o processamento de atributos do tipo lista.
Gerenciamento de informações duplicadas
Ao carregar vários mapas ENC no mesmo banco de dados, você pode se deparar com uma duplicação de informações quando a cobertura de dois ou mais mapas se sobrepõe.
Essas duplicatas podem ser de dois tipos diferentes:
Duplicatas “verdadeiras”, em que todos os atributos são os mesmos. Essas são raras porque resultam da sobreposição de duas sobreposições de mapas de escala muito semelhante. No entanto, elas também podem ser o resultado de um erro ao recarregar um arquivo S57 duplicado. Essas duplicatas podem ser excluídas.
Duplicatas “falsas”, em que as informações da camada são duplicadas, mas os identificadores de registro não são necessariamente idênticos. Isso ocorre quando áreas mapeadas em escalas diferentes são carregadas no mesmo banco de dados. Além disso, dependendo da escala, a mesma informação (por exemplo, uma obstrução) pode ter uma geometria diferente (ponto no mapa, área em outro). Esse tipo de duplicação não deve ser excluído, mas gerenciado.
Para gerenciar essas duplicações e também para garantir que as informações sejam exibidas corretamente, é necessário adicionar atributos de mapa às camadas. Depois de integradas a uma camada, as entidades de diferentes arquivos não têm nenhum atributo que permita rastreá-las até sua origem.
Incluímos o código necessário no procedimento de importação SQL para adicionar o nome do arquivo, a escala de compilação de dados e a finalidade do gráfico a todas as tabelas do esquema.
No que diz respeito ao nome, pode ser útil na manutenção posterior do banco de dados poder selecionar as entidades correspondentes a um arquivo de origem S57.
Embora o nome do mapa seja de uso relativo, o mesmo não pode ser dito da escala, pois esse é um critério essencial na busca de duplicatas. Quando entidades de vários arquivos S57 são misturadas, os dados de diferentes escalas coexistirão de forma mais ou menos feliz.
Além da escala, há uma informação que pode ser muito útil: a finalidade do mapa. Esse é um valor entre 1 e 6 e corresponde ao objetivo principal do mapa:
- 1: Visão geral
- 2: Geral
- 3: Litoral
- 4: Aproximação
- 5: Porto
- 6: Atracação
Importamos 4.500 arquivos S57 para o banco de dados do projeto usando o PostgreSQL/Postgis.
Os valores mínimo e máximo da escala de todos os mapas para o objetivo 5 são 3000 e 60000. Os valores mínimo e máximo de escala para o objetivo 6 são 2500 e 15000. É evidente que os valores de escala dos mapas mais detalhados encontram-se nos mapas do tipo 5.
Aqui está o resultado para todos os objetivos:
| Tabela de objetivos e escalas | ||
| Purpose | min_scale | max_scale |
| 1 | 325000 | 10000000 |
| 2 | 100000 | 1534076 |
| 3 | 50000 | 600000 |
| 4 | 12500 | 150000 |
| 5 | 3000 | 60000 |
| 6 | 2500 | 15000 |
A tabela DSID para arquivos S57 contém dois atributos: o nome do arquivo S57 e a escala de compilação de dados, bem como o atributo “purpose” (finalidade). Esse atributo é encontrado na mesma tabela DSID usada para recuperar a escala com o nome DSID_INTU.
Como os comandos ogr2ogr carregam apenas tabelas espaciais por padrão, precisaremos executar um comando especial solicitando que a tabela DSID seja carregada:
ogr2ogr -skipfailures -append -update -f PostgreSQL PG: “dbname… “DSID
Importação de “primitivas
Portanto, um arquivo S57 tem uma série de tabelas que contêm informações geométricas, conhecidas como “primitivas”:
- IsolatedNode (pontos)
- ConnectedNode (pontos)
- Edge (polilinhas)
- Face (polígonos)
A tabela de atributos para os vários objetos S57 contém apenas os atributos do objeto.
O que complica a tarefa é que há dois atributos que se referem às geometrias: posacc (a precisão estimada da posição, um valor quantitativo) e quapos (qualidade da posição, uma variável qualitativa).
Esses dois atributos podem ser encontrados nas tabelas primitivas.
Para mudar da estrutura S57 para uma estrutura GIS (shapefile, geopackage, postgis), usamos a biblioteca GDAL e seu comando ogr2ogr.
Esse comando cria tabelas GIS a partir da estrutura S57, criando uma tabela por objeto S57, atribuindo a geometria correspondente das tabelas primitivas a cada entidade e adicionando os atributos do objeto S57 a cada entidade. O rastreamento das primitivas usadas para a geometria de cada entidade pode ser encontrado no campo NAME_RCID das tabelas GIS, desde que as opções -oo “RETURN_LINKAGES=ON” -oo “LNAM_REFS=ON” tenham sido adicionadas à linha de comando do ogr2ogr.
A figura abaixo mostra uma camada do tipo ponto. O valor indicado no campo NAME_RCID é o RCID do ponto usado na tabela IsolatedNode.
LA figura a seguir mostra um exemplo de uma camada do tipo linear. Os valores indicados no campo NAME_RCID são os dos RCIDs das polilinhas usadas na tabela Edge.
A figura a seguir mostra um exemplo de uma camada do tipo polígono. Os valores indicados no campo NAME_RCID são os das polilinhas usadas na tabela Edge.
Para recuperar os atributos QUAPOS e POSACC de cada entidade nas tabelas de tipo de ponto, precisamos recuperar os valores do ponto IsolatedNode e atribuí-los às tabelas dos vários objetos ENC.
Se os identificadores fossem diretamente os RCIDs nas tabelas ENC, poderíamos fazer uma junção entre cada tabela (soundg, obstrn,…) e IsolatedNode. Mas, como você pode ver nas imagens anteriores, o atributo NAME_RCID é do tipo stringlist, o que bloqueia essa solução. Portanto, desenvolvemos uma consulta SQL que faz esse trabalho ao carregar dados dos esquemas de importação para o esquema ENC.
Esquemas do PostgreSQL
Para criar tabelas de importação em um esquema específico, é necessário usar a opção active_schema do ogr2ogr. Com essa opção, o PostgreSQL permite que você crie tabelas com o mesmo nome em vários esquemas diferentes. Há apenas uma exceção, o esquema Público. Se um nome de tabela for usado nesse esquema, a opção active_schema será ignorada. Portanto, você deve ter cuidado para não criar tabelas S57 no esquema Público. Se isso acontecer, você terá de excluí-las manualmente, uma a uma.
Criação com uma consulta SQL
A consulta a seguir cria os quatro esquemas necessários:
CREATE SCHEMA IF NOT EXISTS encm Para criar esquemas, abra o pgAdmin4 e, em Schemas, abra o menu de contexto->Create-Schema Digite o nome desejado para o esquema: Repita essa operação para criar os quatro esquemas necessários para o nosso banco de dados ENC. ATENÇÃO: Os dois métodos de criação não dão o mesmo resultado. Quando você usa o pgAdmin, os nomes dos esquemas diferenciam maiúsculas de minúsculas e são colocados entre aspas (“ENCpoints”, por exemplo). Quando você usa o SQL, os nomes sempre estarão em letras minúsculas e não haverá vírgulas invertidas (pointsenc, por exemplo). Para evitar isso, não use letras maiúsculas no pgAdmin.. No restante deste artigo, usaremos os nomes da consulta SQL. Se você optar por usar letras maiúsculas, precisará modificar os nomes dos esquemas no código fornecido. A fase de importação de tabelas com o ogr2ogr não se limita à simples execução do comando de transcodificação S57->Postgresql. Depois que o esquema tiver sido preenchido com as tabelas de classes do arquivo S57, adicionaremos a tabela DSID, que será usada para recuperar o nome do arquivo S57, a escala de compilação de dados e a finalidade do mapa. Antes de fazer isso, precisamos adicionar esses atributos a todas as tabelas não vazias resultantes do comando ogr2ogr, aproveitando a oportunidade para criar também os atributos QUAPOS e POSACC que serão preenchidos posteriormente. O princípio dessa etapa é o seguinte:
AUTHORIZATION pg_database_owner;
GRANT ALL ON SCHEMA encm TO pg_database_owner;
CREATE SCHEMA IF NOT EXISTS pointsenc
AUTHORIZATION pg_database_owner;
GRANT ALL ON SCHEMA pointsenc TO pg_database_owner;
CREATE SCHEMA IF NOT EXISTS linesenc
AUTHORIZATION pg_database_owner;
GRANT ALL ON SCHEMA linesenc TO pg_database_owner;
CREATE SCHEMA IF NOT EXISTS polysenc
AUTHORIZATION pg_database_owner;
GRANT ALL ON SCHEMA polysenc TO pg_database_owner;Criação com o pgAdmin
Preparação de esquemas de importação
Esse procedimento é executado para cada tipo de geometria: ponto, linha e polígono.
Para que esse procedimento funcione, precisamos configurar alguns elementos no banco de dados Postgresql.
Tabelas DSID para esquemas de importação
Como usaremos um acionador nessas tabelas, elas precisam ser criadas antes do primeiro carregamento. Portanto, em cada esquema de importação, criaremos uma tabela
- Pointsdsid no esquema pointsenc
- Linesdsid no esquema linesenc
- Polysdsid no esquema polysenc
-- Création de la séquence pointsdsid_ogc_fid_seq
CREATE SEQUENCE IF NOT EXISTS pointsenc.pointsdsid_ogc_fid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;-- Création de la séquence polysdsid_ogc_fid_seqCREATE SEQUENCE IF NOT EXISTS polysenc.polysdsid_ogc_fid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1; -- Création de la séquence linesdsid_ogc_fid_seqCREATE SEQUENCE IF NOT EXISTS linesenc.linesdsid_ogc_fid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
-- Table: pointsenc.pointsdsid-- DROP TABLE IF EXISTS pointsenc.pointsdsid;CREATE TABLE IF NOT EXISTS pointsenc.pointsdsid
(
ogc_fid integer NOT NULL DEFAULT nextval('pointsenc.pointsdsid_ogc_fid_seq'::regclass),
dsid_expp numeric(3,0),
dsid_intu numeric(3,0),
dsid_dsnm character varying COLLATE pg_catalog."default",
dsid_edtn character varying COLLATE pg_catalog."default",
dsid_updn character varying COLLATE pg_catalog."default",
dsid_uadt character varying(8) COLLATE pg_catalog."default",
dsid_isdt character varying(8) COLLATE pg_catalog."default",
dsid_sted numeric(11,6),
dsid_prsp numeric(3,0),
dsid_psdn character varying COLLATE pg_catalog."default",
dsid_pred character varying COLLATE pg_catalog."default",
dsid_prof numeric(3,0),
dsid_agen numeric(5,0),
dsid_comt character varying COLLATE pg_catalog."default",
dssi_dstr numeric(3,0),
dssi_aall numeric(3,0),
dssi_nall numeric(3,0),
dssi_nomr numeric(10,0),
dssi_nocr numeric(10,0),
dssi_nogr numeric(10,0),
dssi_nolr numeric(10,0),
dssi_noin numeric(10,0),
dssi_nocn numeric(10,0),
dssi_noed numeric(10,0),
dssi_nofa numeric(10,0),
dspm_hdat numeric(3,0),
dspm_vdat numeric(3,0),
dspm_sdat numeric(3,0),
dspm_cscl numeric(10,0),
dspm_duni numeric(3,0),
dspm_huni numeric(3,0),
dspm_puni numeric(3,0),
dspm_coun numeric(3,0),
dspm_comf numeric(10,0),
dspm_somf numeric(10,0),
dspm_comt character varying COLLATE pg_catalog."default",
CONSTRAINT pointsdsid_pkey PRIMARY KEY (ogc_fid)
)TABLESPACE pg_default;ALTER TABLE IF EXISTS pointsenc.pointsdsid
OWNER to postgres;
-- Table: polysenc.polysdsid-- DROP TABLE IF EXISTS polysenc.polysdsid;CREATE TABLE IF NOT EXISTS polysenc.polysdsid
(
ogc_fid integer NOT NULL DEFAULT nextval('polysenc.polysdsid_ogc_fid_seq'::regclass),
dsid_expp numeric(3,0),
dsid_intu numeric(3,0),
dsid_dsnm character varying COLLATE pg_catalog."default",
dsid_edtn character varying COLLATE pg_catalog."default",
dsid_updn character varying COLLATE pg_catalog."default",
dsid_uadt character varying(8) COLLATE pg_catalog."default",
dsid_isdt character varying(8) COLLATE pg_catalog."default",
dsid_sted numeric(11,6),
dsid_prsp numeric(3,0),
dsid_psdn character varying COLLATE pg_catalog."default",
dsid_pred character varying COLLATE pg_catalog."default",
dsid_prof numeric(3,0),
dsid_agen numeric(5,0),
dsid_comt character varying COLLATE pg_catalog."default",
dssi_dstr numeric(3,0),
dssi_aall numeric(3,0),
dssi_nall numeric(3,0),
dssi_nomr numeric(10,0),
dssi_nocr numeric(10,0),
dssi_nogr numeric(10,0),
dssi_nolr numeric(10,0),
dssi_noin numeric(10,0),
dssi_nocn numeric(10,0),
dssi_noed numeric(10,0),
dssi_nofa numeric(10,0),
dspm_hdat numeric(3,0),
dspm_vdat numeric(3,0),
dspm_sdat numeric(3,0),
dspm_cscl numeric(10,0),
dspm_duni numeric(3,0),
dspm_huni numeric(3,0),
dspm_puni numeric(3,0),
dspm_coun numeric(3,0),
dspm_comf numeric(10,0),
dspm_somf numeric(10,0),
dspm_comt character varying COLLATE pg_catalog."default",
CONSTRAINT polysdsid_pkey PRIMARY KEY (ogc_fid)
)TABLESPACE pg_default;ALTER TABLE IF EXISTS polysenc.polysdsid
OWNER to postgres;
-- Table: linesenc.linesdsid-- DROP TABLE IF EXISTS linesenc.linesdsid;CREATE TABLE IF NOT EXISTS linesenc.linesdsid
(
ogc_fid integer NOT NULL DEFAULT nextval('linesenc.linesdsid_ogc_fid_seq'::regclass),
dsid_expp numeric(3,0),
dsid_intu numeric(3,0),
dsid_dsnm character varying COLLATE pg_catalog."default",
dsid_edtn character varying COLLATE pg_catalog."default",
dsid_updn character varying COLLATE pg_catalog."default",
dsid_uadt character varying(8) COLLATE pg_catalog."default",
dsid_isdt character varying(8) COLLATE pg_catalog."default",
dsid_sted numeric(11,6),
dsid_prsp numeric(3,0),
dsid_psdn character varying COLLATE pg_catalog."default",
dsid_pred character varying COLLATE pg_catalog."default",
dsid_prof numeric(3,0),
dsid_agen numeric(5,0),
dsid_comt character varying COLLATE pg_catalog."default",
dssi_dstr numeric(3,0),
dssi_aall numeric(3,0),
dssi_nall numeric(3,0),
dssi_nomr numeric(10,0),
dssi_nocr numeric(10,0),
dssi_nogr numeric(10,0),
dssi_nolr numeric(10,0),
dssi_noin numeric(10,0),
dssi_nocn numeric(10,0),
dssi_noed numeric(10,0),
dssi_nofa numeric(10,0),
dspm_hdat numeric(3,0),
dspm_vdat numeric(3,0),
dspm_sdat numeric(3,0),
dspm_cscl numeric(10,0),
dspm_duni numeric(3,0),
dspm_huni numeric(3,0),
dspm_puni numeric(3,0),
dspm_coun numeric(3,0),
dspm_comf numeric(10,0),
dspm_somf numeric(10,0),
dspm_comt character varying COLLATE pg_catalog."default",
CONSTRAINT linesdsid_pkey PRIMARY KEY (ogc_fid)
)TABLESPACE pg_default;ALTER TABLE IF EXISTS linesenc.linesdsid
OWNER to postgres;Configuração dos acionadores AFTER INSERT
Depois de inserir uma linha na tabela DSID no esquema, esses acionadores
- Excluem todas as tabelas vazias no esquema,
- verificar se os campos adicionais já existem e, se não existirem, criar os 5 atributos ‘(enc_chart, scale, purpose,posacc e quapos),
- adicione os valores do registro DSID aos atributos enc_chart, scale e purpose,
- e, por fim, excluir o registro da tabela DSID.
Há uma função para cada tipo de geometria,
- create_fields_and_update_values_pointsenc()
- create_fields_and_update_values_linesenc()
- create_fields_and_update_values_polysenc()
em seguida, uma consulta SQL para configurar acionadores em cada uma das tabelas DSID (pointsDSID, linesDSID, polysDSID)
CREATE OR REPLACE FUNCTION create_fields_and_update_values_pointsenc()RETURNS TRIGGER AS
$$
DECLARE
table_record RECORD;
tables_import RECORD;
enc_chart_value TEXT;
scale_value NUMERIC;
purpose_value NUMERIC;
empty_tables int;
BEGIN
--Le code suivant permet de supprimer les tables vides d'un schéma d'import:
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'pointsenc' AND table_name != 'pointsdsid' LOOP
-- Composer une requête dynamique pour vérifier si la table est vide
EXECUTE format('SELECT COUNT(*) FROM pointsenc.%I', table_record.table_name) INTO empty_tables;-- Si le nombre de lignes est égal à zéro, supprimer la table
IF empty_tables = 0 THEN
EXECUTE format('DROP TABLE IF EXISTS pointsenc.%I CASCADE', table_record.table_name);
RAISE NOTICE 'Table pointsenc.%I supprimée car elle est vide.', table_record.table_name;
END IF;
END LOOP;
-- Parcours de toutes les tables du schéma pointsenc
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'pointsenc' AND table_name != 'pointsdsid' LOOP
-- Vérifie si les champs enc_chart et scale n'existent pas dans la table actuelle
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'pointsenc' AND table_name = table_record.table_name AND column_name IN ('enc_chart', 'scale','purpose')
) THEN
-- Crée le champ enc_chart de type texte
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN enc_chart TEXT', table_record.table_name);
-- Crée le champ scale de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN scale NUMERIC', table_record.table_name);
-- Crée le champ purpose de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN purpose NUMERIC', table_record.table_name);
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'pointsenc' AND table_name = table_record.table_name AND column_name IN ('posacc','quapos')
) THEN
-- Crée le champ POSACC de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN posacc NUMERIC(10,0)', table_record.table_name);
-- Crée le champ QUAPOS de type numérique
EXECUTE format('ALTER TABLE pointsenc.%I ADD COLUMN quapos INTEGER', table_record.table_name);
END IF;
RAISE NOTICE 'Champs enc_chart, scale , purpose, POSACC et QUIAPOS créés dans la table %', table_record.table_name;
END IF;
-- Obtient la valeur de enc_chart à partir de la table DSID pour DSID_DSNM
SELECT DSID_DSNM INTO enc_chart_value FROM pointsdsid LIMIT 1;
-- Obtient la valeur de scale à partir de la table DSID pour DSPM_CSCL
SELECT DSPM_CSCL INTO scale_value FROM pointsdsid LIMIT 1;
-- Obtient la valeur de purpose à partir de la table DSID pour DSID_INTU
SELECT DSID_INTU INTO purpose_value FROM pointsdsid LIMIT 1;
-- Met à jour les enregistrements avec les valeurs trouvées dans la table DSID
EXECUTE format('UPDATE pointsenc.%I SET enc_chart = $1 WHERE enc_chart IS NULL', table_record.table_name) USING enc_chart_value;
EXECUTE format('UPDATE pointsenc.%I SET scale = $1 WHERE scale IS NULL', table_record.table_name) USING scale_value;
EXECUTE format('UPDATE pointsenc.%I SET purpose = $1 WHERE purpose IS NULL', table_record.table_name) USING purpose_value;
END LOOP;
-- Efface l'enregistrement de la table DSID
DELETE FROM pointsenc.pointsdsid;
RETURN NULL;END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION create_fields_and_update_values_linesenc()
RETURNS TRIGGER AS
$$
DECLARE
table_record RECORD;
tables_import RECORD;
enc_chart_value TEXT;
scale_value NUMERIC;
purpose_value NUMERIC;
empty_tables int;
BEGIN
--Le code suivant permet de supprimer les tables vides d'un schéma d'import:
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'linesenc' AND table_name != 'linesdsid' LOOP
-- Composer une requête dynamique pour vérifier si la table est vide
EXECUTE format('SELECT COUNT(*) FROM linesenc.%I', table_record.table_name) INTO empty_tables;-- Si le nombre de lignes est égal à zéro, supprimer la table
IF empty_tables = 0 THEN
EXECUTE format('DROP TABLE IF EXISTS linesenc.%I CASCADE', table_record.table_name);
RAISE NOTICE 'Table linesenc.%I supprimée car elle est vide.', table_record.table_name;
END IF;
END LOOP;
-- Parcours de toutes les tables du schéma linesenc
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'linesenc' AND table_name != 'linesdsid' LOOP
-- Vérifie si les champs enc_chart et scale n'existent pas dans la table actuelle
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'linesenc' AND table_name = table_record.table_name AND column_name IN ('enc_chart', 'scale','purpose')
) THEN
-- Crée le champ enc_chart de type texte
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN enc_chart TEXT', table_record.table_name);
-- Crée le champ scale de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN scale NUMERIC', table_record.table_name);
-- Crée le champ purpose de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN purpose NUMERIC', table_record.table_name);
RAISE NOTICE 'Champs enc_chart, scale et purpose créés dans la table %', table_record.table_name;
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'linesenc' AND table_name = table_record.table_name AND column_name IN ('posacc','quapos')
) THEN
-- Crée le champ POSACC de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN POSACC NUMERIC(10,0)', table_record.table_name);
-- Crée le champ QUAPOS de type numérique
EXECUTE format('ALTER TABLE linesenc.%I ADD COLUMN QUAPOS INTEGER', table_record.table_name);
END IF;
RAISE NOTICE 'Champs enc_chart, scale et purpose créés dans la table %', table_record.table_name;
END IF;
-- Obtient la valeur de enc_chart à partir de la table DSID pour DSID_DSNM
SELECT DSID_DSNM INTO enc_chart_value FROM linesenc.linesdsid LIMIT 1;
-- Obtient la valeur de scale à partir de la table DSID pour DSPM_CSCL
SELECT DSPM_CSCL INTO scale_value FROM linesenc.linesdsid LIMIT 1;
-- Obtient la valeur de purpose à partir de la table DSID pour DSID_INTU
SELECT DSID_INTU INTO purpose_value FROM linesenc.linesdsid LIMIT 1;
-- Met à jour les enregistrements avec les valeurs trouvées dans la table DSID
EXECUTE format('UPDATE linesenc.%I SET enc_chart = $1 WHERE enc_chart IS NULL', table_record.table_name) USING enc_chart_value;
EXECUTE format('UPDATE linesenc.%I SET scale = $1 WHERE scale IS NULL', table_record.table_name) USING scale_value;
EXECUTE format('UPDATE linesenc.%I SET purpose = $1 WHERE purpose IS NULL', table_record.table_name) USING purpose_value;
END LOOP;
-- Efface l'enregistrement de la table DSID
DELETE FROM linesenc.linesdsid;
RETURN NULL;END;
$$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION create_fields_and_update_values_polysenc()
RETURNS TRIGGER AS
$$
DECLARE
table_record RECORD;
tables_import RECORD;
enc_chart_value TEXT;
scale_value NUMERIC;
purpose_value NUMERIC;
empty_tables int;
BEGIN
--Le code suivant permet de supprimer les tables vides d'un schéma d'import:
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'polysenc' AND table_name != 'polysdsid' LOOP
-- Composer une requête dynamique pour vérifier si la table est vide
EXECUTE format('SELECT COUNT(*) FROM polysenc.%I', table_record.table_name) INTO empty_tables;-- Si le nombre de lignes est égal à zéro, supprimer la table
IF empty_tables = 0 THEN
EXECUTE format('DROP TABLE IF EXISTS polysenc.%I CASCADE', table_record.table_name);
RAISE NOTICE 'Table polysenc.%I supprimée car elle est vide.', table_record.table_name;
END IF;
END LOOP;
-- Parcours de toutes les tables du schéma polysenc
FOR table_record IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'polysenc' AND table_name != 'polysdsid' LOOP
-- Vérifie si les champs enc_chart et scale n'existent pas dans la table actuelle
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'polysenc' AND table_name = table_record.table_name AND column_name IN ('enc_chart', 'scale','purpose')
) THEN
-- Crée le champ enc_chart de type texte
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN enc_chart TEXT', table_record.table_name);
-- Crée le champ scale de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN scale NUMERIC', table_record.table_name);
-- Crée le champ purpose de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN purpose NUMERIC', table_record.table_name);
IF NOT EXISTS (
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'polysenc' AND table_name = table_record.table_name AND column_name IN ('posacc','quapos')
) THEN
-- Crée le champ POSACC de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN POSACC NUMERIC(10,0)', table_record.table_name);
-- Crée le champ QUAPOS de type numérique
EXECUTE format('ALTER TABLE polysenc.%I ADD COLUMN QUAPOS INTEGER', table_record.table_name);
END IF;
RAISE NOTICE 'Champs enc_chart, scale et purpose créés dans la table %', table_record.table_name;
END IF;
-- Obtient la valeur de enc_chart à partir de la table DSID pour DSID_DSNM
SELECT DSID_DSNM INTO enc_chart_value FROM polysenc.polysdsid LIMIT 1;
-- Obtient la valeur de scale à partir de la table DSID pour DSPM_CSCL
SELECT DSPM_CSCL INTO scale_value FROM polysenc.polysdsid LIMIT 1;
-- Obtient la valeur de purpose à partir de la table DSID pour DSID_INTU
SELECT DSID_INTU INTO purpose_value FROM polysenc.polysdsid LIMIT 1;
-- Met à jour les enregistrements avec les valeurs trouvées dans la table DSID
EXECUTE format('UPDATE polysenc.%I SET enc_chart = $1 WHERE enc_chart IS NULL', table_record.table_name) USING enc_chart_value;
EXECUTE format('UPDATE polysenc.%I SET scale = $1 WHERE scale IS NULL', table_record.table_name) USING scale_value;
EXECUTE format('UPDATE polysenc.%I SET purpose = $1 WHERE purpose IS NULL', table_record.table_name) USING purpose_value;
END LOOP;
-- Efface l'enregistrement de la table DSID
DELETE FROM polysenc.polysdsid;
RETURN NULL;END;
$$
LANGUAGE plpgsql;CREATE OR REPLACE TRIGGER check_update
AFTER INSERT ON linesenc.linesdsid
FOR EACH ROW
EXECUTE PROCEDURE create_fields_and_update_values_linesenc();CREATE OR REPLACE TRIGGER check_update
AFTER INSERT ON polysenc.polysdsid
FOR EACH ROW
EXECUTE PROCEDURE create_fields_and_update_values_polysenc();CREATE OR REPLACE TRIGGER check_update
AFTER INSERT ON pointsenc.pointsdsid
FOR EACH ROW
EXECUTE PROCEDURE create_fields_and_update_values_pointsenc();Fluxo de trabalho para carregar arquivos S57
Comandos ogr2ogr para criar tabelas Postgis
Com todos esses elementos em mãos, aqui está o arquivo .bat com as linhas de comando do ogr2ogr para criar as tabelas nos esquemas de importação:
REM Vérifie qu’un répertoire a été fourni REM Récupère l’argument REM Itère sur tous les fichiers .000 dans le répertoire ) Você precisa alterar as informações de conexão do seu banco de dados PostgreSQL/POstgis: PostgreSQL PG: “dbname=‘postgis_34_sample’ host=‘localhost’ port=‘5434’ user=‘postgres’ password=‘xxxxxx’ Para executar essas linhas de comando, basta abrir a janela do shell do OSGeo4W: A janela Shell é aberta Digite a seguinte linha de comando: .\Path/ mass_load_s57_postgis.bat repertoire_enc Repertoire_enc é o diretório que contém os arquivos .000 para os cartões enc. Todos os arquivos .000 nesse diretório serão carregados nos esquemas de importação. O resultado será uma série de tabelas criadas em cada esquema de importação. Entretanto, algumas tabelas ficarão completamente vazias. Se o tipo de geometria for possível para uma classe de objeto, a tabela será criada, mas se não houver ocorrências no arquivo S57 que está sendo processado, a tabela não terá registros. A função clone_tables_with_prefix carrega o banco de dados ENC com as tabelas dos três esquemas de importação.
@echo off
setlocal enabledelayedexpansion
if «%~1″==»» (
echo Usage: %0 directory000
exit /b 1
)
set «directory=%~1»
for /r «%directory%» %%i in (*.000) do (
echo Traitement du fichier: %%iogr2ogr -skipfailures -append -update -s_srs EPSG:4326 -t_srs EPSG:4326 ^
-where "OGR_GEOMETRY='POINT' or OGR_GEOMETRY='MULTIPOINT'" ^
-oo RETURN_PRIMITIVES=ON -oo SPLIT_MULTIPOINT=ON -oo RETURN_LINKAGES=ON -oo LNAM_REFS=ON -oo ADD_SOUNDG_DEPTH=ON ^
-nlt MULTIPOINT -f PostgreSQL ^
PG:"dbname=postgis_34_sample host=localhost port=5432 user=postgres password=1touria+ active_schema=pointsenc" %%i
ogr2ogr -skipfailures -append -update -nln pointsDSID -f PostgreSQL PG:"dbname=postgis_34_sample host=localhost port=5432 user=postgres password=1touria+ active_schema=pointsenc" %%i DSID
ogr2ogr -skipfailures -append -update -s_srs EPSG:4326 -t_srs EPSG:4326 ^
-where "OGR_GEOMETRY='LINESTRING' or OGR_GEOMETRY='MULTILINESTRING'" ^
-oo RETURN_PRIMITIVES=ON -oo SPLIT_MULTIPOINT=ON -oo RETURN_LINKAGES=ON -oo LNAM_REFS=ON ^
-f PostgreSQL PG:"dbname=postgis_34_sample host=localhost port=5432 user=postgres password=1touria+ active_schema=linesenc" %%i
ogr2ogr -skipfailures -append -update -nln linesDSID -f PostgreSQL ^
PG:"dbname=postgis_34_sample host=localhost port=5432 user=postgres password=1touria+ active_schema=linesenc" %%i DSID
ogr2ogr -skipfailures -append -update -s_srs EPSG:4326 -t_srs EPSG:4326 ^
-where "OGR_GEOMETRY='POLYGON' or OGR_GEOMETRY='MULTIPOLYGON'" ^
-oo RETURN_PRIMITIVES=ON -oo SPLIT_MULTIPOINT=ON -oo RETURN_LINKAGES=ON -oo LNAM_REFS=ON ^
-f PostgreSQL PG:"dbname=postgis_34_sample host=localhost port=5432 user=postgres password=1touria+ active_schema=polysenc" %%i
ogr2ogr -skipfailures -append -update -nln polysDSID -f PostgreSQL ^
PG:"dbname=postgis_34_sample host=localhost port=5432 user=postgres password=1touria+ active_schema=polysenc" %%i DSIDecho Traitement terminé.
pause
Carregamento do banco de dados a partir dos esquemas de importação
Antes de usar essa função, é necessário instalar a função delete_all_records_in_schema:
Copie a consulta em uma janela SQL do pgAdmin e execute-a.
-- FUNCTION: public.delete_all_records_in_schema(text)-- DROP FUNCTION IF EXISTS public.delete_all_records_in_schema(text);CREATE OR REPLACE FUNCTION public.delete_all_records_in_schema(
schema_name text)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
table_record RECORD;
BEGIN
-- Récupérer toutes les tables du schéma spécifié
FOR table_record IN
SELECT table_name
FROM information_schema.tables
WHERE table_schema = schema_name AND table_type = 'BASE TABLE'
LOOP
-- Construction de la requête DELETE pour chaque table
EXECUTE format('DELETE FROM %I.%I', schema_name, table_record.table_name);
END LOOP;
END;
$BODY$;ALTER FUNCTION public.delete_all_records_in_schema(text)
OWNER TO postgres;O banco de dados ENC é atualizado chamando o comando
SELECT clone_tables_with_prefix()
Não se esqueça de substituir o nome do esquema (enc2) pelo nome de seu esquema enc.
CREATE OR REPLACE FUNCTION clone_tables_with_prefix()
RETURNS void AS
$$
DECLARE
table_nom text;
BEGIN
-- Clonage des tables dans le schéma ENC et mise à jour des tables existantes-- Boucle sur les tables du schéma pointsENC
FOR table_nom IN (SELECT table_name as table_nom FROM information_schema.tables WHERE table_schema = 'pointsenc' AND table_name NOT IN ('pointsdsid','isolatednode','connectednode'))
LOOP
-- Construction de la requête dynamique pour créer ou mettre à jour la table dans ENC
EXECUTE format('CREATE TABLE IF NOT EXISTS enc2.pt_%I AS SELECT * FROM pointsenc.%I', table_nom, table_nom);
EXECUTE format('INSERT INTO enc2.pt_%I SELECT * FROM pointsenc.%I ON CONFLICT DO NOTHING', table_nom, table_nom);
EXECUTE format('UPDATE enc2.pt_%I SET posacc = isolatednode.posacc, quapos = isolatednode.quapos FROM pointsenc.IsolatedNode isolatednode WHERE enc2.pt_%I.NAME_RCID[1] = isolatednode.RCID AND enc2.pt_%I.enc_chart = isolatednode.enc_chart;', table_nom, table_nom,table_nom);
END LOOP;
-- Boucle sur les tables du schéma LinesENC
FOR table_nom IN (SELECT table_name as table_nom FROM information_schema.tables WHERE table_schema = 'linesenc' AND table_name NOT IN ('linesdsid','edge'))
LOOP
-- Construction de la requête dynamique pour créer ou mettre à jour la table dans ENC
EXECUTE format('CREATE TABLE IF NOT EXISTS enc2.li_%I AS SELECT * FROM linesenc.%I', table_nom, table_nom);
EXECUTE format('INSERT INTO enc2.li_%I SELECT * FROM linesenc.%I ON CONFLICT DO NOTHING', table_nom, table_nom);
EXECUTE format('UPDATE enc2.li_%I SET posacc = edge.posacc, quapos = edge.quapos FROM linesenc.edge edge WHERE enc2.li_%I.NAME_RCID[1] = edge.RCID AND enc2.li_%I.enc_chart = edge.enc_chart;', table_nom, table_nom,table_nom);
END LOOP;
-- Boucle sur les tables du schéma PolysENC
FOR table_nom IN (SELECT table_name as table_nom FROM information_schema.tables WHERE table_schema = 'polysenc' AND table_name NOT IN ( 'polysdsid','m_qual','m_srel'))
LOOP
-- Construction de la requête dynamique pour créer ou mettre à jour la table dans ENC
EXECUTE format('CREATE TABLE IF NOT EXISTS enc2.pl_%I AS SELECT * FROM polysenc.%I', table_nom, table_nom);
EXECUTE format('INSERT INTO enc2.pl_%I SELECT * FROM polysenc.%I ON CONFLICT DO NOTHING', table_nom, table_nom);
EXECUTE format('UPDATE enc2.pl_%I SET posacc = edge.posacc, quapos = edge.quapos FROM linesenc.edge edge WHERE enc2.pl_%I.NAME_RCID[1] = edge.RCID AND enc2.pl_%I.enc_chart = edge.enc_chart;', table_nom, table_nom,table_nom);
END LOOP;
EXECUTE (SELECT delete_all_records_in_schema('pointsenc'));
EXECUTE (SELECT delete_all_records_in_schema('linesenc'));
EXECUTE (SELECT delete_all_records_in_schema('polysenc'));EXECUTE (SELECT update_sbdare());
END;
$$ LANGUAGE plpgsql;Tratamento especial da tabela pt_SBDARE
Após a primeira execução do script anterior, se os arquivos carregados tiverem valores para as naturezas de fundo, você deverá ter uma tabela pt_sbdare no esquema ENC. Essa é a única tabela que requer processamento especial para a simbologia padrão que fornecemos. De fato, o processamento dos valores dos dois atributos necessários para criar o rótulo da natureza de fundo (NATSUR e NATQUA) é muito complexo devido à natureza dos atributos (StringLists) e às inúmeras combinações possíveis.
A solução aplicada aqui é a seguinte:
- Uma tabela natsurf lista as possíveis combinações de valores das tuplas natqua e natsur com o rótulo correspondente.
- Uma função update_sbdare processa as duas listas de strings para compor as tuplas de valores e concatena os rótulos da tabela natsurf adequadamente.
- A função atualiza os registros na tabela pt_sbdare cujo valor “label” é zero.
Quando os esquemas de importação são carregados pela primeira vez, a tabela pt_sbdare não existe. Uma vez criada por uma importação, você pode definir um acionador que executará a função automaticamente em seguida.
Portanto, você precisa :
- Importar a tabela natsurf para o esquema ENC
- Criar a função update_sbdare
- Executar a função manualmente pela primeira vez
.
Importando a tabela natsurf
Carregue a consulta a seguir em uma janela SQL do pgAdmin e execute-a. Presume-se que você tenha criado um esquema “enc” para o seu banco de dados. Se esse não for o caso, edite a tabela, substituindo “enc.natsurf” por “your_schema.natsurf”.
Carregue a consulta a seguir em uma janela SQL do pgAdmin e execute-a. Presume-se que você tenha criado um esquema “enc” para o seu banco de dados. Se esse não for o caso, edite a tabela substituindo “enc.” por “your_schema”. Em uma janela SQL do pgAdmin, insira a consulta ALTER TABLE enc.pt_sbdare ADD COLUMN label VARCHAR(25); SELECT update_sbdare() O resultado será o atributo “label” na tabela pt_sbdare. Posteriormente, quando você executar clone_tables_with_prefix(), a atualização será executada automaticamente.SET standard_conforming_strings = ON;
DROP TABLE IF EXISTS enc.natsurf CASCADE;
BEGIN;
CREATE TABLE enc.natsurf();
ALTER TABLE enc.natsurf ADD COLUMN "ogc_fid" SERIAL CONSTRAINT "natsurf_pk" PRIMARY KEY;
ALTER TABLE enc.natsurf ADD COLUMN "fid" NUMERIC(20,0);
ALTER TABLE enc.natsurf ADD COLUMN "natsurt" VARCHAR;
ALTER TABLE enc.natsurf ADD COLUMN "natquat" VARCHAR;
ALTER TABLE enc.natsurf ADD COLUMN "etiq" VARCHAR;
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (1, '1', '0', 'M');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (2, '1', '1', 'fM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (3, '1', '2', 'mM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (4, '1', '3', 'cM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (5, '1', '4', 'bkM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (6, '1', '5', 'syM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (7, '1', '6', 'soM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (8, '1', '7', 'sfM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (9, '1', '8', 'vM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (10, '1', '9', 'caM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (11, '1', '10', 'hM');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (12, '2', '0', 'Cy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (13, '2', '1', 'fCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (14, '2', '2', 'mCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (15, '2', '3', 'cCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (16, '2', '4', 'bkCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (17, '2', '5', 'syCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (18, '2', '6', 'soCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (19, '2', '7', 'sfCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (20, '2', '8', 'vCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (21, '2', '9', 'caCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (22, '2', '10', 'hCy');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (23, '3', '0', 'Si');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (24, '3', '1', 'fSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (25, '3', '2', 'mSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (26, '3', '3', 'cSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (27, '3', '4', 'bkSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (28, '3', '5', 'sySi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (29, '3', '6', 'soSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (30, '3', '7', 'sfSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (31, '3', '8', 'vSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (32, '3', '9', 'caSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (33, '3', '10', 'hSi');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (34, '4', '0', 'S');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (35, '4', '1', 'fS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (36, '4', '2', 'mS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (37, '4', '3', 'cS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (38, '4', '4', 'bkS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (39, '4', '5', 'syS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (40, '4', '6', 'soS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (41, '4', '7', 'sfS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (42, '4', '8', 'vS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (43, '4', '9', 'caS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (44, '4', '10', 'hS');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (45, '5', '0', 'St');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (46, '5', '1', 'fSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (47, '5', '2', 'mSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (48, '5', '3', 'cSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (49, '5', '4', 'bkSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (50, '5', '5', 'sySt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (51, '5', '6', 'soSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (52, '5', '7', 'sfSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (53, '5', '8', 'vSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (54, '5', '9', 'caSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (55, '5', '10', 'hSt');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (56, '6', '0', 'G');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (57, '6', '1', 'fG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (58, '6', '2', 'mG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (59, '6', '3', 'cG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (60, '6', '4', 'bkG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (61, '6', '5', 'syG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (62, '6', '6', 'soG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (63, '6', '7', 'sfG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (64, '6', '8', 'vG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (65, '6', '9', 'caG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (66, '6', '10', 'hG');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (67, '7', '0', 'P');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (68, '7', '1', 'fP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (69, '7', '2', 'mP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (70, '7', '3', 'cP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (71, '7', '4', 'bkP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (72, '7', '5', 'syP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (73, '7', '6', 'soP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (74, '7', '7', 'sfP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (75, '7', '8', 'vP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (76, '7', '9', 'caP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (77, '7', '10', 'hP');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (78, '8', '0', 'Cb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (79, '8', '1', 'fCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (80, '8', '2', 'mCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (81, '8', '3', 'cCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (82, '8', '4', 'bkCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (83, '8', '5', 'syCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (84, '8', '6', 'soCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (85, '8', '7', 'sfCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (86, '8', '8', 'vCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (87, '8', '9', 'caCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (88, '8', '10', 'hCb');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (89, '9', '0', 'R');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (90, '9', '1', 'fR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (91, '9', '2', 'mR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (92, '9', '3', 'cR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (93, '9', '4', 'bkR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (94, '9', '5', 'syR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (95, '9', '6', 'soR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (96, '9', '7', 'sfR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (97, '9', '8', 'vR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (98, '9', '9', 'caR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (99, '9', '10', 'hR');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (100, '11', '0', 'L');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (101, '11', '1', 'fL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (102, '11', '2', 'mL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (103, '11', '3', 'cL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (104, '11', '4', 'bkL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (105, '11', '5', 'syL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (106, '11', '6', 'soL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (107, '11', '7', 'sfL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (108, '11', '8', 'vL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (109, '11', '9', 'caL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (110, '11', '10', 'hL');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (111, '14', '0', 'Co');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (112, '14', '1', 'fCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (113, '14', '2', 'mCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (114, '14', '3', 'cCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (115, '14', '4', 'bkCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (116, '14', '5', 'syCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (117, '14', '6', 'soCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (118, '14', '7', 'sfCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (119, '14', '8', 'vCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (120, '14', '9', 'caCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (121, '14', '10', 'hCo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (122, '17', '0', 'Sh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (123, '17', '1', 'fSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (124, '17', '2', 'mSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (125, '17', '3', 'cSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (126, '17', '4', 'bkSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (127, '17', '5', 'sySh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (128, '17', '6', 'soSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (129, '17', '7', 'sfSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (130, '17', '8', 'vSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (131, '17', '9', 'caSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (132, '17', '10', 'hSh');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (133, '18', '0', 'Bo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (134, '18', '1', 'fBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (135, '18', '2', 'mBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (136, '18', '3', 'cBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (137, '18', '4', 'bkBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (138, '18', '5', 'syBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (139, '18', '6', 'soBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (140, '18', '7', 'sfBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (141, '18', '8', 'vBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (142, '18', '9', 'caBo');
INSERT INTO enc.natsurf ("fid", "natsurt", "natquat", "etiq") VALUES (143, '18', '10', 'hBo');
COMMIT;Criação da função update_sbdare
CREATE OR REPLACE FUNCTION update_sbdare()
RETURNS VOID AS
$$
DECLARE
rec_row RECORD;
natsurt VARCHAR[4];
natquat VARCHAR[4];
S VARCHAR[3];
flag INT := 0;
etiq VARCHAR(25);
etiquet VARCHAR(25);
BEGIN
-- Vérifier si le champ 'Label' existe déjà
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema = 'enc2' AND table_name = 'pt_sbdare' AND column_name = 'label'
) THEN-- Parcourir les lignes de la table enc.pt_sbdare
FOR rec_row IN SELECT * FROM enc2.pt_sbdare WHERE label IS NULL LOOP
-- Réinitialiser les variables à chaque itération de la boucle
natsurt := ARRAY['','','',''];
S := ARRAY[',',',',','];
flag :=0;
-- Extraire les parties de natsur
FOR i IN 1..4 LOOP
--sortie anticipée
IF i=4 AND flag=1 THEN
EXIT;
END IF;
-- Vérifier si rec_row.natsur[i] est NULL
IF rec_row.natsur[i] IS NULL THEN
IF flag=0 THEN
natsurt[i] := '0';
ELSE
natsurt[i+1] := '0';
END IF;
-- Vérifier si rec_row.natsur[i] contient un '/'
ELSIF strpos(rec_row.natsur[i], '/') = 0 THEN
IF flag=0 THEN
natsurt[i] := rec_row.natsur[i];
IF rec_row.natqua[i] IS NOT NULL THEN
natquat[i] := rec_row.natqua[i];
ELSE
natquat[i] := '0';
END IF;
ELSE
natsurt[i+1] := rec_row.natsur[i];
IF rec_row.natqua[i] IS NOT NULL THEN
natquat[i+1] := rec_row.natqua[i];
ELSE
natquat[i] := '0';
END IF;
END IF;
ELSE
-- Extraire les parties avant et après le '/'
IF i < 5 THEN
natsurt[i] := split_part(rec_row.natsur[i], '/', 1);
natsurt[i+1] := split_part(rec_row.natsur[i], '/', 2);
IF rec_row.natqua[i] IS NOT NULL THEN
natquat[i] := rec_row.natqua[i];
ELSE
natquat[i] := '0';
END IF;
S[i] := '/';
flag :=1; -- nous avons trouvé une valeur avec un '/'
ELSE
natsurt[i] := split_part(rec_row.natsur[i], '/', 1);
END IF;
END IF;
END LOOP;
etiquet :='';
etiq :='';
FOR i IN 1..4 LOOP
IF natsurt[i] <> '0' THEN
-- Exécutez la requête SQL pour récupérer le label en fonction de natsurt[i] et natquat[i]
EXECUTE 'SELECT etiq FROM enc2.natsurf WHERE NATSURT = $1 AND NATQUAT = $2' INTO etiq USING natsurt[i], natquat[i];
IF i =1 THEN
etiquet := etiq ;
ELSE
etiquet := etiquet || S[i-1] || etiq;
END IF;
END IF;
END LOOP;
-- Mettre à jour la ligne avec les valeurs extraites
UPDATE enc2.pt_sbdare
SET
label = etiquet
WHERE pt_sbdare.ogc_fid = rec_row.ogc_fid;
END LOOP;END IF;
END;
$$
LANGUAGE plpgsql;
Execução manual da função para a primeira atualização
