Cálculo de cobertura de Campos de golfe

Objectivo

Com o cálculo de cobertura de Campos de golfe pretende-se determinar qual a percentagem de campos de golfe georreferenciados no OSM em Portugal.

Para ser possível cumprir este objectivo foi necessário utilizar várias fontes de dados:

• Campos de golfe,
• CAOP (Dados correspondentes aos limites administrativos de Portugal),
• INE (Dados estatísticos correspondentes à densidade populacional de Portugal),
• OSM.

Preparação dos dados - Campos de golfe

Os dados sobre os campos de golfe existentes em Portugal encontram-se disponíveis no endereço web: [1]

Como se pode ver na imagem 1, os 87 resultados obtidos para a pesquisa de Campos de golfe encontram-se em links.

Após se clicar num link do campo de golfe, é possível visualizar alguns detalhes deste, como se pode observar na imagem 2.

Através do mapa é possível visualizar a localização do Campo de golfe (Imagem 3)

Para ser possível recolher as informações sobre os campos de golfe (nome, coordenadas x e y) contidas no site, utilizou-se um Add-on do Firefox denominado Firebug versão 1.7.3 [2]. Este Add-on extrai o seguinte link:

http://mapas.visitportugal.com/feature/poi/golfe?zona=0&nome=&limit=101&suggest=on&offset=0

Este link contém um XML com os vários registos, do género:

<item id="/feature/poi/golfe/{699B43EB-EC66-4E1D-964A-61E64E35634D}" guid="{699B43EB-EC66-4E1D-964A-61E64E35634D}" 
dist="0.0">Balaia Golf Village</item>

A informação recolhida pelo Firebug é armazenada no ficheiro golfe.xml.

Pretende-se extrair apenas o URL de cada campo de golfe, esta informação é direccionada para o script:

grep -o "/feature[^']\+" golfe.xml > saca.sh 

Foi desenvolvido um script.

echo "<campos>" > resultado.xml
curl "http://mapas.visitportugal.com/feature/poi/golfe/%7B699B43EB-EC66-4E1D-964A-61E64E35634D%7D" >> resultado.xml
curl "http://mapas.visitportugal.com/feature/poi/golfe/%7B03063D7F-9944-47DF-9C74-683AB2CB5E62%7D" >> resultado.xml
...
curl "http://mapas.visitportugal.com/feature/poi/golfe/%7BACCB835F-8A62-448C-A76F-D54C539FB595%7D" >> resultado.xml
echo "</campos>" >> resultado.xml
cat resultado.xml | sed 's/<?xml version="1.0" encoding="UTF-8"?>//g' > resultado2.xml

Este script cria um ficheiro XML, denominado resultado.xml e introduz como conteúdo a informação contida nas diversas páginas web correspondentes aos vários links dos campos de golfe.

O comando curl [3] presente no script retorna toda a informação contida na página para o output. Este output por sua vez é direccionado para o ficheiro resultado.xml.

Para:

curl "http://mapas.visitportugal.com/feature/poi/golfe/%7B699B43EB-EC66-4E1D-964A-61E64E35634D%7D" >> resultado.xml

É introduzida a seguinte informação no ficheiro:

<poi id="/feature/poi/golfe/{699B43EB-EC66-4E1D-964A-61E64E35634D}"><nome>Balaia Golf Village</nome><guid>
{699B43EB-EC66-4E1D-964A-61E64E35634D}</guid><map href="/map?pos=-6572.196384,-285295.244134,350&select=/
feature/poi/golfe/{699B43EB-EC66-4E1D-964A-61E64E35634D}"><x>-6572.196384</x><y>-285295.244134</y><scale>
350</scale></map></poi>

De seguida é necessário retirar nome, X e Y. Pretende-se gerar um ficheiro em que a informação acerca de cada campo de golfe se encontre numa linha, sem tags e os seus campos separados por um |. Para tal recorreu-se a um editor de XML, para criar o ficheiro final.xsl, que terá a função de filtrar a informação contida no ficheiro resultado.xml.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="/">
        <xsl:apply-templates/>
    </xsl:template>
        <xsl:template match="nome">
            <xsl:apply-templates/>
            <xsl:text>|</xsl:text>
        </xsl:template>
        <xsl:template match="x">
            <xsl:apply-templates/>
            <xsl:text>|</xsl:text>
        </xsl:template>
        <xsl:template match="y">
            <xsl:apply-templates/>
            <xsl:text>
            </xsl:text>
        </xsl:template>
    <xsl:template match="guid"/>
    <xsl:template match="scale"/>
</xsl:stylesheet>

A informação resultante foi guardada num ficheiro denominado golfefinal.txt.

<?xml version="1.0" encoding="utf-8"?>
Balaia Golf Village|-6572.196384|-285295.244134
            CS Salgados Golf|-16838.41327|-286216.271853
            Pine Cliffs Golf e Country Club|-4204.257416|-285406.261916
            Aroeira I|-91738.328937|-120777.36681
...

Agrupando toda a informação no ficheiro golfe.csv. Posteriormente foi adicionado o nome de cada coluna.

nome	coordenadax	coordenaday
Balaia Golf Village	-6572.196384	-285295.244134
CS Salgados Golf	-16838.41327	-286216.271853
Pine Cliffs Golf e Country Club	-4204.257416	-285406.261916
Aroeira I	-91738.328937	-120777.36681

Este ficheiro encontra-se disponível em [4]).

Importar para a Base de dados

É necessário criar um ficheiro VRT, que contem a seguinte informação:

<OGRVRTDataSource>
    <OGRVRTLayer name="golfe">
        <SrcDataSource>golfe.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="coordenadax" y="coordenaday"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

Através do comando seguinte, é possível saber se o ficheiro é valido.

ogrinfo -ro golfe.vrt golfe | less

Para se conseguir criar uma tabela no Postgresql, utilizou-se o seguinte comando:

ogr2ogr -overwrite -skipfailures -s_srs EPSG:27492 -t_srs EPSG:900913 -f "PostgreSQL" PG:"host=localhost 
user=geobox dbname=osm password=geobox" golfe.vrt 

Importação dos dados da CAOP

Para uma melhor analise da distribuição dos equipamentos de lazer de acordo com a região administrativa em que se encontra, recorreu-se aos dados dos limites administrativos fornecidos pela CAOP (CARTA ADMINISTRATIVA OFICIAL DE PORTUGAL). Estes dados foram retirados da sua fonte oficial [5] e posteriormente importados para a base de dados "OSM".

Para tal foi necessário efectuar um conjunto de passos, descritos de seguida:

• Descarregar a CAOP de 2010, em formato Shapefile, o ficheiro correspondente à área administrativa (Polígonos) correspondente ao Continente. Escolher a norma ISO 8859-1.
• No contexto do trabalho a desenvolver, a divisão do território em freguesias não apresenta relevância, tendo disso estas dissolvidas. Para tal, carregou-se o Shapefile no QGis, através da opção Nova camada vectorial. De seguida a informação relativa às freguesias foi dissolvida através do menu Vector -> Ferramentas de Geoprocessamento -> Dissolver -> Escolher a opção freguesia no Campo "Dissolver a partir do campo".
• De modo a evitar problemas de codificação de texto, deve-se correr o seguinte comando:

export PGCLIENTENCODING=LATIN1

• O shapefile é carregado para o PostGis, através do comando:

ogr2ogr -skipfailures -overwrite -s_srs EPSG:3763  -t_srs EPSG:900913 -f "PostgreSQL" PG:"host=localhost user
=geobox dbname=osm password=geobox" Cont_AAD_CAOP2010_concelho.shp

• A tabela Cont_AAD_CAOP2010_concelho foi adicionada à base de dados OSM.
• As seguintes querys são executadas, para garantir a compatibilidade dos sistemas de coordenadas.

select updategeometrysrid('cont_aad_caop2010_concelho', 'wkb_geometry', 900913);
select setsrid(wkb_geometry, 900913) from cont_aad_caop2010_concelho;

A imagem seguinte mostra Portugal Continental Antes e Depois do processo de dissolução das freguesias.

Uma alternativa a alguns passos a cima é criar uma tabela "concelhos" na base de dados e povoar com os dados relativos ao município, distrito e aos contornos. Não esquecer de carregar a shapefile para o PostGis.

Através do comando seguinte:

export PGCLIENTENCODING=LATIN1
ogr2ogr -skipfailures -overwrite -s_srs EPSG:27492  -t_srs EPSG:900913 -f "PostgreSQL" PG:"host=localhost user=
geobox dbname=osm password=geobox" Cont_AAD_CAOP2010.shp

CREATE TABLE concelho
(
  id serial NOT NULL,
  distrito character varying(254),
  municipio character varying(254),
  contorno geometry,
  CONSTRAINT concelho_pk PRIMARY KEY (id)
)

Para povoar a tabela com os dados, fazemos a seguinte query:

insert into concelho (distrito, municipio, contorno)
select distrito_, municipio, st_union(wkb_geometry)
from cont_aad_caop2010
group by distrito_, municipio

Importação dos dados do INE

Os dados relativos à população de Portugal encontra-se disponível em na página web do INE (Instituto Nacional de Estatística). O objectivo destes dados é comparar a densidade populacional com o número de campos de golfe disponíveis em cada distrito.

Os dados foram retirados da sua fonte oficial [6] e posteriormente importados para a base de dados "OSM".

Para tal foi necessário efectuar um conjunto de passos, descritos de seguida:

• O ficheiro retirado da página web encontra-se no formato .xsl, é necessário converter para .csv. Nesta conversão é retirada a informação desnecessária, ficando apenas as colunas correspondentes ao nome e ao código do concelho e o número de habitantes.

• No pgAdminII é necessário criar a tabela. Esta pode ser criada através do seguinte código:

CREATE TABLE populacao (
  concelho character varying(120) NOT NULL,
  codigo char(120),
  habitantes integer
);

• Para carregar a tabela com os dados dos concelhos/n_habitante, basta correr o seguinte código:

copy populacao (concelho, codigo, habitantes) from '/home/geobox/Transferências/populacao.csv' delimiter ';' 
CSV HEADER

Importação dos dados do OSM

Antes de importar os dados do OSM, é necessário ter a base de dados do OSM. Os passos encontram-se em [7]

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 [8]

No caso especifico deste trabalho, o ficheiro com a informação existente no OSM para Portugal encontra-se no ficheiro portugal.osm.bz2, sendo esta informação inserida na base de dados recorrendo ao seguinte comando:

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

A informação existente no OSM é armazenada nas tabelas que são criadas no PostGIS:

• planet_osm_line
• planet_osm_point
• planet_osm_polygon
• planet_osm_roads

Visualização dos dados

A figura seguinte mostra o mapa de Portugal produzido pelo software Quantum GIS com os pontos relativos aos campos de golfe que existem realmente.

NOTA: Os dados correspondentes à CAOP(AZUL) foram apenas carregados para Portugal Continental. Os pontos isolados correspondem ao arquipélago da Madeira e dos Açores.

A figura que se segue corresponde aos campos de golfe existentes no OSM marcados como POINT(vermelho).

De seguida é mostrado o mapa de Portugal com os campos de golfe marcados como POLYGON(vermelho).

O mapa seguinte (zona do Algarve) mostra os campos de golfe que existem realmente (AMARELO), os campos marcados no OSM como POINT(VERDE) e POLYGON(VERMELHO).

Cálculos

Nesta secção é apresentada um conjunto de queries e as respostas obtidas.

Cálculo dos campos de golfe existentes na Realidade

Os campos de golfe que existem na realidade podem ser calculados através da seguinte query:

select count(*) as contagem from golfe

contagem
87

Cálculo dos campos de golfe existentes no OSM

Os campos de golfe podem ser adicionados ao OSM de duas formas, como:

• POINT
• POLYGON

Desta forma, criou-se duas querys para a contagem:

• POINT

select count(*) as contagem from planet_osm_point  where leisure='golf_course'

contagem
7

• POLYGON

select count(*) as contagem from planet_osm_polygon  where leisure='golf_course'

contagem
69

Comparando os resultados obtidos com o número de campos de golfe (87 campos) da base de dados criada, pode-se verificar que faltam ainda marcar alguns campos de golfe.

Cálculo do número de campos de golfe sem qualquer nome atribuído

Pode existir casos em que são adicionados campos de golfe ao OSM mas não é atribuído nenhum nome. Para saber o número de casos em que isso acontece faz-se as seguinte query:

• POINT

select count(*) as contagem 
from planet_osm_point 
where leisure='golf_course' and name isnull

contagem
1

• POLYGON

select count(*) as contagem 
from planet_osm_polygon 
where leisure='golf_course' and name isnull

contagem
56

Dados duplicados

Existem diversas formas de aparecerem dados duplicados no OSM, no caso corrente pode acontecer devido a:

• Os campos de golfe no OSM podem ser marcados como POINT ou POLYGON, logo podem existir casos em que o mesmo campo de golfe pode ser marcado tanto como POINT como POLYGON.

A forma mais simples de detectar esta repetição é através da procura de nomes iguais num POINT e num POLYGON.

select planet_osm_point.name 
from planet_osm_polygon, planet_osm_point 
where planet_osm_polygon.leisure='golf_course' and 
planet_osm_point.leisure='golf_course' and 
planet_osm_polygon.name=planet_osm_point.name

name text
SALGADOS

• Pode acontecer o caso de existir para o mesmo campo de golfe duas marcações idênticas (dois POINT, ou dois POLYGON)

Mais uma vez pode-se detectar se existem nomes iguais.

select name, count(*) as contagem 
from planet_osm_point 
where leisure='golf_course' group by name order by contagem desc

Através da tabela acima pode-se concluir que para o POINT não existem campos duplicados.

select name, count(*) as contagem 
from planet_osm_polygon 
where leisure='golf_course' group by name order by contagem desc

Através da tabela acima pode-se concluir que existe um campo de golfe que aparece marcado no OSM duas vezes.

Como é referido anteriormente existem muitos campos sem nome, logo, apesar da detecção de nomes duplicados identificar um caso replicado, o facto de haver muitos campos sem nome não permite ter a certeza que todos foram detectados. Assim é necessário ter também em conta outro método para detectar possíveis erros de marcação.

Eliminação dos casos duplicados

Antes de efectuar qualquer query é necessário eliminar os dados duplicados.

delete from planet_osm_polygon as pg
where pg.name=(
     select planet_osm_point.name
     from planet_osm_polygon, planet_osm_point
     where planet_osm_polygon.leisure='golf_course' and planet_osm_point.leisure='golf_course' and
     planet_osm_polygon.name=planet_osm_point.name)

Neste caso são eliminadas todas as linha da tabela planet_osm_polygon que tem um nome repetido na tabela planet_osm_point. Como referido anteriormente apenas existe um resultado:

Query returned successfully: 1 row affected

A linha eliminada corresponde ao nome SALGADOS.

A query seguinte elimina dados repetidos na tabela planet_osm_polygon

delete from planet_osm_polygon as pg
where pg.osm_id=(
     select osm_id 
     from planet_osm_polygon as pg, (
          select name, count(*) as contagem 
          from planet_osm_polygon 
          where leisure='golf_course' and not name isnull 
          group by name 
          order by contagem desc) as sel 
     where sel.contagem > 1 and pg.name=sel.name limit 1)

Neste caso o resultado é o seguinte:

Query returned successfully: 1 row affected

A linha eliminada corresponde ao nome repetido PENINA CHAMPIONSHIP.

Existem casos em que os pontos encontram-se marcados dentro dos poligonos. Segue-se essa query:

select distinct pg.osm_id, pg.name, pt.osm_id, pt.name 
from planet_osm_point as pt, planet_osm_polygon as pg 
where st_contains(pg.way,pt.way) and pt.leisure='golf_course' and pg.leisure='golf_course' limit 1

O resultado é o seguinte:

Para eliminar os poligonos quem contem pontos a marcar um campo de golfe basta executar a seguinte query, até não restar nenhum poligono nestas condições.

delete from planet_osm_polygon as pg 
where pg.osm_id = (
      select distinct pg.osm_id 
      from planet_osm_point as pt, planet_osm_polygon as pg 
      where st_contains(pg.way,pt.way) and pt.leisure='golf_course' and pg.leisure='golf_course' limit 1)

Cálculo do número de campos de golfe por concelho

É possível saber quantos campos de golfe existem na realidade por concelho.

select municipio, count(*) as contagem_golfe 
from golfe, concelho 
where _st_contains(concelho.contorno, golfe.wkb_geometry) 
group by municipio 
order by contagem_golfe desc

NOTA: A tabela só mostra para 19 concelhos, havendo num total 37 concelhos com campos de golfe

De seguida calcula-se os campos de golfe existentes no OSM por concelho. Para calcular os campos de golfe definidos como POLYGON, é necessário numa primeira etapa verificar se os poligonos desenhados são validos ou não, isto é possível através da seguinte query:

select name, osm_ide, st_isvalid(way) from planet_osm_polygon where leisure = 'golf_course' and not st_isvalid(way)

O resultado desta query é que existem 3 campos de golfe inválidos.

Estes resultados podem ser eliminados através da seguinte query:

delete from planet_osm_polygon where not st_isvalid(way) and leisure = 'golf_course'

A query que se segue calcula os campos de golfe (definidos como POLYGON) existentes no OSM, por concelho.

select municipio, count(*) as contagem_pg
from planet_osm_polygon as pg, concelho
where pg.leisure='golf_course' and st_isvalid(way) and st_contains(concelho.contorno, st_transform(pg.way,900914))
group by municipio
order by contagem_pg desc

A query seguinte calcula o número de campos de golfe por concelho, existentes no OSM como POINT.

select municipio, count(*) as contagem_pt 
from planet_osm_point as pt, concelho 
where pt.leisure='golf_course' and _st_contains(concelho.contorno, st_transform(pt.way,900914)) 
group by municipio 
order by contagem_pt desc

Distância mínima entre dois campos de golfe

Para saber quais os campos mais próximos dentro de Portugal, efectuou-se a seguinte query:

select nome1, nome2, dist 
from (select g1.nome as nome1, g2.nome as nome2, distance(g1.wkb_geometry, g2.wkb_geometry) as dist 
      from golfe as g1, golfe as g2) as distancias 
where dist > 0 order by dist limit 1

O resultado obtido é:

Campo de golfe mais próximo do Aeródromo de Braga

A título de curiosidade efectuou-se a seguinte query:

select golfe.nome,municipio , distance(golfe.wkb_geometry, st_transform(pt.way,900914)) as dist
from  golfe, planet_osm_point as pt, concelho as caop
where pt.name = 'Aeródromo de Braga' and _st_contains(caop.contorno, golfe.wkb_geometry)
order by dist
limit 1

Esta query devolve o campo de golfe mais próximo do aeródromo de Braga.O resultado é o seguinte:

Campos de golfe pertencentes a um determinado concelho

Para saber que campos de golfe pertenciam a um determinado concelho realizou-se a seguinte query (O concelho escolhido para teste foi o de Loulé):

select nome
from golfe, concelho
where st_contains (concelho.contorno, golfe.wkb_geometry) and municipio ilike 'Loulé';

O resultado obtido para o concelho de Loulé foi o seguinte:

Este resultado pode ser visualizado no Quantum Gis:

Cálculo da densidade populacional

Densidade demográfica, densidade populacional ou população relativa é a medida expressa pela relação entre a população e a superfície do território e pode ser calculada através da divisão da população pela área do concelho.

A seguinte query calcula a densidade populacional por concelho (pessoas por km):

select populacao.concelho, (habitantes / (st_area(contorno)/100000)) as densidade
from populacao, concelho
where concelho.municipio = populacao.concelho
order by densidade desc

Alguns desses resultados encontram-se na tabela seguinte:

Cálculo do número de campos de golfe por 1000 pessoas

A seguinte query calcula o número de campos de golfe por 1000 pessoas:

select populacao.concelho, contagem, (contagem / float8(habitantes/1000)) as campos_por_1000pessoas
from populacao, concelho, 
    (select municipio, count(*) as contagem from golfe, concelho 
    where _st_contains(concelho.contorno, golfe.wkb_geometry) 
    group by municipio 
    order by contagem desc) as golfe
where concelho.municipio = populacao.concelho and golfe.municipio = concelho.municipio
order by campos_por_1000pessoas desc

Alguns desses resultados encontram-se na tabela seguinte:

Cálculo da área de um campo de golfe

A vantagem de uma marcação do tipo POLYGON é que posteriormente é possível calcular a área desse polígono. Para cada campo de golfe marcado com POLYGON no OSM calculou-se a sua àrea, segundo a seguinte query:

select osm_id, name, st_area(way) as area
from planet_osm_polygon
where leisure='golf_course'
order by area desc

O resultado obtido encontra-se ordenado do maior para o menor.

Campos de golfe mais próximos de um determinado hotel

A query que se segue pretende encontrar os campos de golfe mais próximos de um dado hotel.

select golfe.nome, st_distance(st_transform(way,900914), golfe.wkb_geometry) as distancia
from golfe, planet_osm_polygon
where tourism='hotel' and name='Dom Pedro Marina'
order by distancia

O resultado desta query é o seguinte:

O campo de golfe mais próximo do hotel DOM PEDRO MARINA, do concelho LOULÉ é o Oceânico Pinhal Golf Course.

Conclusão

As querys efectuadas sobre os campos de golfe existentes na realidade e os mapeados no OSM (tanto como POINT ou POLYGON) leva a concluir que um pequeno número ainda falta ser mapeado e os que se encontram mapeados alguns apresentam erros (falta de nome, duplicados(marcados como POINT e POLYGON), ou sobrepostos).

A zona do Algarve é aquela que apresenta um maior número de campos de golfe mapeado sendo o concelho da LAGOA aquele que apresenta um maior número de campos mapeados.