segunda-feira, 18 de setembro de 2017

EXCLUINDO TODAS AS TABELAS DE UM SCHEMA - POSTGRESQL/POSTGIS

Para excluir todas as tabelas de um schema, você pode excluir e recriar ele:

DROP SCHEMA nome_do_esquema CASCADE;
CREATE SCHEMA nome_do_esquema;

Porém, se quiser excluir somente as tabelas, pode ser escrito um comando que gera a sintaxe completa para executar a instrução de exclusão de todas as tabelas de um determinado schema, como o comando abaixo:

SELECT 'DROP TABLE nome_do_esquema.' || tablename || ';' 
FROM pg_tables WHERE schemaname = 'nome_do_esquema';

Depois de gerar o comando, basta copiar, colar (se necessário, excluir as aspas) e executá-lo.
Se houver dependências e for necessário usar o CASCADE, use:

SELECT 'DROP TABLE nome_do_esquema.' || tablename || ' CASCADE;' 
FROM pg_tables WHERE schemaname = 'nome_do_esquema';

segunda-feira, 4 de setembro de 2017

ATUALIZAÇÃO COM RELAÇÃO ESPACIAL NO POSTGIS

Muitas vezes é necessário fazer uma atualização de dados por relação espacial no PostGIS. Isso ocorre principalmente quando temos que transformar uma relação espacial em uma relação por chave estrangeira (que é mais rápida em retornar resultados) ou outras, quando há relação espacial entre determinadas geometrias. Quando essas geometrias se tocam, usamos a função ST_Intersects para fazer a atualização (para mais funções de relações espaciais clique aqui).

Por exemplo, uma situação onde é necessário atualizar o número de quadra em lotes:

UPDATE lotes SET id_quadra = id FROM quadras WHERE ST_Intersects(lotes.geom,quadras.geom);

Essa instrução pode ser melhorada, usando o centroide do lote:

UPDATE lotes SET id_quadra = id FROM quadras WHERE ST_Intersects(ST_Centroid(lotes.geom),quadras.geom);

Ou, em outro exemplo, para atualizar o nome do bairro nos lotes:

UPDATE lotes SET nome_bairro = nome FROM bairros WHERE 
ST_Intersects(ST_Centroid(lotes.geom),bairros.geom);

segunda-feira, 21 de agosto de 2017

EDITANDO UM ARQUIVO DE PROJETO DO QGIS

Como o arquivo de projeto do QGIS é estruturado em formato XML, é possível alterar algumas de suas características editando o conteúdo de suas tags em um editor de texto simples (como o bloco de notas). Um exemplo de situação onde isso pode ser muito útil é a alteração de dados de uma conexão de bancos de dados - Na tag <datasource> temos o dbname (nome do banco de dados), o host (endereço IP) e a porta, entre outros. Basta substituir o valor antigo pelo desejado. 

Por exemplo, para alterar a conexão da "tabela", do "banco_antigo" em 192.168.0.100:5432 para o "banco_novo" em 192.168.0.101:5433, basta alterar a linha:
<datasource>dbname='banco_antigo' host=192.168.0.100 port=5432 sslmode=disable key='id' table="public"."tabela" sql=</datasource>
Por:
<datasource>dbname='banco_novo' host=192.168.0.101 port=5433 sslmode=disable key='id' table="public"."tabela" sql=</datasource>

A mesma estrutura vale para os arquivos de estilo de camadas. Um exemplo útil:
Como o QGIS não permite copiar / colar estilos entre tipos diferentes de elementos (ponto, linha ou polígono), essa edição também pode ser feita (no arquivo de estilo):
- Para copiar / colar nomes de campos, de um arquivo de estilo para outro, copiar / colar a tag <aliases>
- Para copiar / colar formatos de campos, de um arquivo de estilo para outro, copiar / colar a tag <edittypes>

sexta-feira, 28 de julho de 2017

DOCUMENTANDO UM BANCO DE DADOS POSTGRESQL/POSTGIS

Tendo as tabelas e campos de um banco de dados PostgreSQL comentados, fica fácil gerar a documentação desses campos e tabelas. Para isso pode ser usado:

  • Para documentar as tabelas (o SQL a seguir traz o esquema, a tabela, o dono, o comentário, o número de registros e o tablespace):

SELECT nspname AS esquema, c.relname AS tabela,
pg_catalog.pg_get_userbyid(c.relowner) AS dono,
pg_catalog.obj_description(c.oid, 'pg_class') AS comentario,
reltuples::integer as registros,
(SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE pt.oid=c.reltablespace) AS tablespace
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
ORDER BY esquema, tabela;

  • Para documentar os campos (o SQL a seguir traz a tabela, a coluna, o tipo e o comentário):

SELECT t.relname AS tabela,
a.attname AS coluna,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS tipo,
(SELECT col_description(a.attrelid,a.attnum)) AS comentario
FROM pg_catalog.pg_attribute a
INNER JOIN pg_stat_user_tables t ON a.attrelid = t.relid
WHERE a.attnum > 0 AND NOT a.attisdropped
ORDER BY tabela, coluna;

Outros exemplos podem ser encontrados em https://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Metadados

terça-feira, 2 de maio de 2017

COMENTÁRIOS NO POSTGRESQL

Comentar adequadamente um banco de dados é extremamente importante. Os comentários auxiliam principalmente na compreensão e documentação do conteúdo do banco de dados. No PostgreSQL usamos a seguinte sintaxe para comentários:

Comentário em uma tabela:
COMMENT ON TABLE nome_da_tabela IS 'comentário';

Para sobrepor um comentário basta executar a mesma instrução com o novo comentário. Se quiser  excluir um comentário pode usar:
COMMENT ON TABLE nome_da_tabela IS NULL;

Outros exemplos:
COMMENT ON COLUMN nome_da_tabela.nome_da_coluna IS 'Comentário';
COMMENT ON DATABASE nome_do_banco_de_dados IS 'Comentário';
COMMENT ON FUNCTION nome_da_função() IS 'Comentário';
COMMENT ON INDEX nome_do_índice IS 'Comentário';
COMMENT ON ROLE nome_do_papel IS 'Comentário';
COMMENT ON RULE nome_da_regra ON nome_da_tabela IS 'Comentário';
COMMENT ON SCHEMA nome_do_esquema IS 'Comentário';
COMMENT ON TRIGGER nome_da_trigger ON nome_da_tabela IS 'Comentário';
COMMENT ON TYPE nome_do_tipo IS 'Comentário';
COMMENT ON VIEW nome_da_visão IS 'Comentário';

sexta-feira, 21 de abril de 2017

ATRIBUIÇÃO DE ATIVIDADES A USUÁRIOS ESPECÍFICOS NO POSTGRESQL / POSTGIS

Uma atividade muito comum em Sistemas de Informação Geográfica é a atribuição de atividades específicas para usuários. Para definir níveis de permissões/operação para cada usuário dentro de uma mesma tabela no PostgreSQL / PostGIS devemos usar o conceito de POLICY (https://www.postgresql.org/docs/9.5/static/sql-createpolicy.html).

Vejamos o exemplo a seguir, onde é desejável que dois usuários de edição recebam atribuições específicas sobre a edição em uma tabela de "Lotes":

1) Criação dos usuários:
CREATE USER edicao1 LOGIN PASSWORD 'ed1';
CREATE USER edicao2 LOGIN PASSWORD 'ed2';

2) Criação do campo que vai receber o nome do usuário autorizado para edição:
ALTER TABLE lote ADD atribuicao VARCHAR(20);

3) Atribuição das quatro operações básicas aos usuários dentro da tabela de lotes:
GRANT SELECT,UPDATE,INSERT,DELETE ON lote TO edicao1;
GRANT SELECT,UPDATE,INSERT,DELETE ON lote TO edicao2;

4) Criação a regra de uso por usuário:
CREATE POLICY policy_atribuicao ON lote FOR ALL
TO PUBLIC USING (atribuicao = current_user);
ALTER TABLE lote ENABLE ROW LEVEL SECURITY;

Já podemos fazer a atribuição definindo o nome do usuário no campo atribuicao da tabela lote. Exemplo de atribuição:
UPDATE lote SET atribuicao = 'edicao1' WHERE id >= 1 AND id <= 100;
UPDATE lote SET atribuicao = 'edicao2' WHERE id >= 101 AND id <= 200;

Pronto. Nessa situação, quando um usuário (desenho1 ou desenho2) acessar o banco (inclusive por um software de SIG, como o QGIS, por exemplo), ele só terá acesso e possibilidade de edição nos registros que lhe estão atribuídos.

segunda-feira, 4 de julho de 2016

SELECIONAR E REINICIAR SEQUÊNCIAS (SEQUENCES) NO POSTGRESQL

Para selecionar as sequências do PostgreSQL basta usar o comando

SELECT * FROM information_schema.sequences;

Dentro dele podemos incluir algumas opções, de acordo com o resultado que queremos. Por exemplo:


  • Seleciona somente as sequências do schema public:
    • SELECT * FROM information_schema.sequences WHERE sequence_schema = 'public';
  • Seleciona apenas os nomes das sequências:
    • SELECT sequence_name FROM information_schema.sequences;

Para reiniciar uma sequência use o seguinte comando:

ALTER SEQUENCE nome_da_sequencia RESTART;

Finalmente, para atribuir os novos valores aos campos já existentes, use:

UPDATE nome_da_tabela SET coluna = nextval('nome_da_sequencia');