Verificar se um ponto esta contido em um poligono

Esta cada vez mais recorrente a pergunta:

"Esta coordenada (latitude, longitude) de um equipamento esta dentro do município?"

A consulta baixo permite responder esta pergunta.

select ST_Contains(the_geom, ST_Point(longitude::float,latitude::float) ), uf, nome
from mapas.shp_munic
order by uf, nome

Com a base do IBGE de municípios, pode-se pegar as informações de (latitude, longitude) e executar a pesquisa.

Copia de tabelas em servidores diferentes com PLPGSQL

Um desafio que apareceu foi fazer cópia de 54 tabelas de um servidor Postgres de um servidor para outro. Estas tabelas são formadas por 2 grupos de 27 tabelas referentes à 27 unidades da federação. O primeiro caminho que segui para solução do problema, foi fazer uma cópia de segurança, vulgo BACKUP, e restaurar em outro servidor. O problema apareceu quando percebi que o arquivo resultante da cópia era muito grande e o processo de restauração levaria mais tempo que a cópia. Assim passei para outra solução, criar um script para fazer a cópia da tabela. Para isso utilizei o dblink e a linguagem plpgsql.

O código para criar a função foi:


create or replace function copia_pessoa(esquema varchar, uf varchar) returns text as $$
declare
     tabela1 varchar;

begin
        tabela1 := 'tab_cad_'|| esquema || '.tb_pessoa_uf_' || uf;
RAISE NOTICE 'Tabela destino %' ,tabela1;
execute 'create table '|| tabela1 ||' as
select * from dblink 
(''port=porta dbname=database host=iphost user=user password=senha'',
''select * from  ' || tabela1 || ' '') as t1
(
  cd_ibge character varying(13),
  cod_familiar_fam character varying(11),
  num_membro_fmla character varying(11),
  marc_pbf numeric(8,0),
  marc_bsp numeric(8,0)
)' ;

execute ' CREATE INDEX idx_cd_ibge_uf'||uf||'_pess_'||esquema||'   ON ' || tabela1 || '  USING btree   (cd_ibge)';

execute ' CREATE INDEX idx_cod_familiar_fam_uf' ||uf|| '_pess_' ||esquema||' ON '|| tabela1 ||' USING btree   (cod_familiar_fam)';



return 'Sucesso ' || tabela1;
end;

$$ language plpgsql;


Destaco que neste código pude fazer com que o nome da tabela é uma variável conforme o parâmetro passado.

tabela1 := 'tab_cad_'|| esquema || '.tb_pessoa_uf_' || uf;


O nome da tabela é composto pelo nome do esquema e código do ibge da UF.

O comando EXECUTE permite que o comando SQL seja executado com parâmetros, neste caso o nome da tabela.

execute 'create table '|| tabela1 ||' as
select * from dblink ...';

Para utilizar a função pode-se utilizar o comando abaixo, onde o primeiro parâmetro é o nome do esquema e o segundo parâmetro é o código ibge da UF.

select copia_pessoa('nome_do_esquema','12')

Uma outra forma de utilização foi:

select copia_pessoa('18052013',ibge::varchar) from estados

onde na tabela estados se obtém os códigos do IBGE dos estado no campo ibge.

create table estados (ibge integer, uf char(2));

 insert into estados (ibge, uf) values (11,'RO');
 insert into estados (ibge, uf) values (12,'AC');
 insert into estados (ibge, uf) values (13,'AM');
 insert into estados (ibge, uf) values (14,'RR');
 insert into estados (ibge, uf) values (16,'AP');
 insert into estados (ibge, uf) values (17,'TO');
 insert into estados (ibge, uf) values (22,'PI');
 insert into estados (ibge, uf) values (23,'CE');
 insert into estados (ibge, uf) values (24,'RN');
 insert into estados (ibge, uf) values (25,'PB');
 insert into estados (ibge, uf) values (27,'AL');
 insert into estados (ibge, uf) values (28,'SE');
 insert into estados (ibge, uf) values (29,'BA');
 insert into estados (ibge, uf) values (31,'MG');
 insert into estados (ibge, uf) values (41,'PR');
 insert into estados (ibge, uf) values (43,'RS');
 insert into estados (ibge, uf) values (50,'MS');
 insert into estados (ibge, uf) values (51,'MT');
 insert into estados (ibge, uf) values (52,'GO');
 insert into estados (ibge, uf) values (53,'DF');
 insert into estados (ibge, uf) values (15,'PA');
 insert into estados (ibge, uf) values (21,'MA');
 insert into estados (ibge, uf) values (33,'RJ');
 insert into estados (ibge, uf) values (35,'SP');
 insert into estados (ibge, uf) values (42,'SC');
 insert into estados (ibge, uf) values (26,'PE');

 insert into estados (ibge, uf) values (32,'ES');

UPDATE com um comando SQL

Precisei atualizar a tabela pradin.s00031label os campos UF, MUNICIPIO, LAT, LON a partir da tabela mapas.shp_munic. Estava com preguiça em escrever 4 consultas SQL uma para cada campo. Com a expressão abaixo consegui fazer um comando SQL para atualizar 4 campos em um SGBD Postgresql 9.2.

update pradin.s00031label
set
  uf = b.uf,
  municipio = b.nome ,
  lat = b.latitude,
  lon = b.longitude
from
    mapas.shp_munic b
where  
    s00031label.ibge=b.ibge

comando UPDATE e DBLINK

O problema que tive é atualizar o campo "ibge7" da tabela "dados" com informações contidas em um outro banco de dados.

Utilizei o comando UPDATE e DBLINK

update sisfam.dados as l set ibge7 = (
select geocodigo from
(
select geocodigo, nome, ibge, sigla
from dblink
(
'dbname=database
hostaddr=enderecoip
user=usuario
password=senha
port=porta',

'
select m.geocodigo, m.nome, m.ibge, u.sigla
from mapas.shp_munic m , mapas.shp_uf u
where trunc(m.ibge/10000)=u.ibge'
)
as t1(geocodigo bigint, nome varchar, ibge bigint, sigla char(2))
)
 as f
 where  lower(trim(f.sigla))=lower(trim(l.uf))
and lower(trim(f.nome)) = lower(trim(l.municipio))
)

Versão do Centos Instalado

cat /etc/*release*

Arquitetura da Instalacao

 uname -m : This command will give you an output like x86_64 or i686. 

Informação sobre Processador e Memoria Linux

Para obter informações sobre o processador digite o comando:

$ cat /proc/cpuinfo

Para obter informações sobre a memória digite o comando:

$ cat /proc/meminfo

Consulta Recursiva

Tenho uma tabelas órgãos que necessito apresentar uma hierarquia.

select * from orgaos

codigo	codigo_pai	sigla
3	2	SAGI
1	null	PR
4	3	DGI
5	3	DFD
6	3	DM
7	3	DA
2	1	MDS
12	1	MTE
13	1	MRE
11	3	Gab/SAGI

Para representar este organograma

A consulta abaixo:


with recursive org (sigla, codigo, codigo_pai, path,deep,nome) as (
 select sigla, codigo, codigo_pai, array[codigo],1, nome
   from orgaos
   where codigo_pai is null
 union all
   select o1.sigla, o1.codigo, o1.codigo_pai,  path || o1.codigo ,o2.deep+1, o1.nome
   from orgaos o1, org o2
   where o2.codigo = o1.codigo_pai
 
)

 select * from org

me gerou

orgao	orgao_pai	sigla	path	deep
1		PR	{1}	1
2	1	MDS	{1,2}	2
3	2	SAGI	{1,2,3}	3
4	3	DGI	{1,2,3,4}	4
5	3	DFD	{1,2,3,5}	4
6	3	DM	{1,2,3,6}	4
7	3	DA	{1,2,3,7}	4
11	3	Gab/SAGI	{1,2,3,11}	4
12	1	MTE	{1,12}	2
13	1	MRE	{1,13}	2