PostGIS atualizou e a base de dados quebrou?

A mensagem de erro é a seguinte:
An error has occured: ERROR. could not access file “$libdir/postgis-2.1”: No such file or directolry.

A solução é fácil. A nova versão de PostGIS está numa nova pasta e temos de indicar isso a PostgreSQL. Depois, fazer o ALTER EXTENSION em cada uma das base de dados, para que atualize PostGIS.

No Ubuntu e para a versão de PostgreSQL 9.3, fica assim:

SELECT de um determinado ano e dia da semana

Por exemplo, todas as terças-feiras de 2016:

Notas:
generate_Series() – é uma função para gerar uma lista de valores, neste caso de 0 a 365, usados para somar com a data do inicio do ano (2016-01-01).

extract() – tal como o nome indica extrai sub valores de um timestamp ou data. Foi aqui usado para extrair o dia da semana (dow).

dowday of the week. Os valores de dow vão de 0 a 6. Sendo 0 domingo e 6 sábado. Portanto 2 corresponde a terça-feira.

Como alterar a senha desconhecida de administrador de PostgreSQL

Este é apenas mais um exemplo de que uma password não é suficiente para proteger o conteúdo de um computador. O acesso físico ao computador é essencial.

Então suponhamos que se perdeu a senha de administrador de PostgreSQL, ou seja, do utilizador postgres. Primeiro há que ter em consideração que o utilizador postgres existe tanto na base de dados como no sistema operativo. Então vai ser necessário alterar a senha nos dois sítios.

Para alterar a senha no sistema operativo é necessário acesso root ao sistema. Se também perdeu a senha de root, ver aqui primeiro. Se não prosseguir:

Neste ponto a senha de postgres no sistema operativo está atualizada. Resta alterar a senha na base de dados.

Feito!

Função para intersectar geometrias em PostgreSQL + PostGIS

Escrevi esta função para dar resposta a uma operação muito comum em SIG. Intersecção entre geometrias. No entanto, por vezes, as geometrias não se intersectam e nesses casos o que nos gostaria de saber é a que distancia está a geometria mais próxima. Ou mesmo, todas as geometrias que estão dentro uma determinada distancia. Outro fator a ter em conta é a velocidade da consulta. Para acelerar o calculo usa-se o típico a.geom && b.geom e assim a consulta faz uso do bounding box da geometria subjacente. Mas no caso de uma geometria estar fora de todos os bounding box vamos ter como resposta um valor NULL, e isso não é muito informativo. Esta função tentar dar resposta a todas estas questões.

Aspetos a ter em conta.
– Ambas tabelas tem de ter uma PRIMARY KEY.
– Não importa o tipo de geometria (POINT, LINE, POLYGON, …).
– Não importa se as geometrias tem diferentes Sistemas de Coordenadas, a função converte tudo a EPSG:4326 (WGS84) para realizar o calculo.
– Vão ser criados duas colunas, uma para os id‘s da(s) geometria(s) mais próxima(s) e outra para a(s) distancia(s) em metros.
– Se a função se executa mais de uma vez, elimina as colunas anteriores antes de as criar novamente.
– O resultado da distancia é dado em metros.
– O valor de tolerance deve ser dado em metros.
– Se o valor de tolerance é igual a 0, apenas devolve o id de uma geometria, a mais próxima e a sua distancia (0 ou mais).
– Se o valor de tolerance é maior do que 0, devolve uma array com todos os id das geometrias a menor ou igual distancia do valor de tolerance e uma array com as correspondentes distancias. Pela mesma ordem.

Como usar esta função.

SELECT fun_closest_geom(sch_from, table_from, sch_to, table_to, tolerance);

– Caso 1. Queremos saber que geometria da tabela public.tabela2 esta mais próxima das geometrias da tabela public.tabela1. Os resultados escritos na public.tabela1.
SELECT fun_closest_geom(‘public’, ‘tabela1’, ‘public’, ‘tabela2’, 0);

– Caso 2. Queremos saber que geometrias da tabela public.tabela2 estão a <= 1 Km das geometrias da tabela public.tabela1. Os resultados escritos na public.tabela1.
SELECT fun_closest_geom(‘public’, ‘tabela1’, ‘public’, ‘tabela2’, 1000);

Mas antes temos de criar a função, para isso basta executar o seguinte código.

Disparador (trigger) para actualizar automaticamente a área e o perímetro de geometrias em PostgreSQL

Nesta entrada vou explicar como criar um disparador para actualizar automaticamente a área e perímetro das geometrias de uma tabela em PostgreSQL. Para tal, vão-se utilizar as funções ST_Area() e ST_Perimeter() de PostGIS.

Nota: Onde está esquema deve ir o nome do esquema, onde está tabela o nome da tabela e onde está geom o nome do campo com a geometria.

As seguintes linhas são para criar os campos area e perimetro e preencher os campos. Utilizar uma das opções: UTM ou Geográficas, dependendo do sistema de coordenadas do tema.

Decidi utilizar o tipo bigint porque considero que os valores decimais não aportam informação útil para pequenas escalas e iriam ocupar mais espaço na tabela. No entanto, para escalas maiores e se interessa a exactidão, basta com mudar bigint por float.

Agora criamos a função que preenche automáticamente os campos area e perimetro cada vez que uma geometria é inserida ou modificada. Se anteriormente alteraste bigint por float, elimina ‘::bigint’ nas linhas 5 e 6. O resultado por defeito é do tipo float e já não é necessário fazer um cast.

O exemplo dado é para temas com sistemas de coordenadas em metros, como UTM. Se o tema está em geográficas e também se pretende o resultado em metros, deve ir assim:

Eliminamos o disparador no caso de que já exista um com o mesmo nome. Para passar a criar o disparador e indicar quando se deve executar e sobre que tabela.

Agora os valores de área e perímetro da tabela estarão sempre de acordo com a geometria.

Instalação de PostgreSQL + PostGIS em Ubuntu 10.4

Partindo do principio que temos adicionado às nossas fontes de software o repositório de UbuntuGIS (ver entrada anterior), procedemos à instalação de PostgreSQL 8.4, PostGIS 1.5 e pgAdmin III.

Mudamos a senha do utilizador postgres no gestor de base de dados. Este utilizador tem todos os privilégios para administrar a base de dados.

Mudamos novamente a senha do utilizador postgres no sistema operativo, para que sejam iguais.

Criamos outro utilizador, com o mesmo nome que usamos no sistema operativo, no meu caso eloi, mudar pelo vosso utilizador. Este utilizador será para um uso corrente da base de dados e assim evitar o uso do utilizador postgres (superuser) para as tarefas comuns.

Executamos o script adminpack.sql para adicionar novas funcionalidades a pgAdmin III.

Agora vamos criar uma base de dados espacial como modelo, com o nome de postgis, para a partir de esta poder criar outras bases de dados espaciais mais facilmente. Vamos adicionar-lhe a linguagem de programação plpgsql e executar dois scripts: postgis.sql que vai criar todas as funções de PostGIS e spatial_ref_sys.sql que criará a tabela spatial_ref_sys no esquema public com todos os Sistemas de Coordenados suportados.

Finalmente criamos a base de dados que vamos dar uso, a partir da base de dados modelo (postgis), que criamos no passo anterior. Com a opção -U definimos o dono e com -T (de template) definimos a base de dados modelo seguido do nome da nova base de dados, por exemplo geodatabase.

Mover um tema de PostGIS de um schema para outro

Vamos supor que temos o tema (tabela com geometria) wd_estaciones_meteo_wmo no esquema sch_sig e queremos mover-lo para o esquema sch_ambiente.

Editado em 2012-03-16

É bem mais simples do que está descrito anteriormente, basta executar a seguinte sentencia:

Depois só se a versão de PostGIS é inferior à 2.0, temos reflectir esta mudança de esquema na tabela geometry_columns.

A partir da versao 2.0 de PostGIS geometry_columns deixa de ser uma tabela para ser uma vista e actualiza-se automaticamente.

Aceder a dados de PostgreSQL desde R

Numa anterior entrada vimos como criar um conector ODBC para PostgreSQL em Ubuntu. Nesta entrada veremos como aceder aos dados de PostgreSQL desde R-project. Abrimos R e o primeiro passo será instalar o pacote RODBC.

Criar um conector ODBC para PostgreSQL em Ubuntu

Se temos os nossos dados armazenados numa base de dados PostgreSQL em Ubuntu é bem provável que surja a necessidade de ligar-nos à base de dados a partir de outras aplicações, como seja, R, OpenOffice… A solução que encontrei é simples, passo a explicar.

Instalamos unixODBC

No tabulador “System DSN” pressionamos no botão “Add…“.unixodbc_01

Seleccionamos o driver, no meu caso escolhi “PostgreSQL Unicode” e depois em “OK“.

unixodbc_02

E preenchemos os dados que nos pedem…

unixodbc_03

Já temos uma ligação à nossa base de dados PostgreSQl!

Importar dados meteorologicos da web para PostgreSQL com Python

Supondo que temos a seguinte tabela em PostgreSQL

e aproveitando a disponibilidade de dados meteorológicos dos aeroportos pela página de Weather Underground e com recurso a um pequeno script de Pytthon podemos ter uma base de dados com os últimos dados meteorologicos dos aeroportos que desejamos, neste caso utilizarei apenas os da Peninsula Ibérica.