PostgreSQL most useful extensions

PostgreSQL most useful extensions | Railsware Blog.

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.

ALTER TABLE esquema.tabela ADD COLUMN area bigint;
ALTER TABLE esquema.tabela ADD COLUMN perimetro bigint;
-- Coordenadas UTM
UPDATE esquema.tabela SET area = ST_area(geom)::bigint;
UPDATE esquema.tabela SET perimetro = ST_perimeter(geom)::bigint;
-- Coordenadas Geográficas
UPDATE esquema.tabela SET area = ST_Area(Geography(geom))::bigint;
UPDATE esquema.tabela SET perimetro = ST_Length(Geography(geom))::bigint;

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.

CREATE OR REPLACE FUNCTION esquema.fun_area_perimetro()
  RETURNS trigger AS
$BODY$
BEGIN
NEW.area = ST_Area(NEW.geom)::bigint;
NEW.perimetro = ST_Perimeter(NEW.geom)::bigint;
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

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:

CREATE OR REPLACE FUNCTION esquema.fun_area_perimetro()
  RETURNS trigger AS
$BODY$
BEGIN
NEW.area = ST_Area(Geography(NEW.geom))::bigint;
NEW.perimetro = ST_Length(Geography(NEW.geom))::bigint;
RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

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.

DROP TRIGGER IF EXISTS trg_tabela_area_perimetro ON esquema.tabela;
CREATE TRIGGER trg_tabela_area_perimetro BEFORE INSERT OR UPDATE
ON esquema.tabela FOR EACH ROW EXECUTE PROCEDURE esquema.fun_area_perimetro();

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.

sudo apt-get install postgresql postgresql-8.4 postgresql-client postgresql-client-8.4 postgresql-client-common postgresql-common  postgresql-contrib postgresql-contrib-8.4 postgresql-doc postgresql-doc-8.4 postgresql-8.4-postgis postgis pgadmin3 pgadmin3-data

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.

sudo su postgres -c psql template1
ALTER USER postgres WITH PASSWORD 'senha';
\q # para sair de psql

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

sudo passwd postgres # em seguida pedirá a senha

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.

createuser --createdb eloi

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

su postgres
psql -f /usr/share/postgresql/8.4/contrib/adminpack.sql

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.

su postgres
createdb -U postgres postgis
createlang -U postgres plpgsql postgis
psql -U postgres -d postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -U postgres -d postgis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='postgis';"

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.

createdb -U eloi -T postgis 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.

-- copiamos o tema para o esquema desejado
select * into sch_ambiente.wd_estaciones_meteo_wmo from sch_sig.wd_estaciones_meteo_wmo;
-- eliminamos a coluna gid, pois uma vez copiado passou a ser uma simples coluna integer
alter table sch_ambiente.wd_estaciones_meteo_wmo drop column gid;
-- adicionamos a coluna gid agora como serial e primary key
alter table sch_ambiente.wd_estaciones_meteo_wmo add column gid serial primary key;
-- mudamos o nome da coluna geom para geom2
alter table sch_ambiente.wd_estaciones_meteo_wmo rename column geom to geom2;
-- adicionamos o campo geom
select AddGeometryColumn('sch_ambiente','wd_estaciones_meteo_wmo','geom',4326,'POINT',2);
-- preenchemos a coluna geom
update sch_ambiente.wd_estaciones_meteo_wmo set geom = geom2;
-- eliminamos a coluna geom2
alter table sch_ambiente.wd_estaciones_meteo_wmo drop column geom2;
-- eliminamos a tabela de origem da tabela public.geometry_columns
select DropGeometryColumn('sch_sig','wd_estaciones_meteo_wmo','geom');
-- eliminamos a tabela de origem
drop table sch_sig.wd_estaciones_meteo_wmo;
-- visualizamos os primeiros 100 registos do tema no novo esquema
select * from sch_ambiente.wd_estaciones_meteo_wmo limit 100;

Editado em 2012-03-16

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

ALTER TABLE sch_origem.tabela SET SCHEMA sch_destino;

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

UPDATE geometry_columns SET f_table_schema = 'sch_destino' WHERE f_table_name = 'tabela'; 

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.

install.packages("RODBC", dependencies=TRUE)

# Em seguida carregar o pacote
library(RODBC)

# Criamos a ligação à base de dados, indicando o nome do conector
chan <- odbcConnect("nome do conector ODBC", case="postgresql", believeNRows=FALSE)

# Com este comando podemos listar todas as tabelas na base de dados
sqlTables(chan)

# Seleccionamos o conjunto de dados pretendido
dados <- sqlQuery(chan, "SELECT * FROM nome_da_tabela WHERE nome_campo = 'restrição';")

# E a partir de aqui já temos os dados carregados em R e podemos começar a trabalhar com eles
summary(dados)

# Quando terminarmos, fechamos a ligação
odbcClose(chan)

# Também podemos criar tabelas ou inserir dados, sempre que o utilizador tenha permissão para tal, da seguinte maneira
sqlQuery(chan, "CREATE TABLE nome_da_tabela(nome_campo tipo_campo);")
sqlQuery(chan, "INSERT INTO nome_da_tabela ('Ola mundo');")

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

sudo apt-get install unixodbc unixodbc-bin odbc-postgresql
# Instalamos o driver de PostgreSQL para ODBC
sudo odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template
# Abrimos ODBC
sudo ODBCConfig

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

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

E preenchemos os dados que nos pedem…

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

CREATE TABLE meteo_aeroportos(
unico character varying(15),estacao_cod character(4), data date,
t_max real, t_med real, t_min real,o_max real, o_med real, o_min real,
h_max real, h_med real, h_min real,pa_max_in real, pa_med_in real, pa_min_in real,
vi_max_km real, vi_med_km real, vi_min_km real,v_max_kh real, v_med_kh real, 
vr_max_kh real, p_tot real, neblosidade real, descricao character varying(50),
id serial primary key);

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.

#coding: latin-1
import os
import urllib2
from BeautifulSoup import BeautifulSoup
import datetime
import psycopg2

def meteo_aero(base_de_dados, utilizador, host, senha):
    conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" % (base_de_dados, utilizador, host, senha))
    cur = conn.cursor()
    print '\n    Aeroportos'
    aeroportos = ['BGC', 'BGZ', 'CHV', 'CVU', 'FAO', 'FLW', 'FNC', 'GRW', 'HOR', 'LIS', 'LPBJ', 'LPEV', 'OPO', 'PDL', 'PIX', 'PRM', 'PXO', 'SJZ', 'SMA', 'TER', 'VRL', 'AGP', 'ALC', 'BCN', 'BIO', 'BJZ', 'EAS', 'GRO', 'GRX', 'IBZ', 'LCG', 'LEGA', 'LEGT', 'LEI', 'LERT', 'LPA', 'MAD', 'MJV', 'ODB', 'OVD', 'OZP', 'PMI', 'PNA', 'REU', 'SCQ', 'SDR', 'SLM', 'SVQ', 'TFN', 'TFS', 'TOJ', 'VGO', 'VIT', 'VLC', 'VLL', 'XRY', 'ZAZ']
    print 'Descarregar e inserir...'
    for a in aeroportos:
        sql = "select extract(year from max(data)), extract(month from max(data)), extract(day from max(data)) from dados.meteo_aeroportos where estacao_cod = '%s';" % a
        cur.execute(sql)
        rows = cur.fetchall()
        for row in rows:
           ano_i = int((row[0]))
           mes_i = int((row[1]))
           dia_i = int((row[2]))
        url = 'http://www.wunderground.com/history/airport/%s/%s/%s/%s/CustomHistory.html?dayend=30&monthend=05&yearend=2009&req_city=NA&req_state=NA&req_statename=NA&format=1' % (a, ano_i, mes_i, dia_i)
        page = urllib2.urlopen(url)
        sopa = BeautifulSoup(page)
        sopa = list(sopa)
        n = len(sopa[2:-4])
        x = 1
        while x < n:
            if (x % 2 == 0):
                linha = str(sopa[x]) + ','+ a
                linha = str(linha).replace(',,', ',null,')
                linha = str(linha).replace(',,', ',null,')
                linha = str(linha).replace('\n', '')
                
                data    = linha.split(',')[0]
                t_max = round(((float(linha.split(',')[1]) -32) / 1.8), 2) if linha.split(',')[1]  'null' else linha.split(',')[1] # c = (f-32) / 1.8
                t_med = round(((float(linha.split(',')[2]) -32) / 1.8), 2) if linha.split(',')[2]  'null' else linha.split(',')[2] # c = (f-32) / 1.8
                t_min = round(((float(linha.split(',')[3]) -32) / 1.8), 2) if linha.split(',')[3]  'null' else linha.split(',')[3] # c = (f-32) / 1.8
                o_max = round(((float(linha.split(',')[4]) -32) / 1.8), 2) if linha.split(',')[4]  'null' else linha.split(',')[4] # c = (f-32) / 1.8
                o_med = round(((float(linha.split(',')[5]) -32) / 1.8), 2) if linha.split(',')[5]  'null' else linha.split(',')[5] # c = (f-32) / 1.8
                o_min = round(((float(linha.split(',')[6]) -32) / 1.8), 2) if linha.split(',')[6]  'null' else linha.split(',')[6] # c = (f-32) / 1.8
                h_max   = (linha.split(',')[7])
                h_med   = (linha.split(',')[8])
                h_min   = (linha.split(',')[9])
                pa_max_in = linha.split(',')[10]
                pa_med_in = linha.split(',')[11]
                pa_min_in = linha.split(',')[12]
                vi_max_km = round((float(linha.split(',')[13]) * 1.609344), 2) if linha.split(',')[13]  'null' else linha.split(',')[13] # Km = miles * 1.609344
                vi_med_km = round((float(linha.split(',')[14]) * 1.609344), 2) if linha.split(',')[14]  'null' else linha.split(',')[14] # Km = miles * 1.609344
                vi_min_km = round((float(linha.split(',')[15]) * 1.609344), 2) if linha.split(',')[15]  'null' else linha.split(',')[15] # Km = miles * 1.609344
                v_max_kh = round((float(linha.split(',')[16]) * 1.609344), 2) if linha.split(',')[16]  'null' else linha.split(',')[16] # Km = miles * 1.609344
                v_med_kh = round((float(linha.split(',')[17]) * 1.609344), 2) if linha.split(',')[17]  'null' else linha.split(',')[17] # Km = miles * 1.609344
                vr_max_kh = round((float(linha.split(',')[18])* 1.609344),2)if linha.split(',')[18]  'null' else linha.split(',')[18] # Km = miles * 1.609344
                p_tot =          round((float(linha.split(',')[19]) * 25.4),2) if linha.split(',')[19]  'null' else linha.split(',')[19] # mm = in * 25,4 
                neblosidade =  (linha.split(',')[20])
                descricao =  (linha.split(',')[21])
                estacao_cod =  (linha.split(',')[22])
                sql = "INSERT INTO dados.meteo_aeroportos ( \
                data, t_max, t_med, t_min, o_max, o_med, o_min, h_max, h_med, h_min, pa_max_in, pa_med_in, pa_min_in, vi_max_km, vi_med_km, vi_min_km, v_max_kh, v_med_kh, vr_max_kh, p_tot, neblosidade, descricao, estacao_cod) \
                VALUES ('%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, '%s', '%s');" % (data, t_max, t_med, t_min, o_max, o_med, o_min, h_max, h_med, h_min, pa_max_in, pa_med_in, pa_min_in, vi_max_km, vi_med_km, vi_min_km, v_max_kh, v_med_kh, vr_max_kh, p_tot, neblosidade, descricao, estacao_cod)
                cur.execute(sql)
            else:
                pass
            x = x + 1
    conn.commit()
    print 'Eliminar repetidos...'
    sql = 'create temp table meteo_aeroportos_t1 as select unico from dados.meteo_aeroportos group by unico having count(unico) > 1;'
    cur.execute(sql)
    sql = 'create temp table meteo_aeroportos_t2 as select max(id) as id, unico from dados.meteo_aeroportos group by unico having count(unico) > 1;'
    cur.execute(sql)
    sql = 'delete from dados.meteo_aeroportos where dados.meteo_aeroportos.unico in (select meteo_aeroportos_t1.unico from meteo_aeroportos_t1) and id not in (select id from meteo_aeroportos_t2);'
    cur.execute(sql)
    conn.commit()
    conn.close()

meteo_aero('base_de_dados', 'utilizador', 'host', 'senha')

Carregar massivamente shp’s em PostgreSQL com um script de Python

Para exportar shp’s a PostgreSQL existe a ferramenta shp2pgsql que faz o tabalho na perfeição. O problema surge se em vez de uns quantos shp’s temos uma barbaridade, numa situação como esta ou automatizamos o processo ou corremos o risco de morrer de tédio. Eliminando a segunda possibilidade, cabe-nos escolher o método a usar. Podemos usar shell script, que no meu caso não estaria mal aprender, ou usar uma linguagem de programação. Assim que vou deixar aqui com um pequeno script escrito em Python para este tipo de situações.

Para usar este script temos que indicar duas coisas, a pasta onde estão os shp’s e o sistema de coordenadas(EPSG). Para facilitar o processo é conveniente ter a cartografia separada por pastas consoante o sistema de coordenadas. Por exemplo: a cartografia de Portugal que está no sistema de coordenadas 20791 está na pasta /cartografia/shp/20791/, a cartografia de Espanha em /cartografia/shp/23030/ e assim por diante.

E o que faz este script? Percorre, recursivamente (em profundidade), as pastas indicadas, procura pela extensão.shp, se a encontra, exporta para a base de dados bdg, cria um indice pelo campo de geometria (geom) e termina com um vacuum analyze.

Agora apenas temos que guardar o codigo que está a seguir a este paragrafo num ficheiro com a extensão.py, adicionar e/ou editar as duas últimas linhas em concordancia com as pastas e sistemas de coordenadas dos shp’s.

import os
import datetime
def postgis_import_shp(inicio, epsg, db, schema):
    principio = datetime.datetime.today()
    conta = 0
    l = os.walk(inicio)
    for i in l:
        directorio, subdirectorios, ficheros = i
        for f in ficheros:
            nome_completo =  os.path.join(directorio, f)
            tema = schema + '.' + f.split('.')[0]
            extensao = f.split('.')[-1]
            try:
                if extensao == 'shp':
                    os.system('shp2pgsql -s %s -g geom -W iso-8859-1 -D -I %s %s %s | psql -U postgres %s' % (epsg, nome_completo, tema, db, db))
                    os.system('psql -U postgres -d %s -c "VACUUM ANALYZE %s(geom);"' % (db, tema))
                    conta += 1
                    print '\n'
            except:
                pass
    print conta, 'shp`s importados em', datetime.datetime.today() - principio
    return

shp2postgis('/cartografia/shp/20791/','20791', 'mome_base_dados', 'nome_esquema')
shp2postgis('/cartografia/shp/23030/','23030', 'mome_base_dados', 'nome_esquema')

Criar uma base de dados espacial em PostgreSQL com a extensão PostGIS

No post anterior vimos como instalar PostgreSQL e hoje veremos como arrancar uma base de dados com funcionalidades SIG com a extensão espacial PostGIS.

O primeiro passo será criar uma base de dados espacial, à qual lhe vou chamar postgis e que servirá de modelo (template) para criar outras bases de dados espaciais. Com esta base de dados criaremos outra que servirá para trabalhar e que lhe darei o nome de bdg, de base de dados geográfica. Os nomes são completamente arbitrários e ao gosto do freguês.

Abrimos a linha de comandos e mãos à massa.

Mudamos de utilizador
su postgres

Criamos a base de dados postgis
createdb -U postgres postgis

E executamos dois scripts
psql -U postgres -d postgis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
psql -U postgres -d postgis -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql

Criamos a base de dados bdg semelhante à postgis
createdb -U postgres bdg -T postgis

Ok já temos as duas bases de dados criadas, bdg e postgis. A partir de agora quando necessitarmos de uma nova base de dados espacial apenas temos que repetir o último passo.

Instalação de PostgreSQL em Ubuntu Intrepid Ibex

Instalação
Abrimos o Gestor de pacotes Synaptic indo a Sistema -> Administração -> Gestor de pacotes Synaptic. Aqui marcamos para instalação as seguintes bibliotecas (pacotes):

postgresql-8.3
postgresql-client-8.3
postgresql-client-common
postgresql-common
postgresql-contrib-8.3
postgresql-8.3-postgis
postgis
pgadmin3
pgadmin3-data

Damos em “Aplicar” e esperamos que o sistema faça o resto por nós.

Agora temos que proceder a umas pequenas configurações para que tudo funcione às mil maravilhas, que vamos ver em seguida.

Mudar a senha do utilizador postgres no gestor da base de dados
Abrimos a linha de comandos (consola) e escrevemos:

sudo su postgres -c psql template1
ALTER USER postgres WITH PASSWORD ‘senha’;
\q

Não esquecer, na segunda linha, de pôr a senha entre aspas simples e o ponto e virgula no final.

Agora temos de mudar a senha do utilizador postgres no sistema linux
Abrimos de novo a linha de comandos e escrevemos:

sudo passwd postgres
aqui pede a senha, pôr a mesma que a anterior

Criamos outro utilizador
Os nomes de utilizadores de PostgreSQL tem de existir previamente no sistema linux, tenham isto presente no próximo passo. No meu caso o nome de utilizador no sistema linux é eloi assim que vou criar um utilizador identico para PostgreSQL. Na linha de comandos escrevemos:

createuser eloi

Conectividade com pgadmin
Para melhorar o desempenho de pgAdminIII com PostgreSQL executamos o seguinte script, como sempre com a linha de comando aberta, escrevemos:

sudo su postgres -c psql < /usr/share/postgresql/8.3/contrib/adminpack.sql

Ok já temos PpostgreSQL instalado e configurado. Podemos começar a usar o nosso gestor de bases de dados atravéz da linha de comandos usando o pgsql ou com uma interfaçe gráfica como o pgAdmiIII.

pgsql
sudo psql -U postgres
\d – lista tabelas
\l – lista bases de dados
\c – mudar de base de dados
\q – sai de pgsql

pgAdminIII
Abrimos o pgAdmin e pressionando no icon com uma ficha criamos uma nova ligação com o servidor:
Nome: O nome la ligação, por exemplo, PostgreSQL, para ser original
Servidor: localhost
Utilizador: postgres
Senha: *******, a mesma que definimos no segundo passo.

Boa sorte!