Pt:Análise da rede de equipamentos saúde
Introdução
Nesta página pretende-se apresentar um conjunto de queries (algumas podem ser vistas em [1])que permitam realizar uma análise à rede existente de equipamentos de saúde.
Para tal, procedeu-se à importação da base de dados do OSM (este processo pode ser visto em [2]) para o programa pgAdminIII, onde foram executadas as queries.
Poderá ser interessante antes de ler esta página proceder à leitura da página relativa à quantificação dos equipamentos de saúde, disponível em Equipamentos de Saúde.
Com este conjunto de queries é possível comparar, cruzar ou obter informação relativa aos equipamentos de saúde, bem como relacionar-los com acessibilidades e transportes.
Detecção de dados redundantes
Como se sabe os hospitais/centros de saúde podem ser marcados quer como pontos quer como polignos. No entanto, podem existir casos, onde por engano o mesmo hospital esteja definido como ponto e poligno, é então interessante saber quais são esses hospitais, executando para tal a seguinte query :
SELECT p.name as namepoint,st_astext(p.way) as point ,po.name as namepolygon, st_astext(po.way) as ploygon
FROM planet_osm_point AS p, planet_osm_polygon AS po
WHERE p.amenity='hospital' AND po.amenity='hospital' AND p.name=po.name and intersects(p.way,po.way);
Na tabela que se segue pode-se ver um excerto da tabela resultante desta query:
| namepoint | point | namepolygon | polygon |
|---|---|---|---|
| Casa de Saúde do Montepio Rainha D. Leonor | POINT(-1016798.18449852 4780046.27343387) | Casa de Saúde do Montepio Rainha D. Leonor | POLYGON((-1016874.21 4780046.37,-1016871.39 4780069.22,-1016841.36 4780065.52,-1016790.68 4780059.25,-1016794.81 4780036.72,-1016826.01 4780038.36,-1016874.21 4780046.37)) |
| Hospital da Luz | POINT(-1023315.32842727 4686789.01011269) | Hospital da Luz | POLYGON((-1023523.13 4686785.54,-1023452.18 4686956.52,-1023404.28 4686968.56,-1023201.87 4686832.12,-1023269.94 4686691.19,-1023394.15 4686775.99,-1023523.13 4686785.54)) |
Obtenção de dados
Para algumas das queries apresentadas de seguida é necessário a importação de alguns dados externos:
A importação de dados CAOP e do INE pode ser vista em Cálculo da Cobertura da Rede Viária.
Para importação dos dados INE, para o cruzamento de dados, utilizou-se dois tipo de dados:
- População por Município
- Acidentes rodoviários por Município
Estes dados são obtidos no site oficial do INE, e podem exportados em ficheiro .csv. A partir deste ficheiro, os dados são assim importados.
Cruzamento de dados
Relacionamento com as farmácias
Obter conjunto de farmácias perto de um determinado hospital
Nesta parte pode ser interessante ler a página relativa a Farmácias. Uma aplicação no telemóvel que diga às pessoas quais as farmácias mais próximas do hospital em que se encontram, poderá ser muito útil. Neste caso, assumiu-se como proximidade 5km e apenas foram consideradas os hospitais/centro de saúde marcados como pontos.
select name
from planet_osm_point
where amenity='pharmacy' and st_distance((select st_astext(way)
from planet_osm_point
where amenity='hospital' and name='Hospital de S. Marcos'), st_astext(way) )< 5000;
Ordenar as farmácias mais próximas por ordem crescente de distância
Para calcular qual a farmácia efectivamente mais próxima, poderá ser executada a seguinte query:
select name,st_distance((select st_astext(way)
from planet_osm_point
where amenity='hospital' and name='Hospital de S. Marcos'), st_astext(way) ) as k
from planet_osm_point
where amenity='pharmacy' and st_distance((select st_astext(way)
from planet_osm_point
where amenity='hospital' and name='Hospital de S. Marcos'), st_astext(way) ) < 5000
order by k;
Calcular a farmácia com menor distância para um dado hospital
Dado um hospital, encontrar a farmácia que é mais próxima.
select pt.name as farmacia,st_distance(st_astext(ho.way),st_astext(pt.way))as distancia
from planet_osm_point as pt, hospital as ho
where amenity='pharmacy' and ho.name='Hospital de S. Marcos' and st_distance(st_astext(ho.way),st_astext(pt.way))=
(select min(st_distance(st_astext(h.way),st_astext(p.way)))
from planet_osm_point as p, hospital as h
where amenity='pharmacy' and h.name='Hospital de S. Marcos');
Neste caso utilizou-se o hospital S. Marcos sendo que foi devolvida a seguinte tabela:
Obtendo-se desta forma a seguinte tabela:
| farmacia | distancia |
|---|---|
| Farmácia Cristal | 170.458624188183 |
É importante salientar que estas queries podem também ser utilizadas, para hospitais definidos como poligonos, bastando para isso alterar o nome da tabela.
Relacionamento com municípios
Por forma, a simplificar as operações, ou seja, não ser necessário aplicar as consultas quer na tabela planet_osm_point e planet_osm_polygon, achou-se relevante criar uma tabela hospital que contêm os campos: nome, id e localização de todos os hospitais, quer marcados como pontos quer como polignos.
create table hospital as select osm_id,name,way from planet_osm_polygon where amenity='hospital';
insert into hospital select osm_id,name,way from planet_osm_point where amenity='hospital';
A tabela importada do CAOP #Obtenção de dados designa-se por caop.
Obter os hospitais que pertencem ao município de Braga
select name
from hospital, caop
where st_contains (st_transform(the_geom, 900913), way) and municipio ilike 'braga';
Mudando na query o atributo do município podemos obter hospitais de outros municípios.
Verificar em que municípios existem hospitais que não têm nome, e quais os utilizadores que os registaram
É importante para que se possam corrigir este tipo de anomalias no open street maps, e pode ser efectuado através da query:
select municipio, name, way, osm_user
from planet_osm_point, caop
where amenity='hospital' and
st_contains (st_transform(the_geom, 900913), way)
and name is NULL;
Relacionamento com transportes públicos
Calcular os hospitais que pertençam a municípios onde exista estação de comboio
Esta operação é importante, pois hoje em dia muitas pessoas utilizam como meio de transporte o comboio, sendo interessante, por exemplo, para uma pessoa que vá visitar um familiar a hum hospital possa saber se existe forma de chegar lá de comboio ou não. Para tal, foi criada a tabela hm que com os municípios que contêm hospitais.
create table hm
as
select distinct municipio
from hospital, caop
where st_contains (st_transform(the_geom, 900913), way);
select p.name,caop.municipio,hm.name
from planet_osm_point as p,caop,hm
where railway='station' and (st_contains (st_transform(the_geom, 900913), p.way)) and caop.municipio=hm.municipio
order by hm.municipio;
Para cada hospital obter as paragens de transportes públicos que estão a menos de 1km
As deslocações ao hospital nem sempre são realizadas em ambulâncias, nem em carro particular. Grande parte das pessoas utilizam os transportes públicos por isso é relevante saber quais os hospitais que possuem paragens a menos de mil metros e como estas se chamam.
select h.name as hospital,po.name as paragem, (st_distance(st_astext(h.way),st_astext(po.way)) )as distancia
from hospital as h, planet_osm_point as po
where po.highway='bus_stop' and st_distance(st_astext(h.way), st_astext(po.way) )< 1000;
Contar o número de paragens de transportes públicos perto de cada hospital
Quantificar o número de paragens (marcadas como point) para cada hospital, ordenando por ordem descendente, de forma a verificar qual o hospital que está melhor servido.
select h.name as hospital,count(*) as num
from hospital as h, planet_osm_point as po
where (po.highway='bus_stop' and st_distance(st_astext(h.way), st_astext(po.way)) < 1000) group by h.name order by num desc;
Em cada município, contar o número de paragens de transporte público perto do hospital
select h.name as hospital,municipio, count(*) as num
from hospital as h, planet_osm_point as po, caop
where (po.highway='bus_stop' and st_distance(st_astext(h.way), st_astext(po.way) )< 1000 and st_contains (st_transform(the_geom, 900913), po.way))
group by h.name,municipio order by num desc;
Verificar quais as paragens de taxi que estão a menos de 500m de um determinado hospital
Neste exemplo utilizou-se o Centro de Saúde de Ribeirão, sendo que a query pode ser aplicada a outro hospital/centro mudando apenas este atributo.
select name as paragem,st_distance((select distinct st_astext(h.way)
from hospital as h
where h.name='Centro de Saúde de Ribeirão'), st_astext(po.way) ) as distancia
from planet_osm_polygon as po
where amenity='taxi' and st_distance((select distinct st_astext(h.way)
from hospital as h
where h.name='Centro de Saúde de Ribeirão'), st_astext(po.way) )< 500
order by distancia;
Obtendo-se desta forma a seguinte tabela:
| paragem | distancia |
|---|---|
| ribeirão | 181.574403349534 |
Relacionamento com a população
Partindo da densidade populacional de cada município, é possível verificar se um concelho está muito editado no OSM. Pois é natural que que um município com uma grande densidade populacional tenha uma grande numero de densidade populacional.
Usando os dados no ficheiro .csv do INE, efectua-se a importação após a criação da tabela:
Create table populacaomunicipio (‘municipio’ text, ‘populacao’ integer);
copy populacaomunicipio from '/home/geobox/distritos.csv' delimiter ';';
Densidade Populacional
Para saber qual a densidade populacional por município é necessário dividir a população pela área: Nota: o facto de se dividir a área ha ([5]) por 100, é para manter as unidades SI de km2 ([6]).
create table densidademunicipio as
(select p.municipio, (populacao/(sum(area_t_ha)/100)) as densidade
from caop as c, populacaomunicipio as p
where c.municipio=p.municipio
group by p.municipio,populacao
order by p.municipio);
Que se obtem a seguinte tabela:
| Município | Densidade |
|---|---|
| ABRANTES | 53.7099475339472 |
| AGUIAR DA BEIRA | 26.1503847835423 |
| ALANDROAL | 10.8204108476093 |
| ... | ... |
| VOUZELA | 52.2523871354117 |
Para comparação, utilizou-se então uma query que mostra o número de pessoas para cada equipamento de saúde, num determinado município (braga)
select p.municipio, populacao/count (h.name) as "hab/hospital"
from hospital as h, populacaomunicipio as p, caop
where st_contains (st_transform(the_geom, 900913), h.way) and p.municipio=caop.municipio and p.municipio ilike 'braga'
group by p.municipio, p.populacao;
Com estes dados é possível verificar a cobertura de equipamentos de saúde em cada município.
Relacionamento com acidentes
No site do INE, é possível retirar dados sobre os acidentes automóveis por município. Com estes dados é possível identificar quais os municípios onde existe um maior número de emergências.
Da mesma forma que a anterior, utiliza-se a query de importação de dados em formato .csv:
create table acidentes ("municipio" text, "numero" integer);
copy acidentes from '/home/geobox/acidentes.csv' delimiter ';';
Assim, é possivel o cruzamento de dados:
Comparar o número de acidentes com o número de hospitais num determinado município (Braga)
Pode ser interessante para saber se o município em questão tem infraestruturas hospitalares que permitam acolher um elevado número de emergências.
select a.municipio, count(h.name), a.numero
from hospital as h, acidentes as a, caop as c
where c.municipio=a.municipio and st_contains (st_transform(the_geom, 900913), h.way) and a.municipio='BRAGA'
group by a.municipio, a.numero
Acessibilidades
Encontrar o hospital mais próximo de um determinado local
Pode ser interessante para uma ambulância saber para onde se deve deslocar, ou seja qual é o de mais rápido acesso. Utilizando o Google maps obteve-se as coordenadas da universidade do Minho que foram convertidas para se utilizar o mesmo sistema de coordenadas.
select ho.name
from hospital as ho
where st_distance(st_transform(ho.way, 3763), ST_GeomFromText('POINT(-21952.171037322 211129.157118981)',3763))=
(select min(st_distance(st_transform(h.way, 3763), ST_GeomFromText('POINT(-21952.171037322 211129.157118981)',3763)))
from hospital as h)
Saber a que município pertence determinado hospital
Esta query revela-se importante, para as pessoas se situarem quando têm de se deslocar a determinado hospital. Para tal, apenas tem de se substituir na query o nome do hospital que se pretende. Neste caso, é utilizado o hospital de S. Marcos.
Select municipio
from caop, hospital as h
where h.name=’Hospital de S. Marcos’ and st_contains (st_transform(the_geom, 900913), h.way);
Verificar quais os parques de estacionamento que estão a menos de 1km de um determinado hospital
Uma questão crítica nas acessibilidades de um hospital é se tem ou não, perto de si, um parque de estacionamento. Para verificar esse facto pode ser utilizada a seguinte query.
select name,st_distance((select distinct st_astext(h.way)
from hospital as h
where h.name='Hospital Psiquiátrico de S. Marcos'), st_astext(po.way) ) as k
from planet_osm_polygon as po
where amenity='parking' and st_distance((select distinct st_astext(h.way)
from hospital as h
where h.name='Hospital Psiquiátrico de S. Marcos'), st_astext(po.way) ) < 1000
order by k;
Comparação com dados reais
No separador "serviços" do site do Portal da saúde [7]é possível realizar algumas consultas sobre hospitais, extensões de saúde e centros de saúde existentes em Portugal. No final, esses dados podem ser exportados para um ficheiro .csv. Achou-se relevante obter a informação sobre os hospitais existentes em Portugal e criar a tabela hospitais oficiais.
Para tal foi necessário um pré-processamento do ficheiro csv, de forma a, substituir as letras Latin-1 por UTF-8, ou seja retirar Á, Ê, Õ, Ç, etc. As coordenadas também foram alteradas de modo que após a remoção dos outros campos, e ter guardado em ficheiro .csv, a primeira linha do ficheiro apresentava-se da seguinte forma:
HOSPITAL DR. JOSE MARIA GRANDE - PORTALEGRE (UNIDADE LOCAL DE SAUDE DO NORTE ALENTEJANO. EPE);AV. DE SANTO ANTONIO;POINT(-7.426385 39.30031)
Para importação do ficheiro csv, utilizou-se a query:
CREATE TABLE hospiataisreal ("nome" text, "morada" text, "way" geometry);
copy hospitaisreal (nome,morada,way) from '/home/geobox/hospitais.csv' delimiter ';'
Para verificar então, quantos hospitais oficiais existem num município pode-se proceder à seguinte query:(Neste caso, procurar os hospitais oficiais do concelho do Porto)
SELECT municipio, count(h.nome) AS "hospitais oficiais"
FROM caop, hospitalreal AS h
WHERE municipio='PORTO' and st_contains (st_transform(the_geom, 900913),ST_transform(st_geometryfromtext(h.way,4326),900913))
GROUP BY municipio
Obtendo-se desta forma a seguinte tabela:
| municipio | hospitais oficiais |
|---|---|
| PORTO | 6 |
Para a informação sobre centros de saúde efectuaram-se as mesmas alterações mencionadas acima, e procedeu-se à importação das mesmas para a base de dados utilizando a seguinte query:
CREATE TABLE centrosaude ("nome" text, "morada" text, "way" geometry);
copy centrosaude (nome,morada,way) from '/home/geobox/centrosaude.csv' delimiter ';'
Para verificar quantos centros de saúde existem num município, neste caso no do Porto, utiliza-se a query:
SELECT municipio, count(c.nome) AS "centros saúde oficiais"
FROM caop, centrosaude AS c
WHERE municipio='PORTO' and st_contains (st_transform(the_geom, 900913),ST_transform(st_geometryfromtext(c.way,4326),900913))
GROUP BY municipio
Obtendo-se a seguinte tabela:
| municipio | centros de saúde oficiais |
|---|---|
| PORTO | 9 |
Relativamente à informação sobre extensões de saúde efectuaram-se as mesmas alterações mencionadas acima (processamento do csv), e procedeu-se à importação das mesmas para a base de dados utilizando a seguinte query:
CREATE TABLE extensoessaude ("nome" text, "morada" text, "way" geometry);
copy extensoessaude (nome,morada,way) from '/home/geobox/extensoessaude.csv' delimiter ';'
Para quantificar o número de extensões num determinado município efectua-se a seguinte query:
SELECT municipio, count(e.nome) AS "extensões oficiais"
FROM caop, extensoessaude AS e
WHERE municipio='PORTO' AND st_contains (st_transform(the_geom, 900913),ST_transform(st_geometryfromtext(e.way,4326),900913))
GROUP BY municipio
Resultando a seguinte tabela:
| municipio | extensões oficiais |
|---|---|
| PORTO | 3 |
Como na base de dados OSM, não existe ainda forma de isolar as diferentes unidades de saúde (são marcadas todas com a tag amenity=hospital), para proceder à comparação tem de se juntar os resultados destas três tabelas. Uma das formas possíveis é criar uma nova tabela a uhporto que contém as distintas unidades hospitalares do Porto. Para tal executa-se o seguinte conjunto de queries:
Create table uhporto as
sELECT municipio, h.nome
FROM caop, hospitalreal AS h
WHERE municipio='PORTO' AND st_contains (st_transform(the_geom, 900913),ST_transform(st_geometryfromtext(h.way,4326),900913))
GROUP BY municipio, h.nome
INSERT INTO uhporto SELECT municipio, c.nome
FROM caop, centrosaude AS c
WHERE municipio='PORTO' AND st_contains (st_transform(the_geom, 900913),ST_transform(st_geometryfromtext(c.way,4326),900913))
GROUP BY municipio,c.nome;
INSERT INTO uhporto SELECT municipio, h.nome
FROM caop, extensoessaude AS h
WHERE municipio='PORTO' AND st_contains (st_transform(the_geom, 900913),ST_transform(st_geometryfromtext(h.way,4326),900913))
GROUP BY municipio,h.nome
Desta forma é mais fácil quantificar pois apenas é necessária a seguinte query:
select count(*) as unidadesoficiais from uhporto
Resultando a seguinte tabela:
| unidadesoficiais |
|---|
| 18 |
Para verificar as unidades existentes no osm, utiliza-se a tabela hospitais criada no passo 3.
select municipio,count(h.name)
from caop, hospital as hospitaisosm
where municipio='PORTO'and st_contains (st_transform(the_geom, 900913),h.way)
group by municipio
Através desta query resulta a seguinte tabela, que nos permite fazer uma comparação entre o que se encontra no OSM e a realidade.
| municipio | hospitaisosm |
|---|---|
| PORTO | 10 |
Fica-se a saber então que das 18 unidades do município do Porto, estão registadas 10 no OSM. Caso se deseje saber o nome destas unidades pode-se executar a seguinte query:
select municipio,hospitalosm.name
from caop, hospital as hospitalosm
where municipio='PORTO'and st_contains (st_transform(the_geom, 900913),hospitalosm.way)
group by municipio,hospitalosm.name
Resultando a seguinte tabela:
| municipio | hospitaisosm |
|---|---|
| PORTO | Hospital Militar |
| PORTO | Ordem da Lapa |
| PORTO | Hospital Santo António |
| PORTO | Hospital geral de Santo António |
| PORTO | Hospital da Prelada |
| PORTO | Ordem da Trindade |
| PORTO | Magalhães Lemos |
| PORTO | Hospital de Santa Maria |
| PORTO | Casa de Saúde da Boavista |
| PORTO | Hospital de São João |
Recomendações
Com este trabalho foi possível constatar relações importantes em relação aos equipamentos de saúde. No entanto, existem duas ideias que poderiam facilitar e melhorar a tarefa de análise de equipamentos, tornando-a mais completa. Seria interessante definir tags com as quais se pudesse fazer a separação entre hospitais e centros de saúde, pois as valências disponibilizadas por estas duas entidades são distintas. Outra ideia interessante, seria criar uma tag para marcar os hospitais que estão autorizados a efectuar colheita de órgãos para doação. Podendo ser útil para diminuir o tempo de colheita de órgãos, na medida em que as pessoas sejam encaminhadas directamente para um destes centros.