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:

1. CAOP [3]
2. INE [4]

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:

1. População por Município
2. 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 Análise_da_rede_de_equipamentos_saúde#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 km² ([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.

---