Posts com Tag ‘PostgreSQL’

Para fazer um bom acompanhamento deste post é recomendável ter um conhecimento geral dos comandos SQL. Um bom guia para isto pode ser o próprio manual do PostgresSQL na sua seção SQL Commands Supported by PostgreSQL.

1. Funções de informação do sistema
O PostgreSQL possui algumas funções de informação do sistema que destinam-se a extrair informações da sessão e do sistema. Exemplos de comandos:

psql=# SELECT version();
psql=# SELECT current_user;
psql=# SELECT current_schema;

2. PostgreSQL Roles Management
O PostgreSQL utiliza o conceito de roles para gerenciar as permissões de acesso ao banco de dados. Um role pode ser um usuário ou um grupo de usuários, dependendo de como se configura. Um role que tem permissão de fazer login é chamado de usuário. Usuários e grupos de usuários são compartilhados por todo “PostgreSQL database cluster”, porém nenhum outro dado é compartilhado entre bancos de dados.

CREATE ROLE adiciona um novo role para o cluster de base de dados do PostgreSQL. Note então que roles são definidos no nível do “database cluster”, sendo válidos para todas as bases de dados do cluster PostgreSQL.

2.1 Login “Roles” (usuários)
No PostgreSQL existe apenas uma espécie de conta: “role”. Alguns roles podem fazer login e são chamadas de “users”.
Exemplos de comandos:
psql=# CREATE ROLE maria LOGIN PASSWORD 'secret' CREATEDB VALID UNTIL 'infinity';
psql=# CREATE ROLE jose LOGIN PASSWORD 'secret' SUPERUSER VALID UNTIL '2014-08-25 18:00';
psql=# ALTER ROLE maria WITH CREATEDB CREATEUSER;
psql=# ALTER ROLE maria WITH CREATEROLE;
psql=# ALTER ROLE aline WITH PASSWORD 'secret';
psql=# DROP ROLE aline;

Veja o manual do PostgreSQL para os comandos SQL CREATE ROLE e ALTER ROLE.

2.2 Group “Roles”
“Roles” podem ser membros de outros roles, onde estes formam um “grupo”. “Group roles” geralmente são roles que não têm atribuições de login e tendo outros roles como membros.
Exemplos de comandos:
psql=# CREATE ROLE group_role INHERIT;
psql=# GRANT group_role TO maria;

3. Tablespaces
Tablespaces no PostgreSQL são diretórios no sistema de arquivos utilizados para armazenamento físico dos dados e objetos (como tabelas e índices) de um banco de dados. Os tablespace divide um banco de dados fisicamente. Apenas super usuários têm permissão de gerenciar tablespaces. Os tablespaces são utilizados para:

  • Gerenciamento de espaço de armazenamento de dados – pode-se armazenar bancos grandes em mídia com tamanho apropriado que esteja disponível na rede.
  • Melhor desempenho – objetos que sofrem mais acessos podem ser alocados em discos mais rápidos, por exemplo, com ganho substancial de desempenho.

Um tablespace pode armazenar dados de diversos bancos de dados, desde que se tenha permissão para acesso aos mesmos. Um banco de dados também pode dividir seus objetos e dados entre vários tablespaces. O nome do tablespace não deve começar com pg_, porque estes nomes são revertidos para o tablespaces do sistema.

Por default, o usuário que executar a chamada SQL CREATE TABLESPACE é o proprietário do tablespace. CREATE TABLESPACE registra um novo tablespace no cluster PostgreSQL. Uma vez que o tablespace esteja criado, pode-se especificar comandos SQL CREATE DATABASE, CREATE TABLE e CREATE INDEX para armazenar arquivos de dados de objetos no tablespace.

Ao se instalar o PostgreSQL, são criados dois tablespaces:

  • PG_DEFAULT – por padrão, quando não é definido explicitamente um tablespace, os dados e objetos de todos os usuários são armazenados no tablespace PG_DEFAULT.
  • PG_GLOBAL – armazena objetos globais, visíveis a todos os bancos

Exemplos de comandos:
shell# mkdir /home/jose/tspace
shell# chown postgres:postgres /home/jose/tspace
psql=# CREATE TABLESPACE dbspace OWNER maria LOCATION '/home/jose/tspace';
psql=# ALTER TABLESPACE dbspace RENAME TO db_data;
psql=# ALTER TABLESPACE db_data OWNER TO jose;
psql=# DROP TABLESPACE db_data;

O diretório deve estar vazio e deve pertencer ao “PostgreSQL system user”. O diretório deve ser especificado através de um caminho absoluto.
Maneiras de visualizar os tablespaces:
psql=# \db
psql=# select * from pg_tablespace;

4. Organizar cada base de dados usando “Esquemas”
Esquemas são uma maneira lógica de particionar uma base de dados em mini-containers. Pode-se dividir os esquemas por funcionalidade, por usuários, ou por qualquer outro atributo. Um banco pode ter dezenas de objetos (tabelas, visões, índices …). Por meio de esquemas é possível organizá-los, atribuindo cada objeto a um esquema. Os esquemas ajudam o gerenciamento de acessos: é possível liberar acesso de usuários a todo um esquema de uma única vez.

Aspectos relevantes sobre os esquemas:

  • cada novo banco de dados criado conterá sempre, por padrão, um esquema chamado “public”;
  • uma base de dados pode conter um ou mais esquemas, que por sua vez conterão as tabelas;
  • um objeto pode ser usado sem conflito em diferentes esquemas. Por exemplo, tanto Esquema1 e Esquema2 poderão conter uma tabela chamada MinhaTabela;
  • para criar ou acessar objetos em um esquema, deve-se escrever um nome qualificado que consiste no nome do esquema e o nome da tabela separados por um ponto: schema.table

CREATE SCHEMA cria em um novo esquema no banco de dados atual. O nome do esquema deve ser distinto do nome de qualquer esquema existente no banco de dados atual.

4.1 Parâmetro de configuração “search_path”
Nomes qualificados são tediosos ​​de escrever, e muitas vezes é melhor não utilizar o nome do esquema no código das aplicações. Portanto tabelas são muitas vezes referidos por nomes não qualificados, que consistem apenas no nome da tabela. O PostgreSQL determina qual tabela está sendo referenciada seguindo um caminho de busca (dado pelo parâmetro de configuração do PostgreSQL “search_path“), que consiste de uma lista de esquemas a procurar. A primeira tabela encontrada no caminho de procura é considerada como sendo a desejada. Se não houver nenhuma correspondência no caminho de procura, um erro é relatado, mesmo que os nomes das tabelas procuradas existirem em outros esquemas no banco de dados.

O primeiro esquema no caminho de procura é chamado de esquema corrente. Além de ser o primeiro esquema a ser pesquisado, também é o esquema em que as novas tabelas serão criadas se o comando CREATE TABLE não especificar um nome de esquema. Este caminho de busca é determinado por um parâmetro de configuração do PostgreSQL chamado search_path. Assim, para visualizar o caminho de busca corrente, use o seguinte comando:
psql=# SHOW search_path;

Quando se cria uma base de dados, o parâmetro “search_path” default é:

search_path="$user",public

4.2 Configuração de sessão – psqlrc
Para automatizar a configuração do ambiente de trabalho do psql deve-se utilizar o arquivo psqlrc (que deve estar na área do sistema indicada por PGSYSCONFDIR) e/ou o arquivo .psqlrc (que deve estar na área raiz do usuário que lançará o psql). Quando o psql inicia, ele lê os comandos SQL de psqlrc e depois do $(HOME)/.psqlrc.

Estes arquivos são utilizados toda vez que o psql for lançado pelo usuário. Será uma configuração válida enquanto existir a sessão de utilização do psql pelo usuário. Ou seja, estes arquivos são válidos a nível de sessão do psql, e não a nível de base de dados ou qualquer outro objeto.

a) Arquivo psqlrc do sistema
No caso do Debian, o arquivo psqlrc deve ser colocado na pasta /etc/postgresql-common/, atentando-se para:

  • caso o usuário lance o psql de sua máquina local, o arquivo psqlrc deverá estar na citada pasta de sua própria máquina local;
  • caso o usuário lance o psql a partir da máquina servidora onde está instalado o PostgreSQL, o arquivo psqlrc deverá estar nesta máquina servidora.

b) Arquivo .psqlrc da área do usuário
Este arquivo deve existir na pasta $HOME do usuário que está lançando o psql.

c) Exemplo de listagem do arquivo para configuração de sessão do psql

-----------------------------------------
-- psqlrc file to set psql preferences --
-- Author : Albuquerque                --
-----------------------------------------
\pset border 2
\echo '\nCurrent Host Server Date Time : ' `date +%x/--/%X` '\n'
\set PROMPT1 '%n@%M:%>%x %/# '
\l
\c teste
SET search_path TO esquema_ti,public

4.3 Exemplos
psql=# CREATE SCHEMA esquema_rh AUTHORIZATION jose;
psql=# ALTER SCHEMA esquema_rh RENAME TO esquema_ti;
psql=# ALTER SCHEMA esquema_ti OWNER TO maria;
psql=# \dn
psql=# \dn+
psql=# \dtvs esquema_ti.*
psql=# SHOW search_path;
psql=# SET search_path TO esquema_ti, public;
psql=# SHOW search_path;
psql=# SELECT current_schema;
psql=# DROP SCHEMA esquema_ti [CASCADE]

5. Tabelas
Por padrão, quando se cria uma tabela sem especificar qualquer nome de esquema, ela é automaticamente colocada em um esquema chamado “public”. Se um nome de esquema for fornecido, então a tabela é criada no esquema especificado. Assim, os dois comandos a seguir são equivalentes:
CREATE TABLE produtos ( ... );
e
CREATE TABLE public.produtos ( ... );

CREATE TABLE irá criar uma nova tabela, inicialmente vazia, no banco de dados atual. A tabela será de propriedade do usuário que emite o comando.

Exemplos de comandos:
psql=# CREATE TABLE esquema_ti.tabela1 (ID INT NOT NULL, NOME VARCHAR(30) NOT NULL, CPF VARCHAR(12) NOT NULL, PRIMARY KEY(ID), UNIQUE (CPF));
psql=# INSERT INTO esquema_ti.tabela1 (ID, NOME, CPF) VALUES (10, 'Justina Silva', '11122233344');
psql=# \d esquema_ti.tabela1;
psql=# SELECT * FROM esquema_ti.tabela1;
psql=# SELECT * FROM tabela1;
psql=# SET search_path TO esquema_ti, public;
psql=# SHOW search_path;
psql=# SELECT * FROM tabela1;
psql=# ALTER TABLE esquema_ti.tabela1 RENAME TO tabela2;
psql=# TRUNCATE TABLE esquema_ti.tabela2;
psql=# DROP TABLE esquema_ti.tabela2 [CASCADE];

Veja também:
1- Tutorial PostgreSQL ( instalar em ambiente Linux – Debian )
2- Documentação do PostgreSQL 8.0.0 (em português)
3- Instalar PostGIS no Linux Debian

Neste conjunto de posts disponibilizamos informações práticas sobre GIS, especialmente OpenLayers, Geoserver e PostGIS.  Na medida do possível, agregaremos receitas (pequenos tutoriais na forma de programas exemplos), de forma a facilitar o entendimento das tecnologias e poder ajudar a quem deseja programar.

Conceitos:
Os padrões WMS e WFS do consórcio OGC – Open Geospatial Consortium
Análise dos dois padrões definidos pela OGC: WMS e WFS, com exemplos da utilização de suas requisições.

OpenLayers: objeto Feature. Que bicho é este?
Análise de como criar as features, associá-lo a um layer vetorial e criar controles de interatividade usuário-features.

OpenLayers: eventos
Informações práticas para utilizar de forma estruturada eventos com a biblioteca OpenLayers.

OpenLayers: as bibliotecas JavaScript GeoExt e ExtJS
Entendendo as bibliotecas, instalando e utilizando.

Receitas com o OpenLayers:

Receita-1: OpenLayers: compreendendo melhor as features – Feature.Vector –
Nesta receita pretende-se explorar os aspectos iniciais de funcionamento da classe feature, layer vetorial e um pouco de estilo de uma feature.

  • Classes utilizadas: OpenLayers.Geometry.Point; OpenLayers.Geometry.LineString; OpenLayers.Layer.Vector; OpenLayers.Feature.Vector; OpenLayers.Util.applyDefaults e OpenLayers.StyleMap.
  • Métodos utilizados: geom_point1.distanceTo(; geom_point1.clone()

Receita-2: OpenLayers: compreendendo melhor um “Layer Vetorial”
Nesta receita pretende-se explorar o protocolo Protocol.HTTP com arquivo GeoJSON para buscar dados remotos no servidor. Com estes dados, serão mostradas as geometrias no mapa.

  • Classes utilizadas: OpenLayers.Protocol.HTTP; OpenLayers.Format.GeoJSON; OpenLayers.Strategy.Fixed e OpenLayers.LonLat.

Receita-3: OpenLayers: compreendendo melhor as features – Control.SelectFeature –
Nesta receita pretende-se explorar o “controle SelectFeature” sobre geometrias tais como: pontos, linhas e polígonos. O usuário selecionará uma ou várias features simultaneamente com um click do mouse.

  • Classes utilizadas: OpenLayers.Geometry.Point; OpenLayers.Geometry.LineString; OpenLayers.Geometry.LinearRing; OpenLayers.Layer.Vector; OpenLayers.Feature.Vector; OpenLayers.Control.SelectFeature e OpenLayers.LonLat.

Receita-4: OpenLayers: compreendendo melhor as features – Control.SelectFeature e eventos
Nesta receita pretende-se explorar o “controle SelectFeature”, mais eventos gerados pelos clicks do mouse “events.register(‘featureselected’, this, selected_feature)” sobre geometrias tais como: pontos, linhas e polígonos. O usuário selecionará uma ou várias features simultaneamente com um click do mouse.

  • Classes utilizadas: OpenLayers.Geometry.Point; OpenLayers.Geometry.LineString; OpenLayers.Geometry.LinearRing; OpenLayers.Geometry.Polygon; OpenLayers.Feature.Vector; OpenLayers.Layer.Vector; OpenLayers.Control.SelectFeature e OpenLayers.LonLat.
  • Métodos utilizados: selected_feature(event); getElementById e events.register(.

Receita-5: OpenLayers: compreendendo melhor as features – controle GetFeature – WFS
Nesta receita pretende-se demostrar como usar o controle GetFeature, utilizando Protocol.WFS, para buscar no servidor features na localização sobre o cursor do mouse.

  • Classes utilizadas: OpenLayers.Layer.Vector; OpenLayers.Feature.Vector.style; OpenLayers.Control.GetFeature; OpenLayers.Protocol.WFS.fromWMSLayer e OpenLayers.LonLat.
  • Métodos utilizados: control.events.register

Receita-6: OpenLayers: utilizando o protocolo WFS para requisitar features de servidor remoto Geoserver
Nesta receita pretende-se mostrar como buscar features em um servidor remoto, adicionando estas informações em um layer vetorial de forma a torná-las visíveis em um mapa.

  • Classes utilizadas: OpenLayers.Layer.Vector, OpenLayers.Strategy.BBOX e OpenLayers.Protocol.WFS

Sobre o Geoserver:
Instalar Geoserver no Linux – Debian
Tutorial para instalar o GeoServer utilizando tanto o container Jetty como o Tomcat numa máquina Linux – Debian. Aspectos de segurança são explorados.

Utilizar o GeoServer
São abordados aspectos referente a configuração do GeoServer para ter acesso a arquivos Shapefile e tabelas do PostGIS, e como trabalhar com arquivos Styled Layer Descriptor (SLD), entre outros.

Instalar a suíte OpenGEO no Linux – Ubuntu
O foco é a instalação da suíte OpenGeo – Community Edition, explorando: acesso a tabelas do PostGIS, instalação e utilização do PgAdminIII e PhpPgAdmin, importação de arquivos Shapefiles para o PostGis, entre outros.

O Geoserver em produção e com segurança
Aspectos importantes para o perfeito funcionamento do servidor de mapas Geoserver em ambiente de produção com Linux – Debian.

Sobre o PostGIS:
Instalar PostGIS no Linux Debian
Instalar extensão PostGIS, comentários sobre a tabela SPATIAL_REF_SYS (e sistemas de referencia espacial); quanto a utilização da extensão PostGIS aborda-se a criação de base de dados, criação de perfis de grupos e usuários, atribuições de permissões as tabelas; detalhamento de como realizar o carregamento de base de dados com arquivos shapes; e detalhes do processo de instalação de bibliotecas complementares (cpmo a Proj.4).

Instalar PostgreSQL + PostGIS no CenTOS
instalar o PostgreSQL e PostGIS no CenTOS, instalar as bibliotecas PROJ.4 e OSGEO, configurar o serviço PostgreSQL e criação de banco de dados espacial.

Ferramentas:
Debug de scripts com Firefox
O Firefox tem extensões que trazem várias falicidades para debug de códigos JavaScript e PHP.

Extensões do PostgreSQL

Publicado: 27/09/2012 em Programação
Tags:,

PostgreSQL dá a possibilidade ao usuário de estender suas funcionalidades através da instalação de módulos adicionais. Os módulos disponíveis para o release 9.1 do Postgresql pode ser encontrado no diretório contrib da distribuição do PostgreSQL.  Estes incluem ferramentas de portabilidade, utilitários análise e plugins que não são parte do “core” do sistema PostgreSQL, principalmente porque eles são dirigidos a um público limitado ou são considerados experimentais para ser parte da sua árvore principal. Embora estes fatores não impessam a sua utilização.

Entre eles está o módulo hstore. Este módulo implementa o tipo de dados hstore para armazenar conjuntos de pares do tipo “key => value” separados por vírgulas, dentro de um valor único PostgreSQL.  Pode ser muito útil para guardar dados desestruturados, tais como tags e tabelas de hashs. Este tipo de dados é similar ao conceito de “associative arrays” do PHP.

1. Baixar as extensões
# apt-get install postgresql-contrib

2. Instalar o módulo hstore
O comando “CREATE EXTENSION” carrega uma nova extensão na base de dados corrente. Carregar uma extensão equivale essencialmente a execução de arquivos de scripts da extensão. O script irá tipicamente criar novos objetos SQL tais como funções, tipos de dados, operadores e suporte a métodos de indexação.

Para usar o comando de criação de uma extensão, deve-se conectar com a base de dados desejada e ter autoridade de “superuser”:
postgres$ psql minha_base
minha_base=# CREATE EXTENSION hstore;
CREATE EXTENSION

Agora pode-se utilizar este tipo de dados normalmente, como no exemplo abaixo de criação de uma tabela:
minha_base=# CREATE TABLE meu_esquema.minha_tabela (gid integer NOT NULL, geom geometry, contador integer, tags hstore);

Referências:
1. Additional PostgreSQL Modules
2. Instalar extensões no PostgreSQL

Será utilizada a versão 8.4.10 do PostgreSQL

1) Baixar Postgres

minha_pasta$ wget -c http://ftp.postgresql.org/pub/source/v8.4.10/postgresql-8.4.10.tar.gz
minha_pasta$ wget -c http://ftp.postgresql.org/pub/source/v8.4.10/postgresql-8.4.10.tar.gz.md5

Verificando a integridade do arquivo baixado:

minha_pasta$ md5sum -c postgresql-8.4.10.tar.gz.md5
postgresql-8.4.10.tar.gz: SUCESSO

2) Documentação de instalação
Para o Postgres: http://www.postgresql.org/docs/8.4/static/index.html

3) Desempacotar o arquivo baixado
minha_pasta$ tar -vzxf nome_arquivo.tar.gz

4) Instalar Headers do Kernel
# apt-get install build-essential linux-headers-$(uname -r)

5) Instalar alguns pacotes requeridos por postgres

# apt-get install bison gawk python-setproctitle zlib1g-dev
# apt-get install libreadline5-dev libreadline6 libreadline6-dev ledit gcc g++ zlibc

6) Compilar Postgres, carregar e instalar
minha_pasta$ cd postgresql-8.4.10/
minha_pasta/postgresql-8.4.10$ ./configure --prefix=/usr/local/postgresql/pgsql8.4 --without-readline
minha_pasta/postgresql-8.4.10$ make

Se obter sucesso neste comando, a última mensagem será: “All of PostgreSQL successfully made. Ready to install”.

minha_pasta/postgresql-8.4.10# make install

Se obter sucesso neste comando, a última mensagem será: “PostgreSQL installation complete”. Neste momento estará criada a pasta especificada anteriormente “/usr/local/postgresql/pgsql8.4” com o conteúdo necessário para por em funcionamento o postgres versão 8.4.10.


# adduser postgres
# mkdir /usr/local/postgresql/pgsql8.4/data
# chown -R postgres:postgres /usr/local/postgresql
# su - postgres
postgres@nome_host:/$ /usr/local/postgresql/pgsql8.4/bin/initdb -D /usr/local/postgresql/pgsql8.4/data

// este último comando para inicializar o cluster de dados do postgres

Se tudo ocorreu corretamente, após o último comando acima a mensagem abaixo deve ser mostrada:

WARNING: enabling “trust” authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb.
Success. You can now start the database server using:
/usr/local/postgresql/pgsql8.4/bin/postgres -D /usr/local/postgresql/pgsql8.4/data
or
/usr/local/postgresql/pgsql8.4/bin/pg_ctl -D /usr/local/postgresql/pgsql8.4/data -l logfile start

7. Deixar o Postgresql pronto para uso
Para iniciar o Postgres:
postgres@nome_host:/$ /usr/local/postgresql/pgsql8.4/bin/postgres -D /usr/local/postgresql/pgsql8.4/data &
Para verificar se processo postgres fora ativado:
$ ps -A | grep postgres
O que se obterá uma resposta semelhante a esta:

20705 pts/2    00:00:00 postgres
20707 ?        00:00:00 postgres
20708 ?        00:00:00 postgres

Criar uma base de dados “test” e iniciar sua utilização:
postgres@nome_host:/$ /usr/local/postgresql/pgsql8.4/bin/createdb test

CREATE DATABASE

postgres@nome_host:/$ /usr/local/postgresql/pgsql8.4/bin/psql test

psql (8.4.10)
Type “help” for help.
test=#

8. Automatizar o Postgresql
Antes de tudo, vamos parar os serviços postgresql que estejam rodando:
# killall postgres
Agora, aos procedimentos de automatização:
postgres@nome_host:/usr/local/postgresql/pgsql8.4/bin$ ln -s postgres postgres8.4
minha_pasta/postgresql-8.4.10# cp contrib/start-scripts/linux /etc/init.d/postgresql8.4
# chmod +x /etc/init.d/postgresql8.4
# mcedit /etc/init.d/postgresql8.4
e fazer 4 (quatro) alterações:

1_)  prefix=/usr/local/postgresql/pgsql8.4
2_)  PGDATA="/usr/local/postgresql/pgsql8.4/data"
3_)  DAEMON="$prefix/bin/postgres8.4"
4_)  echo -n "Starting PostgreSQL: "
     su - $PGUSER -c "$DAEMON -i -D '$PGDATA' &" >>$PGLOG 2>&1 

Agora o administrador já poderá utilizar os comandos:

# /etc/init.d/postgresql8.4 stop
# /etc/init.d/postgresql8.4 start
# /etc/init.d/postgresql8.4 restart

9. Verificações
Para verificar se o PostgreSQL está rodando na portão padrão, utilize o comando netstat:
# netstat -avnp |grep 5432
O resultado esperado deverá ser próximo de:

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               OUÇA       22412/postgres8.4
unix  2      [ ACC ]     STREAM     OUVINDO       69035    22412/postgres8.4   /tmp/.s.PGSQL.5432
netstat: no support for `AF IPX' on this system.
netstat: no support for `AF AX25' on this system.
netstat: no support for `AF X25' on this system.
netstat: no support for `AF NETROM' on this system.

10. Várias versões do Postgresql rodando na mesma máquina
Existem 3 arquivos principais de configuração que controlam o funcionamento básico de um servidor postgresql. Estes 3 arquivos estão todos localizados da pasta de dados default do Postgresql. Na forma como compilamos o Postgresql, estes arquivos estão na pasta /usr/local/postgresql/pgsql8.4/data. Os arquivos são pg_hba.conf, pg_ident.conf e postgresql.conf:
postgresql.conf: controla as definições gerais.
pg_hba.conf: controla a segurança.
pg_ident.conf: mapeia um login autenticado do OS para um usuário Postgresql. O menos utilizado.

Para se ter mais de uma versão do Postgresql sendo executadas na mesma máquina, algumas providências devem ser adotadas inclusive trocar a configuração da porta em que esta versão do servidor Postgresql estará respondendo, que está definida no arquivo postgresql.conf. Por exemplo, suponhamos que já compilamos e instalamos a versão 8.4 do Postgresql conforme os procedimentos descritos anteriormente e desejamos agora também disponibilizar a versão 9.2.3 do Postgresql na mesma máquina. Então, para termos também esta versão do Postgresql 9.2.3 rodando na mesma máquina teríamos que fazer os seguintes passos adicionais:

# su - postgres
postgres@nome_host:$ cd /usr/local/postgresql/pgsql9.2/bin 
postgres@nome_host:/usr/local/postgresql/pgsql9.2/bin$ ln -s postgres postgres9.2
minha_pasta/postgresql-9.2.3# cp contrib/start-scripts/linux /etc/init.d/postgresql9.2
# chmod +x /etc/init.d/postgresql9.2
# mcedit /etc/init.d/postgresql9.2
     prefix=/usr/local/postgresql/pgsql9.2
     PGDATA="/usr/local/postgresql/pgsql9.2/data"
     DAEMON="$prefix/bin/postgres9.2"
     echo -n "Starting PostgreSQL: "
            su - $PGUSER -c "$DAEMON -i -D '$PGDATA' &" >>$PGLOG 2>&1
    
# mcedit /usr/local/postgresql/pgsql9.2/data/postgresql.conf
     //  e trocar a diretiva 'port' para 'port = 5430'
     //  ou seja, mudamos a porta default 5432 para 5430.
    // OBS: não esquecer de reiniciar o Postgresql

Aqui já teríamos disponíveis os comandos:

# /etc/init.d/postgresql9.2 stop
# /etc/init.d/postgresql9.2 start
# /etc/init.d/postgresql9.2 restart

Para verficicar se as duas versões do Postgress estão rodando simultaneamente, use os comandos netstat e ps:
# netstat -avnp |grep 5432
# netstat -avnp |grep 5430
# ps -A | grep postgres
Para este último comando, a resposta poderá ser algo semelhante a:

 2271 pts/0    00:00:00 postgres8.4
 2278 ?        00:00:00 postgres8.4
 2279 ?        00:00:00 postgres8.4
 2423 pts/0    00:00:00 postgres9.2
 2431 ?        00:00:00 postgres9.2
 2432 ?        00:00:00 postgres9.2
 2433 ?        00:00:00 postgres9.2
 2434 ?        00:00:00 postgres9.2
 2435 ?        00:00:00 postgres9.2


10.1
Utilizando o psql com mais de uma instância do servidor Postgresql rodando
O utilitário de interface de linha de comando “psql” é empacotado junto com cada versão do Postgresql. Assim é importante utilizar a versão do psql que corresponde a versão do Postgresql. Para isto, é necessário chamar o psql definindo a porta em que o servidor Postgresql correspondente está respondendo. Para caso descrito anteriormente neste post, isto seria da seguinte forma:

$ /usr/local/postgresql/pgsql8.4/bin/psql -p 5432 -U postgres
$ /usr/local/postgresql/pgsql9.2/bin/psql -p 5430 -U postgres

10.2 Customizando as sessões do psql
O ambiente do psql pode ser customizado através do arquivo “.psqlrc”. Isto é feito colocando este arquivo na pasta raiz do usuário. Por exemplo, para o usuário postgres, criar o arquivo /home/postgres/.psqlrc com o seguinte conteúdo:
\set PROMPT1 '%n@%M:%>%x %/=# '
Ao este usuário chamar o psql (por exemplo, invocando /usr/local/postgresql/pgsql8.4/bin/psql -p 5432), o prompt do psql será apresentado da seguinte forma:
postgres@[local]:5432 postgres=#
Assim, fica mais fácil o usuário identificar qual a instância do Postgresql está sendo utilizada nesta sessão do psql.

11. Phppgadmin com mais de uma versão do Postgresql
A versão do Phppgadmin está ligada a versão do Postgresql. Por exemplo:
Phppgadmin 4.2.3: suporta Postgresql 7.0.x, 7.1.x, 7.2.x, 7.3.x, 7.4.x, 8.0.x, 8.1.x, 8.2.x, 8.3.x
Phppgadmin 5.0.4: suporta Postgresql 7.4.x, 8.0.x, 8.1.x, 8.2.x, 8.3.x, 8.4.x, 9.0.x

Observada esta relação de compatibilidade, uma configuração simples de ser feita para conectar o Phppgadmin a uma das instâncias do Postgresql que está rodando é alterar a configuração de porta no arquivo de configuração do Phppgadmin:
mcedit /etc/phppgadmin/config.inc.php
$conf[‘servers’][0][‘port’] = 5430;
Neste caso, conforme configurações anteriores citadas neste post, o Phppgadmin passará a se conectar com o Postgresql9.2.

Referências
1- Documentação psql 9.2
2- Repositório de arquivos fontes das versões do Postgresql

1- Camada de Abstração
A abstração de banco de dados é um recurso que permite que a aplicação seja portável entre diferenres SGBDs (Sistema de Gerência de Banco de Dados). É uma camada intermediária entre a aplicação e o banco de dados. As bibliotecas de abstração de banco de dados traduzem os comandos fornecidos para os comandos nativos dos SGBDs.

Abstração de banco de dados

Existem muitas bibliotecas de abstração de banco de dados disponíveis para o PHP. Estas bibliotecas (classes) representam uma conecção entre o PHP e um servidor de base de dados. Podemos citar algumas destas bibliotecas:

  • dbx
  • DBA
  • ODBC
  • PEAR::MDB2
  • PDO

1.1- Extensão dbx:
– disponível na biblioteca de extensões PECL (ver adiante) e precisa ser instalada separadamente. Suporta diversos bancos de dados como o MySQL, PostgreSQL, SQL Server, ODBC, Sybase, Oracle e SQLite. Em ambiente com servidores Linux, a dbx necessita ser instalada separadamente fazendo seu download dos arquivos fonte da biblioteca em http://pecl.php.net/package/dbx

1.2- Classe PEAR::MDB2
– trata-se de uma classe externa disponível para download no PEAR (repositório de extensões e aplicações do PHP, ver adiante). Suporta grande variedade de SGBDs, tais como dBase, Interbase, Informix, mSQL, MySQL, ODBC, PostgreSQL, Sybase, Oracle e outros.

1.3- PDO
A extensão “PHP Data Objects” (PDO) define uma interface leve e consistente para acessar bancos de dados em PHP. Cada driver de banco de dados (PDO_SQLITE, PDO_MYSQL e outros conforme listado abaixo) que implementa a interface PDO pode dispor de facilidades específicas da respectiva base de dados como extensão de funções regulares. Note que deve-se usar um driver específico PDO para acessar um determinado servidor de banco de dados.

PDO fornece uma camada de abstração de acesso a dados, o que significa que, independentemente de qual banco de dados se esteja usando, se utiliza das mesmas funções para emitir consultas e buscas de dados. A grande utilidade do PDO é quando se escreve um sistema já pensando na possibilidade dele ser portado para outros bancos de dados ou quando da necessidade de se trabalhar com mais de um tipo de banco. É uma interface de acesso a banco de dados (ao contrário de uma camada de abstração total, esta extensão não gera SQL automaticamente nem emula características faltantes). É uma extensão que tem como objetivo padronizar o acesso a banco de dados usando as mesmas funções independentemente do SGBD.

PDO está empacotado com PHP 5.1, e está disponível como uma extensão PECL (veja abaixo) para PHP 5.0; não funciona com versões anteriores ao PHP 5. PDO e o driver PDO_SQLITE já estão habilitados por default desde o PHP 5.1.0. No entanto, é necessário habilitar o driver PDO para outros SGBD.

Os seguintes drivers estão disponíveis para implementarem interfaces PDO:

  • PDO_FIREBIRD Firebird/Interbase 6
  • PDO_IBM IBM DB2
  • PDO_INFORMIX IBM Informix Dynamic Server
  • PDO_MYSQL MySQL 3.x/4.x/5.x
  • PDO_OCI Oracle Call Interface
  • PDO_ODBC ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
  • PDO_PGSQL PostgreSQL
  • PDO_SQLITE SQLite 2 e SQLite 3
  • PDO_SQLSRV Microsoft SQL Server / SQL Azure

Como instalar os drivers PDO no Debian?
Para o driver PDO_MYSQL:
# apt-get install php5-mysql
O pacote php5-mysql provê um módulo para conexões de banco de dados MySQL diretamente a partir de scripts PHP. Também inclui o módulo pdo_mysql para uso com a extensão PDO.

Para o driver PDO_PGSQL:
# apt-get install php5-pgsql
O pacote php5-pgsql provê um módulo para conexões de banco de dados PostgreSQL diretamente a partir de scripts PHP. Também inclui o módulo pdo_pgsql para uso com a extensão PDO.

Para o driver PDO_SQLITE:
# apt-get install php5-sqlite

2- PEAR
Pear (PHP Extension and Application Repository) oferece o seguinte:
+ uma biblioteca estruturada de código fonte aberto para programadores PHP;
+ sistema de distribuição de código e gerência de pacote
+ estilo de código padronizado desenvolvido em PHP
+ uma biblioteca de extensões para o PHP (PECL)

PEAR é um sistema de estrutura e distribuição para componentes PHP reutilizáveis, criando uma coleção de funcionalidades de adição para o desenvolvimento de PHP. Cada pacote de código PEAR segue um padrão. Existem muitos pacotes PEAR disponíveis que lidam com finalidades diversas: de gerenciamento de seções até funcionalidades de carrinho de compras. Abaixo lista-se algumas categorias de módulos PEAR:

  •   autenticação;
  •   banco de dados;
  •   data/hora;
  •   encriptação;
  •   internacionalização;
  •   correio;
  •   streams;
  •   e muitas outras. A lista completa de módulos pode ser encontrada em http://pear.php.net/packages.php

O PEAR possui um gerenciador de pacotes chamado “PEAR package manager”, que permite a instalação de de novos pacotes, gerenciamento de pacotes já instalados e verificação de dependência entre eles. Esse gerenciador tem sua funcionalidade semelhante ao “apt-get” do Debian Linux. Este pacote “PEAR package manager” contém as classes base PEAR para o PHP, bem como o instalador PEAR. Muitas classes PEAR já estão empacotados para o Debian, e pode ser facilmente identificados por nomes que começam com “php”, como php-db e php-auth.

Para verificar se o PEAR está instalado no seu sistema, verifique a resposta do sistema ao seginte comando:
$ pear
O programa ‘pear’ não está instalado no momento. Você pode instalá-lo digitando: apt-get install php-pear

No caso acima, o sistema informou que o “PEAR package manager” não está instalado. Proceda a instalação com o seguinte comando:
# apt-get install php-pear

Com a instalação do “PEAR package manager”, pode-se utilizá-lo para instalar os pacotes desejados. Por exemplo, para instalar o pacote MDB2:
# pear install MDB2

No caso acima, o instalador denominado pear abre uma conexão HTTP com o servidor de pacotes do PEAR, obtém o pacote MDB2 e o instala no diretório apropriado. No caso do Debian, o arquivo principal dessa classe estará em: /usr/share/php/MDB2.php , e a documentação correspondente em /usr/share/doc/php5-common/PEAR/MDB2/docs/

Para saber quais os pacotes instalados, utilize o seguinte comando conforme demonstrado no exemplo abaixo:
$ pear list
Installed packages, channel pear.php.net:
=========================================
Package Version State
Archive_Tar 1.3.7 stable
Console_Getopt 1.3.0 stable
MDB2 2.4.1 stable
PEAR 1.9.4 stable
Structures_Graph 1.0.4 stable
XML_Util 1.2.1 stable

3- PECL
PECL (PHP Extension Community Library) é um projeto que distribui extensões PHP (códigos compilados escritos em C) para compilação com o PHP. PECL inclui módulos para muitas finalidades. Extensões PECL são distribuídos como pacotes e podem ser instalados utilizando o instalador PEAR com o comando “pecl”.

O repositório PECL pode ser encontrado em http://pecl.php.net/
A lista de extensões PHP pode ser visualizada em http://pecl.php.net/packages.php

Links:
1- Driver PDO_SQLITE
2- Instalações PECL


1. Introdução

PostGIS adiciona suporte para objetos geográficos ao banco de dados objeto-relacional PostgreSQL. A primeira versão do PostGIS foi liberada em 2001 sob licença GPL.Com efeito, PostGIS “habilita espacialmente” o servidor PostgreSQL, permitindo a ele seja utilizado como um banco de dados espacial back-end (servidor de retaguarda) para sistemas de informação geográfica (SIG).

PostGIS segue o OpenGIS “Simple Features Specification for SQL” e foi certificado como compatível com os perfis de “Tipos e funções”. Diversas ferramentas trabalham com PostGIS, tais como uDIG, Mapserver, GeoTools, GeoServer, GRASS, OpenJUMP, gvSIG e QGIS. E naturalmente qualquer linguagem que trabalha com PostgreSQL pode trabalhar com PostGIS — a lista inclui Perl, PHP, Python, TCL, C, C++, Java, C#, e outras.

2. Exigências
Para detalhes de instalação do PostgreSQL no Linux Debian, veja o post “Tutorial PostgreSQL ( instalar em ambiente Linux – Debian )“.

3. Instalar extensão PostGIS
Após ter instalado o PostgrSQL, realizar os seguintes procedimentos:

3.1) Verificar instalações e versões disponiveis de postgreSQL e postGIS

$ apt-cache policy postgresql
postgresql:
Instalado: 8.4.10-0squeeze1
Candidato: 8.4.10-0squeeze1
Tabela de versão:
*** 8.4.10-0squeeze1 0

$ apt-cache policy postgis
postgis:
Instalado: (nenhum)
Candidato: 1.5.1-5
Tabela de versão:
1.5.1-5 0

Com a resposta aos dois comandos acima, conclui-se que PostgreSQL está instalado na máquina alvo (versão 8.4), mas que o PostGIS não está instalado. A versão do PostGIS disponível no repositório Debian candidata à instalação é a 1.5.

3.2) Instalar o PostGIS referente a versão 8.4 do PostgreSQL

# apt-get install postgis postgresql-8.4-postgis

Onde:

  1. postgis :contém binários do PostGIS em nível usuário, arquivos comuns e documentação. Sugere postgresql-8.4-postgis
  2. postgresql-8.4-postgis :contém suporte a objetos geográficos para o PostgreSQL 8.4. Depende de postgis e de postgresql-8.4

Após a instalação descrita acima, o manual do PostGIS ficará depositado em file:///usr/share/doc/postgis/postgis/postgis.html , com mais detalhes quanto a sua instalação e configuração.

3.3) Criar perfil de grupo e usuário
Usualmente, a melhor maneira de manipular dados GIS no banco de dados PostgreSQL é usando papel e usuário diferentes do default “postgres”, que deve ser usado apenas para tarefas administrativas. No exemplo deste post, será criado o papel e usuário “gis” para a manipulação de dados no servidor PostgreSQL. Para se obter um ambiente mais seguro, podem ser criados outros usuários com diferentes direitos (SELECT, INSERT, UPDATE, DELETE) sobre as diferentes classes SIG.

Conectar ao servidor PostgreSQL (como usuário postgres), criar os papeis “gisgroup” e “gis” (se desejado, escolha menos permissões por razões de segurança), e atribuir a função de login “gis” para o papel do grupo “gisgroup”:

# su - postgres
postgres$ psql
psql=# CREATE ROLE gisgroup NOSUPERUSER NOINHERIT CREATEDB NOCREATEROLE;
psql=# CREATE ROLE gis LOGIN PASSWORD 'minhasenha' NOINHERIT;
psql=# GRANT gisgroup TO gis;

Observe que agora o usuário “gis” já pode fazer login no servidor PostgreSQL com senha “minhasenha”. Uma maneira fácil de fazer isto é fazer login no servidor PostgreSQL através do aplicativo phpPgAdmin.

a) Verificar o que fora criado

psql=# \dg
                         Lista de roles
+--------------+------------------------+-----------------------+
| Nome da role |       Atributos        |       Membro de       |
+--------------+------------------------+-----------------------+
| gis          | Nenhuma herança        | {gisgroup}            |
| gisgroup     | Nenhuma herança        | {}                    |
|              : Cria BD                                        |
|              : Não pode efetuar login                         |
| postgres     | Super-usuário          | {}                    |
|              : Cria role                                      |
|              : Cria BD                                        |
+--------------+------------------------+-----------------------+


3.4) Criar um template de base de dados PostGIS
Isto é prático, tornando fácil a criação das bases de dados GIS no servidor. Sem a criação deste template, o usuário teria que repetir todos os passos abaixo todas as vezes que necessitasse criar uma base de dados PostGIS. O template é a mesma coisa de uma base de dados normal, mas é marcada como template para não ser removida ou alterada inadvertidamente. O resultado deste comando são as funções do PostGIS carregadas dentro deste template de banco de dados:


# su - postgres
postgres$ psql
psql=# create database postgistemplate with template = template1 encoding = 'utf8' owner = gis;
psql=# COMMENT ON DATABASE postgistemplate IS 'postgis template database';
psql=# UPDATE pg_database SET datistemplate=true WHERE datname='postgistemplate';
psql=# \c postgistemplate
psql=# create language plpgsql;
psql=# \i /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql=# \i /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql=# \q

Ou ainda de outra forma:

# su - postgres
postgres$ createdb postgistemplate -T template1 -E utf-8 -O gis "postgis template database"
postgres$ createlang plpgsql postgistemplate
postgres$ psql -d postgistemplate -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
postgres$ psql -d postgistemplate -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
postgres$ psql
psql=# UPDATE pg_database SET datistemplate=true WHERE datname='postgistemplate';
psql=# COMMENT ON DATABASE postgistemplate IS 'postgis template database';
psql=# \q

Onde:

  1. createdb :cria uma nova base de dados PostgreSQL. O usuário que executa este comando se torna o proprietário desta nova base de dados (no caso “postgres”).
  2. createlang :PostGIS requer a extensão de linguagem procedural PL/pgSQL, pois muitas das funções PostGIS são escritas nesta linguagem. Para habilitar a PL/pgSQL, deve-se usar este comando “createlang”. É um utilitário que adiciona uma nova linguagem de programação para um banco de dados PostgreSQL. É apenas um invólucro em torno do comando CREATE LANGUAGE.
  3. createlang -d :banco de dados para instalar linguagem psql.
  4. psql :trata-se de um terminal “front-end” interativo para o PostgreSQL. Ele permite que se digite os comandos interativamente, enviá-los para o servidor PostgreSQL e ver os resultados dos comandos.
  5. psql -d :especifica o nome do banco de dados para conectar-se.
  6. psql -f :usa o arquivo descrito após a chave como a fonte de comandos, em vez de ler comandos interativamente.
  7. postgis.sql :arquivo com definições de funções e objetos do PostGIS.
  8. spatial_ref_sys.sql :arquivo de definições utilizado para um sistema completo de identificadores de definições de coordenadas EPSG. Permitirá que se execute operações ST_Transform() em geometrias.
  9. UPDATE pg_database :marcar a nova base de dados postgistemplate como um template.

OBS: opcionalmente, se desejar um “help”(comentários) das descrições das funções incluídas no PostGIS, instalar o arquivo “postgis_comments.sql”. Os comentários poderão ser vistos simplesmente digitando \dd [function_name] a partir de uma janela de terminal psql. Proceder assim:

postgres$ psql -d postgistemplate -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

O template já está pronto: fora criada o banco de dados “postgistemplate”, com várias funções disponíveis e com duas tabelas (“geometry_columns” e “spatial_ref_sys”). Agora podemos testar o funcionamento do banco de dados postgistemplate que acabamos de criar:

postgres$ psql -d postgistemplate -c "SELECT postgis_full_version();"
postgis_full_version
——————————————————————————————————-
POSTGIS=”1.5.1″ GEOS=”3.2.0-CAPI-1.6.0″ PROJ=”Rel. 4.7.1, 23 September 2009″ LIBXML=”2.7.7″ USE_STATS
(1 registro)

Como mostra a resposta ao comando, vemos o PostGIS em funcionamento com sua versão 1.5. Se a máquina estiver com o phpPgAdmin, acesse este “front-end” (se o phpPgAdmin estiver instalado na máquina local clique aqui para fazer o acesso) e observe o banco de dados criado, as tabelas e as funções espaciais já disponíveis.

Verificar o que fora criado

psql=# \l+
                                                             Lista dos bancos de dados
+-----------------+----------+-------------+-------------+-------------+-----------------------+---------+------------+---------------------------+
|      Nome       |   Dono   | Codificação |  Collation  |    Ctype    | Privilégios de acesso | Tamanho | Tablespace |         Descrição         |
+-----------------+----------+-------------+-------------+-------------+-----------------------+---------+------------+---------------------------+
| postgistemplate | gis      | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 |                       | 9494 kB | pg_default | postgis template database |
| postgres        | postgres | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 |                       | 5510 kB | pg_default |                           |
| template0       | postgres | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 | =c/postgres           | 5408 kB | pg_default |                           |
|                                                                      : postgres=CTc/postgres                                                    |
| template1       | postgres | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 | =c/postgres           | 5510 kB | pg_default | default template database |
|                                                                      : postgres=CTc/postgres                                                    |
+-----------------+----------+-------------+-------------+-------------+-----------------------+---------+------------+---------------------------+

3.5) Atribuir permissões as tabelas
Atribuir permissões para as tabelas do banco de dados “postgistemplate” (“geometry_columns” e “spatial_ref_sys”), que terão como proprietário o usuário “gis”). Sair da conexão anterior (digite \q), e se conectar ao banco de dados “postgistemplate” ainda como usuário postgres e: atribuir permissões, criar um esquema para os dados gis (não se deve criar os dados gis em esquema “public”) e, por último, desfazer a conexão com o banco de dados. Para isto, realizar os seguintes comandos:

postgres$ psql -d postgistemplate
psql=# ALTER TABLE geometry_columns OWNER TO gis;
psql=# ALTER TABLE spatial_ref_sys OWNER TO gis;
psql=# CREATE SCHEMA gis_schema AUTHORIZATION gis;
psql=# \q

4) A tabela SPATIAL_REF_SYS Table e sistemas de referencia espacial
Existem duas tabelas de meta-dados do OpenGIS: SPATIAL_REF_SYS e GEOMETRY_COLUMNS. Estas tabelas de sistema do PostGIS são criadas automaticamente quando da criação de uma base de dados. A tabela SPATIAL_REF_SYS carrega os identificadores numéricos e descrições textuais de sistemas de coordenadas utilizadas no banco de dados espacial.

A tabela spatial_ref_sys é uma inclusão do PostGIS e compatível com a tabela de base de dados da OGC que lista mais de 3000 sistemas de referência espacial conhecidos e possibilita realizar diferentes combinações de projeções e “datums”, necessárias para mostrar com precisão dados geográficos de todas as áreas do planeta. Deve-se ter em mente que a maioria dos sistemas de referência espaciais são regionais e não têm significado quando usado fora dos limites que foram destinados.

Uma excelente fonte de informações para encontrar sistemas de referência espacial não definidos de forma centralizada e pública pode ser encontrado em http://spatialreference.org/

Quando instalado o PostGIS, a sua documentação disponível localmente em file:///usr/share/doc/postgis/postgis/postgis.html#id313360 traz mais detalhes sobre estas tabelas.

5) Criar base de dados
Após os procedimento anteriores, podem ser criados os bancos de dados necessários. Como exemplo neste post, será criado o banco de dados “gisdb”, cujo proprietário será o usuário “gis”, utilizando o template “postgistemplate”:

  • Do shell:
    • postgres$ createdb gisdb -T postgistemplate -E utf-8 -O gis "postgis database"
  • Do psql:
    • postgres$ psql
      psql=# CREATE DATABASE gisdb with TEMPLATE = postgistemplate OWNER = gis ENCODING = 'utf8';
      psql=# COMMENT ON DATABASE gisdb IS 'postgis database';
      psql=# \q

Para verificar:

psql=# \l+
                                                             Lista dos bancos de dados
+-----------------+----------+-------------+-------------+-------------+-----------------------+---------+------------+---------------------------+
|      Nome       |   Dono   | Codificação |  Collation  |    Ctype    | Privilégios de acesso | Tamanho | Tablespace |         Descrição         |
+-----------------+----------+-------------+-------------+-------------+-----------------------+---------+------------+---------------------------+
| gisdb           | gis      | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 |                       | 9486 kB | pg_default | postgis database          |
| postgistemplate | gis      | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 |                       | 9486 kB | pg_default | postgis template database |
| postgres        | postgres | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 |                       | 5510 kB | pg_default |                           |
| template0       | postgres | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 | =c/postgres           | 5408 kB | pg_default |                           |
|                                                                      : postgres=CTc/postgres                                                    |
| template1       | postgres | UTF8        | pt_BR.UTF-8 | pt_BR.UTF-8 | =c/postgres           | 5510 kB | pg_default | default template database |
|                                                                      : postgres=CTc/postgres                                                    |
+-----------------+----------+-------------+-------------+-------------+-----------------------+---------+------------+---------------------------+



5.1) Carregar base de dados

  • Gerar o arquivo .sql
    O carregador de dados shp2pgsql converte arquivos ESRI Shape em declarações adequadas SQL para inserção posterior em um banco de dados PostGIS/PostgreSQL tanto no formato de geometria como no de geografia. A título de exemplo, seria realizado da seguinte forma:
    shp2pgsql -s 4674 -W LATIN1 12UFE250GC_SIR.dbf ufe12250 > ufe12250.sql
    O que resulta o comando acima? Um arquivo sql com comandos para criar uma tabela entitulada “ufe12250” e tendo também uma coluna com o mesmo nome.
    OBS: é importante que o nome da tabela que irá ser gerada posteriormente na base de dados não inicie com um dígido, e sim por um caracter alfabético. A razão disto é que algumas transações XML exigem que os nomes dos layers/tabelas estejam iniciados por este tipo de caracter.
  • Carregar os dados no banco de dados “gisdb”
    $ psql -d gisdb -U gis -f ufe12250.sql
    OBS: para evitar erro de conexão neste comando (conforme melhor descrito no post “Tutorial PostgreSQL:instalar em ambiente Linux – Debian“), certificar-se que no arquivo de configuração do PostgreSQL /etc/postgresql/8.4/main/pg_hba.conf possui uma entrada semelhante a:

    local   all         all			md5
    

6) Um pouco sobre “Sistema de Referência Espacial” (SRS)
Sistema de referência espacial (SRS) é um mecanismo para situar mensurações sobre um corpo geométrico, tal como a terra; estabelece um ponto de origem, orientação de eixos de referência, e significado geométrico das mensurações, assim como unidades de medida. De outra forma, um SRS é um conjunto de parâmetros usado para representar uma geometria.

É comum utilizar apenas um número para nos referir a uma referência espacial. Este número, é o SRID. Veja estes dois exemplos: EPSG 4326 e EPSG 4674. Nestes casos, temos:

  • EPSG 4326 se refere ao GCS WGS84;
  • EPSG 4674 se refere ao GCS SIRGAS-2000.

Obs:
i) GCS – geographic coordinate system;
ii) EPSG – é uma codificação definida pelo “European Petroleum Survey Group” que associa uma codificação numérica a um sistema de coordenadas cartográficas.

O CGS WGS84 é o sistema geodésico desenvolvido pelo Departamento da Defesa dos Estados Unidos da América. É o sistema de referência atualmente utilizado pelo GPS. A origem das coordenadas deste sistema geodésico é o centro da Terra, obtendo-se um erro é inferior a 2cm.

O GCS SIRGAS-2000, Sistema de Referencia Geocêntrico para as Américas, é o sistema de referenciamento espacial padrão do Brasil, definido pelo IBGE. Foi oficialmente adotado como Referencial Geodésico Brasileiro em 2005, através da Resolução do IBGE N°1/2005. Na prática o SIRGAS-2000 utiliza o mesmo elipsóde de referência do WGS84 – GRS 1980. O mesmo ocorre com o posicionamento do elipsóide, com seus centros situando-se bem próximos um do outro. Isto significa que as medidas dados pelo SIRGAS-2000 são quase iguais aos dados pelo WGS84, onde uma diferença entre as medidas pode ser menor que o erro aproximado de um instrumento GPS pessoal, pois esta diferença está na ordem de centímetros. Desta forma, para fins práticos, ou seja, para todos os usuários que não precisam de qualidade superior ao centímetro, é indiferente usar WGS84 ou SIRGAS-2000.

Sobre este tópico, existem boas referências na internet, entre as quais:
FAQ IBGE
Sistema de Referenciamento Espacial

7) A malha digital de municípios publicado pelo IBGE
O IBGE publica a malha digital de municípios a cada censo realizado. No momento deste post, o último censo demográfico fora realizado em 2010 e a malha digital referente pode ser baixada de ftp://geoftp.ibge.gov.br/malhas_digitais/municipio_2010/ . As bases cartográficas disponibilizadas são compatíveis com a escala 1:250.000, sem supressão de pontos.

Este produto cartográfico do IBGE apresenta as seguintes unidades territoriais: municípios, microrregiões, mesorregiões e unidades da Federação. As bases cartográficas utilizam as seguintes referência geodésica e cartográfica:
– Sistema Geográfico – Sistema de Coordenadas Lat / Long – não projetado
– Sistema Geodésico – SIRGAS2000

Os arquivos shapefiles disponibilizados pelo IBGE estão em 3D, e para facilitar sua utilização pelo serviço WFS com o Geoserver (a partir de clientes como o OpenLayers, QGIS e outros) sugerimos fazer ajustes nos arquivos shapefiles. É que o Geoserver parece ainda se mostrar imaturo para tratar o 3D, pelo menos no momento da publicação deste post. No serviço WMS a terceira dimensão é removida, porém o mesmo código de remoção parece que ainda não está disponível para o caso do WFS.

O ajuste sugerido é transformar as fontes shapefiles de 3D para 2D antes de subir as informações cartográficas para o banco de dados. Um método fácil para esta transformação 3D/2D é utilizando a biblioteca GDAL (Geospatial Data Abstraction Library). GDAL é uma biblioteca de tradução para formatos de dados raster geospatial. Está incluída nesta biblioteca o código da biblioteca OGR que possui funcionalidades semelhantes às da GDAL, só que destinada para se trabalhar com dados vetorias (Simple Features). É esta biblioteca OGR que iremos utilizar.

/* instalar a biblioteca GDAL */
# apt-get install gdal-bin
/* se a bilbioteca OGR fora instalada com sucesso, o comando a seguir trará a seguinte resposta: */
$ ogrinfo
Usage: ogrinfo [--help-general] [-ro] [-q] [-where restricted_where]
               [-spat xmin ymin xmax ymax] [-fid fid]
               [-sql statement] [-al] [-so] [-fields={YES/NO}]
               [-geom={YES/NO/SUMMARY}][--formats]
               datasource_name [layer [layer ...]]

Para alterar o arquivo shapefile de 3D para 2D e já disponibilizar as informações numa tabela do PostgreSQL, poderia ser executado o seguinte comando:
$ogr2ogr -update -append -f PostgreSQL PG:"dbname=nome_BD user='usuario_BD' password='senha_BD'" 12UFE250GC_SIR.shp -s_srs EPSG:4674 -nln ufe12250b -nlt MULTIPOLYGON

Onde:
-f nome do formato do arquivo de saída. No caso, PostgreSQL.
-update: abrir a fonte de dados exitente de saída no modo “update”, em vez de tentar criar uma nova.
-append: anexa as informações para o layer existente em vez de criar um novo
-overwrite: apaga o layer de saída e recria um novo (embora que não fora utilizado neste exemplo)
-s_srs: substitui a fonte SRS na saída
-nln: nome do novo layer, ou seja, um nome para a tabela que será criada.
-nlt: define o tipo da geometria para o layer que será criado. Pode ser: NONE, GEOMETRY, POINT, LINESTRING, POLYGON, GEOMETRYCOLLECTION, MULTIPOINT, MULTIPOLYGON or MULTILINESTRING.

Nos testes que realizei, fiz um procedimento mais “controlado” em 3 etapas, conforme abaixo. E funcionou tudo perfeitamente:
$ ogr2ogr -t_srs EPSG:4674 -nlt MULTIPOLYGON ma/arq_novo4674.shp ma/21UFE250GC_SIR.shp
$ shp2pgsql -s 4674 -W LATIN1 ma/arq_novo4674.shp ufemaranhao > ma/ufemaranhao.sql
$ psql -d nome_BD -U usuario_BD -f ma/ufemaranhao.sql

Neste exemplo acima, tem-se:
– arq_novo4674: arquivos shapefiles que serão gerados (.dbf, .prj, .shp, .shx) pelo comando ogr2ogr, já na forma 2D;
– ma/21UFE250GC_SIR.shp: arquivo de entrada, referente ao estado do Maranhão, que tomamos a partir do original fornecido pelo IBGE. Esse arquivo está na pasta “ma” logo abaixo do diretório corrente de trabalho do usuário;
– ufemaranhao: nome da tabela que será gerada posteriormente no banco de dados PostgreSQL, qundo da execução do psql a seguir;
– ufemaranhao.sql: nome do arquivo com comandos SQL que será gerado pela execução do shp2pgsql.

Obs: sobre como o PostgreSQL trata os conjuntos de caracteres (ISO-8859 e UTF-8, por exemplo), ver o manual do PostgreSQL na sessão Character Set Support.

7.1 Tamanho dos arquivos
Sempre se deve ter cuidado com o tamanho dos arquivos. Quanto maior a escala, maior o arquivo. Veja o resultado comparativo para o estado de Pernambuco entre as malhas digitais fornecidas pelo IBGE da escala 1:250k e 1:2500k, conforme abaixo:

IBGE-2010 (1:250k) IBGE-2007 (1:2500k)
shapefile 26MUE250GC_SIR.shp 1608kB shapefile 26mu2500gsr.shp 261kB
download para serviço WFS de 1100kB download para serviço WFS de 278kB

8) O arquivo de Password .pgpass
Uma forma bastante prática de acessar o banco de dados PostgreSQL sem ter a necessidade de se digitar todas as vezes o password é criando o arquivo de autenticação (password) .pgpass, conforme orienta o manual do PostgreSQL na sessão The Password File. Isso pode ser um facilitador quando se utiliza recorrentemente comandos psql.

Este arquivo de password deve ser colocado no diretório home do usuário. Desta forma, o usuário PostgreSQL pode conectar ao banco sem informar senha, pois a mesma já estará informada neste arquivo. O formato deste arquivo é o seguinte:

hostname:port:database:username:password

Veja este exemplo: localhost:5432:*:jose:senha_jose
Onde deve ser observado:
– jose deve ser um usuário do banco PostgreSQL e não do S.O;
– no caso do usuário root, o arquivo deve estar em em /root/.pgpass
– este arquivo deve ter obrigatoriamente a permissão 0600 (comando “chmod 0600 .pgpass”). Se a permissão for menos restritiva que 0600, o arquivo será ignorado.

9) Bibliotecas complementares
As seguintes bibliotecas estão disponíveis, mas não são obrigatórias para um uso inicial do PostGIS:
# apt-get install proj libgeos-c1 libgeos-dev

Proj4 é uma biblioteca responsável por conversões de projeções.
GEOS (Geometry Engine – Open Source) és uma porta C++ da Java Topology Suite (JTS). Ela objetiva alcançar uma completa funcionalidade do JTS no C++. Ou seja, a total compatibilidade do PostGIS com a SFS.

Referências:
1- Tutorial PostgreSQL ( instalar em ambiente Linux – Debian )
2- Manual: installation PostGIS
3- GIS Tutorials
4- Installing PostGIS on debian GNU/Linux
5- Installing PostGIS on Ubuntu
6- Installing postgis and mapserver in Debian
7- Debian Postgis
8- Importando/Exportando dados vetoriais ao PostGIS
—-
9- OGR SQL
10- Malha geométrica dos municípios brasileiros
11- Reprojecting features using OGR
12- OGR to reproject, modify Shapefiles

1) No momento deste post, a versão 8.4.4 é a versão mais atual do PostgreSQL. Caso haja alguma versão postgres instalada na máquina, faça sua desinstalação antes seguir os  procedimentos aqui descritos.

2) Instalar versão mais recente do PostgreSQL:

# yum list | grep postgresql
# yum install postgresql-server.i686
# yum install postgresql-contrib.i686
# yum install postgresql.i386
# yum install postgresql-devel.i686

OBS: Ver documento /usr/share/doc/postgresql-8.4.4# cat README.rpm-dist

3) Baixar, desempacotar e instalar biblioteca PROJ.4 necessário ao PostGIS:
/usr/src/postgis# wget -c ftp://ftp.remotesensing.org/proj/proj-4.4.8-1.i386.rpm
/usr/src/postgis# rpm -ivh proj-4.4.8-1.i386.rpm
/usr/src/postgis# cd proj-4.7.0
/usr/src/postgis/proj-4.7.0# ./configure
/usr/src/postgis/proj-4.7.0# make && make install
/usr/src/postgis/proj-4.7.0# ldconfig

4) Baixar, desempacotar e instalar biblioteca libxml necessário ao PostGIS 5+:
/usr/src/postgis# wget ftp://xmlsoft.org/libxml2/libxml2-2.7.6.tar.gz
/usr/src/postgis# tar -xvf libxml2-2.7.6.tar.gz
/usr/src/postgis# cd libxml2-2.7.6
/usr/src/postgis/libxml2-2.7.6# ./configure
/usr/src/postgis/libxml2-2.7.6# make && make install
/usr/src/postgis/libxml2-2.7.6# ldconfig

5) Baixar, desempacotar e instalar biblioteca OSGEO necessário ao PostGIS:
/usr/src/postgis# wget http://download.osgeo.org/geos/geos-3.2.0.tar.bz2
/usr/src/postgis# tar -xvf geos-3.2.0.tar.bz2
/usr/src/postgis# cd geos-3.2.0
/usr/src/postgis/geos-3.2.0# ./configure
/usr/src/postgis/geos-3.2.0# make && make install
/usr/src/postgis/geos-3.2.0# ldconfig

6) Baixar, desempacotar e instalar versão 1.5 do postgis:

/usr/src/postgis/# yum list | grep postgis
/usr/src/postgis/# wget -c http://www.postgis.org/download/postgis-1.5.2.tar.gz
/usr/src/postgis/# tar -xvf postgis-1.5.2.tar.gz
/usr/src/postgis/postgis-1.5.2# aclocal
/usr/src/postgis/postgis-1.5.2# ./autogen.sh
/usr/src/postgis/postgis-1.5.2# ./configure
/usr/src/postgis/postgis-1.5.2# make && make install
/usr/src/postgis/postgis-1.5.2# ldconfig

7) Configurar e iniciar o serviço Postgresql após sua instalação:
# rm -rf /var/lib/pgsql/data/
# service postgresql initdb
# service postgresql start

8 ) Criar um template_postgis:
a) postgres=# create database template_postgis with template = template1 encoding = 'utf8';
CREATE DATABASE

b) postgres=# \c template_postgis;
You are now connected to database “template_postgis”.

c) template_postgis=# create language plpgsql;
CREATE LANGUAGE

9) Colocar o PostGIS em um banco de dados:
postgres=# \i /usr/src/postgis/postgis-1.5.2/postgis/postgis.sql;
postgres=# \i /usr/share/pgsql/contrib/postgis-1.5/spatial_ref_sys.sql;
postgres=# \i /usr/src/postgis/postgis-1.5.2/doc/postgis_comments.sql;

10) Preparar:
postgres=# update pg_database set datistemplate = true where datname = 'template_postgis';
postgres=# GRANT ALL ON geometry_columns to PUBLIC ;
postgres=# GRANT ALL ON spatial_ref_sys to PUBLIC ;

11) Criar um banco de dados para a sua aplicação:
postgres=# CREATE DATABASE cartcult with template = template_postgis;
CREATE DATABASE
OBS: no caso aqui chamei o banco de dados de ‘cartcult’

12) Alterar senha do banco para ‘postgres’ para facilitar os trabalhos (escolha uma outra posteriormente):
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

13) Reiniciar o postgres:
# /etc/init.d/postgresql restart

14) Verificar o funcionamento e as versões do PostgreSQL, PostGIS de bibliotecas:

a) PostgreSQL:
postgres=# SELECT version();

version
—————————————————————————————————————-
PostgreSQL 8.4.4 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 32-bit

b) PostGIS:
postgres=# SELECT postgis_full_version();

postgis_full_version
——————————————————————————————————–
POSTGIS=”1.5.2″ GEOS=”3.2.2-CAPI-1.6.2″ PROJ=”Rel. 4.7.1, 23 September 2009″ LIBXML=”2.6.23″ USE_STATS

Referências:
1. Instalar PostGIS no Linux Debian
2. Tutorial CentOS
3. Manual: installation PostGIS
4. An almost idiot’s guide to Install and Upgrade to PostgreSQL 8.4 with Yum
5. Compiling PostGIS 1.5 and installing after Yum PostgreSQL Install