Cálculo da Cobertura de Farmácias

From OpenStreetMap Wiki
Jump to: navigation, search

Objectivo

Com este cálculo pretende-se afirmar categoricamente qual a percentagem de farmácias georreferenciadas no OSM. Como o resultado será apresentado por freguesia, o mesmo transforma-se em pequenas tarefas que, depois de completadas, chegaremos aos 100% de cobertura.

Para tal, cria-se um base de dados de farmácias com todas as que estão registadas no site http://www.infarmed.pt. Essa base é comparada com o que já existe no OSM.

Na página Farmácias, existe uma forma diferente de importar os dados (através do portal da saúde[1]).

Nota: O site http://www.farmaciasportuguesas.pt NÃO tem todas as farmácias. Só algumas.

OSM

Para inserir a informação existente no OSM na base de dados, usa-se um backup do país inteiro, que pode ser obtido a partir de portugal.osm.bz2.

Antes de importar, é necessário ter a base de dados 'osm'.

osm2pgsql -H localhost -m -U geobox -W -d osm portugal.osm.bz2

Com este comando, são criadas as tabelas no PostGIS com a informação existente do OSM.

Farmácias no OSM

select amenity, name, st_astext(way)
from planet_osm_point
where amenity = 'pharmacy'

CAOP

Ver em Cálculo da Cobertura da Rede Viária como se importa a CAOP.

Farmácias existentes em Portugal

Para guardar as farmácias, criei a seguinte tabela:

CREATE TABLE anfarmacia
(
  id serial NOT NULL,
  nome character varying(120) NOT NULL,
  morada character varying(120),
  localidade character varying(120),
  distrito character varying(60),
  concelho character varying(60),
  freguesia character varying(60),
  telefone character varying(24),
  responsavel character varying(120),
  observacoes character varying(250),
  CONSTRAINT anfarmacia_pkey PRIMARY KEY (id)
);

Obter as farmácias

Navegando no site infarmed, descobre-se que a forma de chegar às farmácias de um determinado distrito é através de um formulário de pesquisa, onde basta indicar o distrito. Percebe-se que cada pesquisa gera um pedido da seguinte forma:

http://www.infarmed.pt/pt/licenciamento_inspeccao/farmacias/pesquisa/farmacia.php?valor=BRAGANCA&var=DISTRITO&submit=Pesquisar

Ou seja, é feita uma query à base de dados para o valor=AVEIRO, valor=BRAGA, etc. Isto é tudo o que precisamos saber. A partir daqui, em vez de um pedido, vamos fazer automaticamente todos os pedidos necessários, para todos os distrito.

Note-se que no site infarmed os distritos devem ser introduzidos sem acentos. Deve ter sido feito por americanos. Ou com software proprietário. E, já agora, o HTML que resultam das pesquisas é intratável. Nem sequer a tag HTML é fechada (nem a HEAD, BODY, etc). Por isso, foi necessário recorrer a uma script auxiliar limpa.py só para aproveitar o bocadinho de HTML onde vem o resultado das pesquisas.

A seguinte script em Python gera.py gera uma outra script sacafarmacias.sh com todos os pedidos necessários.

#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import urllib
distritos = [ "AVEIRO",
"BEJA",
"BRAGA",
"BRAGANCA",
"CASTELO BRANCO",
"COIMBRA",
"EVORA",
"FARO",
"GUARDA",
"LEIRIA",
"LISBOA",
"PORTALEGRE",
"PORTO",
"SANTAREM",
"SETUBAL",
"VIANA DO CASTELO",
"VILA REAL",
"VISEU" ]
script = open('sacafarmacias.sh', 'w')
script.write('#!/bin/bash\n\n')
for distrito in distritos:
  nome = 'farmacias_%s.html' % distrito
  params = urllib.urlencode({'valor': distrito, 'var': 'DISTRITO', 'submit': 'Pesquisar'})
  curl = "curl \"http://www.infarmed.pt/pt/licenciamento_inspeccao/farmacias/pesquisa/farmacia.php?%s\" | ./limpa.py > \"%s\"" % (params, nome)
  script.write(curl + '\n')
script.close

A script gerada sacafarmacias.sh gera exactamente 18 pedidos, um para cada distrito do continente, e o resultado são 18 documentos HTML, onde aparecem lá pelo meio as farmácias desse distrito. Como se pode ver, depois de obtida a página com o curl, é passada pelo filtro limpa.py, que se apresenta de seguida, antes de ser gerado o código sql.

#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import sys
import re
ok = False;
print '<?xml version="1.0" encoding="ISO-8859-1"?>'
for line in sys.stdin.readlines():
	if re.search('</TABLE><TABLE', line):
		line = re.sub('</TABLE>','', line)
		ok = True
	if ok:
		print line,
		if re.search('</TABLE>', line):
			ok = False

O passo seguinte consiste em fazer o parsing de todos esses (extratos de) documentos HTML, de modo a gerar o sql para inserir na base de dados. Isso pode ser feito, com alguma simplicidade utilizando XSLT. O documento XSLT utilizado farmacias2sql.xsl é o seguinte:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xd="http://www.oxygenxml.com/ns/doc/xsl" exclude-result-prefixes="xd" version="1.0">
    <xsl:output version="1.0" encoding="UTF-8" method="text" indent="no" omit-xml-declaration="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:template match="/">
        <xsl:apply-templates/>
    </xsl:template>
    <xsl:template match="TD[@BGCOLOR='#008080']"/>
    <xsl:template match="TD[@BGCOLOR='#C4E8A4']|TD[@BGCOLOR='#ACDFB9']">
        <xsl:text>'</xsl:text>
        <xsl:apply-templates/>
        <xsl:text>'</xsl:text>
        <xsl:if test="position()!=last()">
            <xsl:text>, </xsl:text>
        </xsl:if>       
    </xsl:template>          
    <xsl:template match="TABLE/TR[1]"/>             
    <xsl:template match="TR">
        <xsl:text>insert into anfarmacia (nome, morada, localidade, freguesia, distrito, responsavel, concelho) values (</xsl:text>
        <xsl:apply-templates/>
        <xsl:text>);
</xsl:text>        
    </xsl:template>        
</xsl:stylesheet>

Esta passagem pelo xmlstarlet é o último passo antes de obtermos o anfarmacias.sql. O ficheiro gerado, no entanto, precisa de pequenas alterações cirúrgicas, para proteger a utilização de ' dentro de strings, e de \.

sed -i "s/\([ A-Z]\)'\([A-Z ]\)/\1''\2/g" anfarmacias.sql
sed -i 's|\\|/|g' anfarmacias.sql

Finalmente, pode-se inserir as farmácias na tabela inicialmente criada.

export PGCLIENTENCODING=LATIN1
psql -U geobox -d osm -h localhost < anfarmacias.sql 2> erros.log

Se tudo correu bem, passam a existir 2805 linhas na tabela. Mas também tive más notícias. Além dos nomes dos distritos e concelhos não estarem acentuados na base de dados do infarmed, há nomes de concelhos incorrectamente escritos. Por isso:

update anfarmacia set concelho = 'CONDEIXA-A-NOVA' where concelho = 'CONDEIXA A NOVA';
update anfarmacia set concelho = 'IDANHA-A-NOVA' where concelho = 'IDANHA A NOVA';
update anfarmacia set concelho = 'PROENCA-A-NOVA' where concelho = 'PROENÇA-A-NOVA';
update anfarmacia set concelho = 'ALBERGARIA-A-VELHA' where concelho = 'ALBERGARIA A VELHA';
update anfarmacia set concelho = 'MONTEMOR-O-NOVO' where concelho = 'MONTEMOR O NOVO';
update anfarmacia set concelho = 'MONTEMOR-O-VELHO' where concelho = 'MONTEMOR O VELHO';

Cálculos

Calcular quantas farmácias que REALMENTE HÁ em cada concelho. Note-se que não há farmácias em todos os concelhos do continente. Só em 259 dos 278 existentes.

select distrito, concelho, count(*)
from anfarmacia
group by distrito, concelho
order by count(*) desc
Distrito Concelho Nº de Farmácias OFICIAL
Lisboa Lisboa 286
Porto Porto 113
Lisboa Sintra 66
... ... ...
Portalegre Monforte 1

Não há concelhos sem farmácias.

Cálculo de quantas farmácias há no OSM para cada concelho:

select distrito_, municipio, count(*)
from planet_osm_point, cont_aad_caop2010
where amenity = 'pharmacy'
and st_contains(wkb_geometry, way)
group by distrito_, municipio
order by count(*) desc
Distrito Concelho Nº de Farmácias no OSM
COIMBRA COIMBRA 21
LISBOA LISBOA 15
LISBOA OEIRAS 13
PORTO VILANOVADEGAIA 11
PORTO SANTOTIRSO 10
PORTO PORTO 9
LISBOA ODIVELAS 8
SETÚBAL ALMADA 8
PORTO MATOSINHOS 7
LISBOA LOURES 6
LISBOA AMADORA 6
LISBOA SINTRA 5
AVEIRO ÁGUEDA 5
BRAGA BRAGA 5
... ... ...

Resultado Final

Antes de mostrar a comparação final, convém por a informação na tabela cont_aad_caop2010_concelho.

Por a informação das farmácias realmente existentes. Atenção à utilização do translate, já que os concelhos do infarmed não estão acentuados.

update cont_aad_caop2010_concelho
set totalreal = (select count(*)
	from anfarmacia
	where upper(concelho) = upper(translate(municipio, 'ÁÀÂÃÉÊÍÓÔÕÜÚÇ', 'AAAAEEIOOOUUC'))
)

Por a informação das farmácias existentes no OSM.

update cont_aad_caop2010_concelho
set totalosm = (
  select count(*)
  from planet_osm_point
  where amenity = 'pharmacy' and st_contains(wkb_geometry, way)
)

Testar as queries anteriores:

select sum(totalreal), sum(totalosm)
from cont_aad_caop2010_concelho
Total infarmed Total OSM (15-02-2011)
2805 247

Calcular a percentagem:

update cont_aad_caop2010_concelho
set percentagem = CASE WHEN totalreal > 0 THEN totalosm / totalreal ELSE 1 END

Na tabela seguinte mostram-se as diferenças, concelho a concelho, entre o que existe e o que está registado no OSM. Dá-se uma indicação da percentagem (entre 0.0 e 1.0) do trabalho já efectuado. A tabela está ordenada por número de farmácias no infarmed, por ordem decrescente.

select municipio, totalreal, totalosm, to_char(percentagem, '0D99')
from cont_aad_caop2010_concelho
order by totalreal desc
Concelho Nº de Farmácias no infarmed Nº de Farmácias no OSM Percentagem (0.0 e 1.0)
LISBOA 286 15 0,05
PORTO 113 9 0,08
SINTRA 66 5 0,08
VILA NOVA DE GAIA 62 11 0,18
COIMBRA 49 21 0,43
OEIRAS 47 13 0,28
LOURES 44 6 0,14
CASCAIS 43 5 0,12
ALMADA 42 8 0,19
AMADORA 41 6 0,15
MATOSINHOS 36 7 0,19
BRAGA 36 5 0,14
SEIXAL 33 1 0,03
GUIMARÃES 33 1 0,03
ODIVELAS 33 8 0,24
GONDOMAR 32 2 0,06
LEIRIA 31 5 0,16
SETÚBAL 30 0 0,00
VILA FRANCA DE XIRA 27 0 0,00
SANTA MARIA DA FEIRA 27 3 0,11
VILA NOVA DE FAMALICÃO 27 0 0,00
BARCELOS 27 3 0,11
TORRES VEDRAS 25 2 0,08
MAIA 24 4 0,17
FIGUEIRA DA FOZ 23 0 0,00
SANTARÉM 23 5 0,22
VISEU 23 4 0,17
VIANA DO CASTELO 23 0 0,00
BARREIRO 22 1 0,05
AVEIRO 21 2 0,10
POMBAL 20 1 0,05
ÉVORA 18 0 0,00
FARO 18 0 0,00
COVILHÃ 18 1 0,06
VALONGO 18 5 0,28
CASTELO BRANCO 17 0 0,00
VILA DO CONDE 17 1 0,06
OLIVEIRA DE AZEMÉIS 16 0 0,00
BRAGANÇA 16 0 0,00
SANTO TIRSO 16 10 0,62
PENAFIEL 16 0 0,00
CALDAS DA RAINHA 15 2 0,13
OVAR 15 0 0,00
OURÉM 15 3 0,20
ALCOBAÇA 15 1 0,07
PÓVOA DE VARZIM 15 0 0,00
CANTANHEDE 15 2 0,13
PAREDES 15 2 0,13
MIRANDELA 14 0 0,00
MOITA 14 0 0,00
ANADIA 14 0 0,00
LOULÉ 14 0 0,00
PALMELA 14 0 0,00
VILA REAL 13 0 0,00
ÁGUEDA 13 5 0,38
AMARANTE 13 0 0,00
ABRANTES 13 0 0,00
MAFRA 13 3 0,23
ALENQUER 12 0 0,00
FELGUEIRAS 12 1 0,08
TOMAR 12 0 0,00
PORTIMÃO 12 2 0,17
SILVES 12 3 0,25
CHAVES 12 0 0,00
TORRES NOVAS 11 0 0,00
VILA VERDE 11 3 0,27
MONTIJO 11 1 0,09
SANTIAGO DO CACÉM 11 0 0,00
GUARDA 11 0 0,00
TONDELA 11 0 0,00
SEIA 10 0 0,00
MONTEMOR-O-VELHO 10 3 0,30
MARCO DE CANAVESES 10 0 0,00
ODEMIRA 10 0 0,00
TAVIRA 10 4 0,40
PONTE DE LIMA 9 2 0,22
PAÇOS DE FERREIRA 9 0 0,00
OLHÃO 9 0 0,00
PORTALEGRE 9 0 0,00
ESPINHO 9 0 0,00
BEJA 9 3 0,33
FAFE 9 0 0,00
ESPOSENDE 8 3 0,38
ALBUFEIRA 8 1 0,12
SOURE 8 0 0,00
FUNDÃO 8 0 0,00
TROFA 8 0 0,00
LOUSADA 8 0 0,00
CARTAXO 8 0 0,00
PESO DA RÉGUA 8 0 0,00
ALBERGARIA-A-VELHA 8 0 0,00
MACEDO DE CAVALEIROS 8 0 0,00
MOURA 8 0 0,00
MARINHA GRANDE 8 0 0,00
LAGOS 8 2 0,25
SESIMBRA 8 0 0,00
ÍLHAVO 8 0 0,00
PORTO DE MÓS 8 1 0,12
SÃO PEDRO DO SUL 8 0 0,00
OLIVEIRA DO HOSPITAL 7 0 0,00
MEALHADA 7 3 0,43
VALPAÇOS 7 0 0,00
CHAMUSCA 7 0 0,00
AROUCA 7 0 0,00
ELVAS 7 0 0,00
LAMEGO 7 0 0,00
OLIVEIRA DO BAIRRO 7 0 0,00
PENICHE 7 0 0,00
VAGOS 7 0 0,00
LAGOA 7 1 0,14
CORUCHE 7 0 0,00
AZAMBUJA 6 0 0,00
MANGUALDE 6 0 0,00
TORRE DE MONCORVO 6 0 0,00
MONTEMOR-O-NOVO 6 0 0,00
ARCOS DE VALDEVEZ 6 0 0,00
CINFÃES 6 0 0,00
LOURINHÃ 6 0 0,00
MOGADOURO 6 0 0,00
MONÇÃO 6 0 0,00
VINHAIS 6 0 0,00
ALIJÓ 6 0 0,00
MIRA 6 0 0,00
SALVATERRA DE MAGOS 6 0 0,00
NAZARÉ 6 2 0,33
ALMEIRIM 6 0 0,00
VALE DE CAMBRA 6 3 0,50
ESTARREJA 6 0 0,00
NELAS 6 0 0,00
VIMIOSO 6 0 0,00
BENAVENTE 6 0 0,00
ESTREMOZ 5 0 0,00
GOUVEIA 5 0 0,00
SEVER DO VOUGA 5 0 0,00
CASTRO DAIRE 5 2 0,40
PÓVOA DE LANHOSO 5 0 0,00
FERREIRA DO ALENTEJO 5 0 0,00
CADAVAL 5 0 0,00
SÃO JOÃO DA MADEIRA 5 1 0,20
CAMINHA 5 0 0,00
GRÂNDOLA 5 0 0,00
CELORICO DE BASTO 5 0 0,00
ANSIÃO 5 0 0,00
VILA REAL DE SANTO ANTÓNIO 5 1 0,20
VIZELA 5 0 0,00
PENACOVA 5 0 0,00
PINHEL 5 0 0,00
SABUGAL 5 0 0,00
PONTE DE SOR 5 0 0,00
RIO MAIOR 5 1 0,20
CONDEIXA-A-NOVA 5 1 0,20
SERPA 5 0 0,00
ALCANENA 5 0 0,00
BAIÃO 5 0 0,00
ALFÂNDEGA DA FÉ 4 0 0,00
MAÇÃO 4 0 0,00
AMARES 4 3 0,75
VOUZELA 4 0 0,00
NISA 4 0 0,00
CASTELO DE PAIVA 4 0 0,00
ENTRONCAMENTO 4 0 0,00
REGUENGOS DE MONSARAZ 4 0 0,00
VILA POUCA DE AGUIAR 4 0 0,00
SERTÃ 4 0 0,00
VILA VIÇOSA 4 0 0,00
FIGUEIRÓ DOS VINHOS 4 0 0,00
VILA FLOR 4 2 0,50
CABECEIRAS DE BASTO 4 0 0,00
IDANHA-A-NOVA 4 0 0,00
MIRANDA DO DOURO 4 0 0,00
VIEIRA DO MINHO 4 0 0,00
RESENDE 4 0 0,00
MONTALEGRE 4 0 0,00
CARRAZEDA DE ANSIÃES 4 0 0,00
ARGANIL 4 0 0,00
SANTA COMBA DÃO 4 0 0,00
TÁBUA 4 0 0,00
ALCÁCER DO SAL 4 0 0,00
TRANCOSO 4 0 0,00
PONTE DA BARCA 3 0 0,00
CARREGAL DO SAL 3 0 0,00
MURTOSA 3 0 0,00
FIGUEIRA DE CASTELO RODRIGO 3 0 0,00
SOBRAL DE MONTE AGRAÇO 3 0 0,00
CELORICO DA BEIRA 3 0 0,00
GAVIÃO 3 0 0,00
VILA NOVA DE CERVEIRA 3 0 0,00
LOUSÃ 3 2 0,67
GOLEGÃ 3 0 0,00
VENDAS NOVAS 3 0 0,00
SINES 3 2 0,67
ALPIARÇA 3 0 0,00
VILA NOVA DA BARQUINHA 3 0 0,00
MORTÁGUA 3 0 0,00
ALVAIÁZERE 3 0 0,00
MIRANDA DO CORVO 3 3 1,00
SÁTÃO 3 0 0,00
ALJUSTREL 3 0 0,00
ÓBIDOS 3 0 0,00
GÓIS 3 0 0,00
MOIMENTA DA BEIRA 3 0 0,00
MESÃO FRIO 3 0 0,00
VALENÇA 3 0 0,00
MELGAÇO 3 0 0,00
BATALHA 3 0 0,00
OLEIROS 3 0 0,00
VIANA DO ALENTEJO 3 0 0,00
BOMBARRAL 3 0 0,00
CRATO 3 0 0,00
MORA 3 0 0,00
TERRAS DE BOURO 3 0 0,00
ARRAIOLOS 3 0 0,00
VILA NOVA DE FOZ CÔA 3 0 0,00
OLIVEIRA DE FRADES 3 0 0,00
PAREDES DE COURA 3 0 0,00
FERREIRA DO ZÊZERE 3 0 0,00
ALCOCHETE 3 0 0,00
REDONDO 3 0 0,00
SÃO JOÃO DA PESQUEIRA 2 0 0,00
ALMODÔVAR 2 0 0,00
ALANDROAL 2 0 0,00
ALTER DO CHÃO 2 0 0,00
BELMONTE 2 0 0,00
VIDIGUEIRA 2 0 0,00
PAMPILHOSA DA SERRA 2 0 0,00
PORTEL 2 0 0,00
ALMEIDA 2 0 0,00
MONCHIQUE 2 0 0,00
CASTRO VERDE 2 0 0,00
RIBEIRA DE PENA 2 0 0,00
SABROSA 2 0 0,00
ARMAMAR 2 0 0,00
FORNOS DE ALGODRES 2 0 0,00
MANTEIGAS 2 0 0,00
CASTRO MARIM 2 0 0,00
CONSTÂNCIA 2 0 0,00
FRONTEIRA 2 0 0,00
BOTICAS 2 0 0,00
MÉRTOLA 2 1 0,50
FREIXO DE ESPADA À CINTA 2 0 0,00
PENAMACOR 2 0 0,00
PENELA 2 0 0,00
MURÇA 2 0 0,00
ARRONCHES 2 1 0,50
OURIQUE 2 0 0,00
ARRUDA DOS VINHOS 2 0 0,00
MONDIM DE BASTO 2 0 0,00
PROENÇA-A-NOVA 2 0 0,00
SOUSEL 2 0 0,00
TAROUCA 2 0 0,00
VILA NOVA DE POIARES 2 0 0,00
BORBA 2 0 0,00
CAMPO MAIOR 2 0 0,00
CASTELO DE VIDE 2 0 0,00
PENALVA DO CASTELO 2 0 0,00
ALJEZUR 2 0 0,00
SÃO BRÁS DE ALPORTEL 2 0 0,00
VILA DO BISPO 2 1 0,50
AGUIAR DA BEIRA 2 0 0,00
SANTA MARTA DE PENAGUIÃO 2 0 0,00
SERNANCELHE 2 0 0,00
VILA DE REI 1 0 0,00
TABUAÇO 1 0 0,00
VILA VELHA DE RÓDÃO 1 0 0,00
AVIS 1 0 0,00
ALVITO 1 0 0,00
MOURÃO 1 0 0,00
CASTANHEIRA DE PÊRA 1 0 0,00
ALCOUTIM 1 0 0,00
CUBA 1 0 0,00
BARRANCOS 1 0 0,00
VILA NOVA DE PAIVA 1 0 0,00
SARDOAL 1 0 0,00
MONFORTE 1 0 0,00
MARVÃO 1 0 0,00
MÊDA 1 0 0,00
PEDRÓGÃO GRANDE 1 0 0,00
PENEDONO 1 0 0,00

Este tabela foi gerada em 15 de Fevereiro de 2011.

A tabela Postgresql cont_aad_caop2010_concelho que usei, exportei-a para shapefile, em PT-TM06/ETRS89, caso queiram fazer mapas temáticos ou outra brincadeira qualquer. A tabela com as farmácias do infarmed anfarmacia exportei-a com o pg_dump. Atenção que os concelhos e distritos desta tabela estão sem acentos. Usar um translate, se for necessário joins com outras tabelas.

ogr2ogr -s_srs EPSG:900913 -s_srs "+init=pt:pttm06" -f "ESRI Shapefile" anfarmaciasvsosm.shp PG:"host=localhost user=geobox dbname=osm password=geobox" "cont_aad_caop2010_concelho"