Calculo da cobertura da Rede Escolar

Objectivo

Com este cálculo pretende-se determinar qual a percentagem de escolas georreferenciadas no OSM, isto é, determinar a sua cobertura.

Para tal, cria-se um base de dados de escolas com todas as que estão registadas no site http://www.min-edu.pt/index.php?s=roteiro-escolas. Essa base é comparada com o que já existe no OSM.

Além disso, através do INE obteve-se informação complementar sobre a população existente por município e respectivas faixas etárias.

Preparação de dados

Para se proceder a este cálculo, são precisos dados de quatro fontes:

• INE (População);
• IGP (CAOP);
• Ministério da Educação - Roteiro das Escolas (localização das escolas);
• OSM (para saber o que já está referenciado).

Os cálculos aqui apresentados foram efectuados sobre o pgAdmin III. Parte-se do princípio que já existe uma base de dados OSM, onde vão ser adicionadas todas as tabelas com informação das quatro fontes.

INE

Os dados relativos à população existente por município e respectivas faixas etárias foram obtidos no INE [1]. Descarregou-se no formato Excel o quadro da população residente (Nº) por local de residência, grupo etário, no ano 2010. Posteriormente, este ficheiro foi tratado sendo eliminado todos os dados desnecessários, e criando por fim, um ficheiro CSV.

De seguida, foi criada na base dados osm uma tabela de nome populacao através do pgAdmin III. Esta contém nove colunas (id, nome, codigo, faixa04, faixa59, faixa1014, faixa1519, faixa2024, faixa2529).

CREATE TABLE populacao (
  id serial NOT NULL,
  nome character varying(120) NOT NULL,
  codigo character varying(120),
  faixa04 integer,
  faixa59 integer,
  faixa1014 integer,
  faixa1519 integer,
  faixa2024 integer,
  faixa2529 integer,
  CONSTRAINT populacao_pkey PRIMARY KEY (id)
);

Por fim, populou-se a tabela com os dados obtidos no INE:

psql  -d  osm  -c  "copy  população (nome, codigo, faixa04, faixa59, faixa1014, faixa1519, faixa2024, faixa2529)  from  '/home/geobox/Transferências/pop_municipio.csv' delimiter ',' CSV HEADER"

Nota: A coluna id é preenchida automaticamente, dai não se encontrar no ficheiro csv originado.

OSM

De modo a 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". De seguida, através do terminal aplica-se o seguinte comando:

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

Com este comando, são criadas as tabelas na base dados OSM com a informação existente do OSM.

CAOP

A exportação de dados CAOP, para a base de dados OSM foi necessária. A tabela foi criada e designada de cont_aad_caop2010.

Descarregar a CAOP de 2010, em formato Shapefile, a partir de [2].

• 1º Passo: De modo a evitar problemas de codificação de texto, através do terminal, aplicou-se o seguinte código:

 export PGCLIENTENCODING=latin1

• 2º Passo: Posteriormente, outro comando foi aplicado para ignorar alguns erros menos relevantes (skipfailures) e para a importação da CAOP_2010:

 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.shp

A tabela Cont_AAD_CAOP2010 é assim criada na base de dados OSM. Por fim, foi necessário aplicar uns comandos em SQL através do pgAdmin III, de forma a garantir a compatibilidade dos sistemas de coordenadas.

 select updategeometrysrid('cont_aad_caop2010' , 'wkb_geometry', 900913)

 select setsrid(wkb_geometry, 900913) from cont_aad_caop2010

Obter as Escolas

Navegando no site http://roteiro.min-edu.pt/, descobre-se que a forma de chegar às escolas de um determinado distrito é através de um formulário de pesquisa, onde basta indicar o distrito, concelho e freguesia respectiva.

Verifica-se que cada pesquisa gera um pedido da seguinte forma:

http://roteiro.min-edu.pt/index.jsp?natureza=-1&tipo=-1&txt=estabelecimento&reg=F30210

Sistematiza-se o processo de obter os detalhes de todas as escolas, com os passos seguintes. Para tal, precisamos de saber os códigos (dicofre) de cada freguesia e, com esses códigos, geramos uma script que vai buscar os detalhes.

Obter os dados sobre as escolas existentes

• 1º Passo: query à base dados CAOP (retirar da CAOP todos os códigos DICOFRE e guardar num ficheiro sacaescolas.sh)

psql  -d geotuga  -c “select dicofre from cont_add_caop2010 order by dicofre” > sacaescolas.sh

• 2º Passo: Alguns arranjos ao ficheiro sacaescolas.sh, criando o ficheiro sacaescolas2.sh com os dados prontos.

cat sacaescolas.sh | grep "^ \+[0-9]" | sed 's/^ \(.*\)/curl --cookie cookie -o \1.html "http:\/\/roteiro.min-edu.pt\/index.jsp?natureza=-1\&tipo=-1
\&text=estabelecimento\&reg=F\1"; sleep 3/g' | sed 's/=F0/=F/g'  >  sacaescolas2.sh

• 3º Passo: Dar permissão ao ficheiro sacaescolas2.sh e correr o ficheiro.

chmod +x sacaescolas2.sh
./sacaescolas2.sh 

Com esta operação, são obtidos os detalhes em HTML. Contudo, estes dados apresentam muita informação que não é necessária.

Desta forma, para filtrar a informação contida nesta espécie de HTML, é necessário passar um filtro para limpar uma parte do HTML que não interessa e depois transformar o HTML restante para uma lista de campos separados por |.

• Foi então criado o filtro, pequeno documento XSLT de nome "filtra.xslt":

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xdt="http://www.w3.org/2005/xpath-datatypes">
	<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 select="//div[@id='resultados']"/>
	</xsl:template>
	<xsl:template match="div[@class='tit']">
	</xsl:template>
	<xsl:template match="img">
		<xsl:text>
</xsl:text>
		<xsl:value-of select="@src"/>
		<xsl:text>|</xsl:text>
		<xsl:value-of select="@pointx"/>
		<xsl:text>|</xsl:text>
		<xsl:value-of select="@pointy"/>
		<xsl:text>|</xsl:text>
	</xsl:template>
	<xsl:template match="td[@class='name']">
		<xsl:value-of select="normalize-space(.)"/>
		<xsl:text>|</xsl:text>
	</xsl:template>
</xsl:stylesheet>

Todos os dados obtidos (*.html) foram então filtrados. Para tal acção, aplicou-se um ciclo for e o comando xmlstarlet.

for nair in *.html; do tidy -utf8 -q -numeric -asxhtml --show-warnings no $nair | xmlstarlet tr --html filtra.xslt  2> /dev/null ; done >> auxescolas.csv

Após passar pelo filtro (filtra.xslt), extraímos apenas a informação necessária sem nenhumas tags, e assim reunindo toda a informação num único ficheiro (auxescolas.csv), em que os campos encontram-se separados por uma |.

• Uma pequena limpeza ao ficheiro auxescolas.csv, criando o ficheiro final escolas.csv:

sed 's/img\/icones\///g' auxescolas.csv >> a2escolas.csv
sed 's/.png//g'  a2escolas.csv >> a3escolas.csv
sed 's/|[0-9]\+ \. /|/g' a3escolas.csv >> escolas.csv

Importar para a base de dados as escolas existentes

Após todos os passos anteriores, resulta então um documento CSV (escolas.csv) com os dados de todas as escolas (existe uma cópia do mesmo disponível a partir de http://dl.dropbox.com/u/13672018/escolas.csv).

• Pequenas alterações ao ficheiro escolas.csv:

gvim escolas.csv  (para acrescentar na primeira linha os nomes dos campos: tipo | lat | lon | nome)   
sed -i 's/|/\t/g' escolas.csv 

• Criar um ficheiro VRT,"escolas.vrt", para ser utilizado pelo GDAL:

<OGRVRTDataSource>
    <OGRVRTLayer name="escolas">
        <SrcDataSource>escolas.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="lon" y="lat"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

• Verificar que o GDAL consegue ler interpretar o ficheiro:

ogrinfo -ro escolas.vrt

• Importar os dados para a base dados, criando uma tabela no PostgreSQL:

ogr2ogr -s_srs EPSG:4326 -t_srs EPSG:3763 -f "PostgreSQL" PG:"host=localhost user=geobox dbname=osm password=geobox" escolas.vrt

Obter tabela concelho

Uma vez que a CAOP se encontra com os polígonos por freguesia, convém criar então uma segunda shapefile, dissolvendo as freguesias e ficando só com os concelhos.

Foi assim criada e povoada a tabela concelho na base dados osm com a informação necessária.

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

insert into concelho (distrito_, municipio, contorno)
select distrito_,municipio, ST_Union(wkb_geometry)
from cont_aad_caop2010 group by municipio, distrito_;

SELECT Populate_Geometry_Columns('public.concelho'::regclass);

PostGIS

Através da aplicação Quantum GIS foi possível uma melhor visualização das escolas existentes em Portugal Continental.

Obteve-se a imagem ilustrada a baixo, para o caso de estudo correspondente ao Distrito de Braga. A azul encontra-se representado o distrito de Braga e os pontos a vermelho correspondem as escolas existentes no respectivo distrito.

Cálculos

Cálculo das escolas existentes em Portugal

De forma a verificar quantas escolas existem na realidade, é executada a seguinte query SQL sobre a tabela escolas, referente às escolas retiradas do mapa disponível em http://roteiro.min-edu.pt/ e tratado conforme descrito anteriormente:

select municipio, count(e.*)
from concelho, escolas e
where st_contains(contorno, st_transform(e.wkb_geometry, 900913))
group by municipio
order by count(e.*) desc

O resultado parcial obtido desta query SQL pode ser observado na seguinte tabela:

Cálculo das escolas existentes no OSM

No OSM, as escolas (school,kindergarten,college) podem ser marcadas como pontos ou como polígonos.

Portanto, para analisar e determinar a quantidade de escolas no OSM foi necessário verificar quantas estão marcadas como pontos ou polígonos.

Escolas definidas como pontos no OSM

Para determinar quantas escolas estão assinaladas como pontos no OSM, a seguinte query SQL executada:

select municipio, count(osm_id) AS "Escolas OSM"
from cont_aad_caop2010, planet_osm_point
where (amenity = 'school' OR amenity = 'kindergarten' OR amenity = 'college')
and st_contains(wkb_geometry, way)
group by municipio
order by count(*) desc

O resultado parcial obtido desta query SQL pode ser observado na seguinte tabela:

Escolas definidas como polígonos no OSM

Para determinar quantas escolas estão assinaladas como polígonos no OSM, a seguinte query SQL executada:

select municipio, count(osm_id) AS "Escolas OSM"
from cont_aad_caop2010, planet_osm_polygon
where (amenity = 'school' OR amenity = 'kindergarten' OR amenity = 'college')
and st_contains(wkb_geometry, way)
group by municipio
order by count(*) desc

O resultado parcial obtido desta query SQL pode ser observado na seguinte tabela:

Resultado Final

Para obter o número total de escolas existentes na realidade e o total de escolas registadas no OSM, executa-se as seguintes query's:

• Informação das escolas existentes na realidade.

select count(e.*)
from escolas e

• Informação das escolas existentes no OSM (como pontos e como polígonos).

select count(p.*)
from cont_aad_caop2010, planet_osm_point p
where (amenity = 'school' OR amenity = 'kindergarten' OR amenity = 'college') and st_contains(wkb_geometry, way)

select count(p.*)
from cont_aad_caop2010, planet_osm_polygon p
where (amenity = 'school' OR amenity = 'kindergarten' OR amenity = 'college')
and st_contains(wkb_geometry, way)

-- Resultado das Query's --

• Total_escolas_real = 8185
• Total_escolas_OSM = 489 (point) + 629 (polygon) = 1258

Assim, através do resultado obtido destas query's, observa-se que existem mais de 8000 escolas em Portugal, e apenas estão referenciadas 1258 no OSM.

Autores

Trabalho realizado no âmbito da UC Sistemas de Informação Geográfica pelos alunos:

• Marco Rodrigues nº 56759
• Nair Alves nº 52558