Detecção de erros de catalogação no OSM

Deteção de erros de catalogação no Open Street Map em Portugal Continental

O objectivo deste levantamento é identificar as tags que necessitam de mais edições.

Quantos equipamentos de saúde existem sem nome em Portugal, por município? E qual a sua localização?

Inicialmente é necessário saber quantos equipamentos de saúde existem em cada município, para isso utiliza-se a seguinte query:

SELECT municipio, amenity, name
FROM planet_osm_point, cont_aad_caop2010
WHERE amenity = 'hospital'
AND st_contains (st_transform(wkb_geometry, 900913), way)
group by municipio, amenity, name
order by municipio asc

Posteriormente obtém-se a contagem deste tipo de equipamentos que se encontram sem nome

SELECT count (*), municipio, amenity
FROM planet_osm_point, cont_aad_caop2010
WHERE amenity = 'hospital'
AND st_contains (st_transform(wkb_geometry, 900913), way)
AND name is null
group by municipio, amenity
order by municipio asc

Seguidamente atribui-se uma localização espacial àqueles hospitais que se encontram sem nome, de modo a facilitar uma posterior correcção, utilizando a seguinte query:

SELECT municipio, amenity, st_astext(way)
FROM planet_osm_point, cont_aad_caop2010
WHERE amenity = 'hospital'
AND st_contains (st_transform(wkb_geometry, 900913), way)
AND name is null
group by municipio, amenity, st_astext(way)
order by municipio asc

--Pedro lourenco

Cemitérios

Identificação dos cemitérios sem nome

Número de cemitérios que se encontram sem nome, por Município, com os valores dispostos por ordem crescente por concelho e por ordem decrescente por número de cemitérios

SELECT COUNT(p.*), c.municipio
FROM planet_osm_point p, caop2010_concelho c
WHERE amenity = 'grave_yard' or landuse = 'cemetery' 
AND name IS NULL 
AND st_contains(wkb_geometry, way)
GROUP BY c.municipio
ORDER BY COUNT(p.*) DESC, c.municipio

Localização espacial dos cemitérios que não têm nome

Query:

SELECT municipio, amenity, st_astext(way)
FROM planet_osm_point, caop2010_concelho
WHERE amenity = 'grave_yard'or landuse = 'cemetery'
AND st_contains (st_transform(wkb_geometry, 900913), way)
AND name IS NULL
GROUP BY municipio, amenity, st_astext(way)
ORDER BY municipio ASC

Área de cada cemitério registado por polígonos no OpenStreetMap Assim, pode saber-se a área de cada cemitério sabendo também o seu nome e o seu código

select osm_id, amenity, landuse, name, (way_area)
from planet_osm_polygon
where amenity= 'grave_yard'or amenity = 'cemetery'

Definição de centróides de poligonos.

Para se poder calcular cada um destes elementos como um ponto, definiu-se o centróide de cada poligono. Assim, definiu-se a seguinte query:

select osm_id, name,amenity, landuse, Centroid(way)
from planet_osm_polygon
where amenity = 'grave_yard'or landuse = 'cemetery'
order by amenity

--Manuel Teixeira

Equipamentos de cultura

É importante conhecer algumas informações acerca dos equipamentos culturais existentes, nomeadamente o nome e a actividade de um equipamento em causa (se é um museu, um teatro, um cinema, ...).

Teatros

A identificação dos teatros que não têm nome associado pode ser realizada através das seguintes querys:

Create table Teatros_pontos as
SELECT osm_id, amenity, name
FROM planet_osm_point
WHERE amenity = 'theatre'
and name is NULl

Create table Teatros_poligonos as
SELECT osm_id, amenity, name
FROM planet_osm_polygon
WHERE amenity = 'theatre'
and name is NULl

Nota: Os teatros podem constar da tabela dos elementos representados como pontos ou como polígonos o que explica a aplicação das 2 querys.

Resultados obtidos a partir da tabela dos pontos:

osm_id	amenity	name
1207840242	theatre	<null>

Resultados obtidos a partir da tabela dos polígonos:

osm_id	amenity	name
39784735	theatre	<null>
100147559	theatre	<null>
100037603	theatre	<null>

Restaurantes de fast food e ecopontos

Fez-se uma contagem de todos os restaurantes de fast food e foram devolvidos 109 resultados. Depois foram seleccionados os que não têm nome e a consulta devolveu 24 resultados.

select count (*)
from planet_osm_point
where amenity = 'fast_food'

select count (*)
from planet_osm_point
where amenity = 'fast_food'
and name is null

De forma a facilitar a actualização da base de dados, foi realizada uma consulta que indique quais os concelhos em que os restaurantes de fast food não têm nome:

SELECT COUNT(p.*), c.municipio
FROM planet_osm_point p, cont_aad_caop2010 c
WHERE amenity = 'fast_food' 
AND name IS NULL 
AND st_contains(st_transform(wkb_geometry, 900913), way)
GROUP BY c.municipio
ORDER BY COUNT(p.*) DESC, c.municipio

Em seguida foram contados os restaurantes de fast food desagregados por Concelho e Distrito.

Posteriormente foram contados os ecopontos: total e desagregado por Concelho e Distrito.

total= 274

SELECT COUNT(*)
FROM planet_osm_point
WHERE amenity = 'recycling'   

Castelos e Fortificações de Portugal

Número de castelos e fortes catalogados, suas respectivas tags, tipo de representação geométrica e localização por concelho. Pretende-se encontrar quantos castelos e fortes estão representados como pontos e quantos estão como polígono, onde estão localizados, e indicar os que não possuem nome atribuído e substituir o NULL por "unclassified".

1 - Quantos castelos e fortes estão catalogados como pontos e polígonos

Quantos castelos estão catalogados como pontos?

Select count(*)

from planet_osm_point

where historic = 'castle'

resultado= 24


Quantos castelos estão catalogados como polígonos?'''

Select count(*)

from planet_osm_polygon

where historic = 'castle'

resultado= 29


Quantos fortes estão catalogados como pontos?'''

Select count(*)

from planet_osm_point

where historic = 'fort'

resultado=1


Quantos fortes estão catalogados como polígonos?'''

Select count(*)

from planet_osm_polygon
 
where historic = 'fort'

resultado=3


Quantos castelos e fortes estão catalogados como pontos?'''

Select count(*)

from planet_osm_point

where historic = 'fort' or historic = 'castle'~

resultado= 25


Quantos castelos e fortes estão catalogados como polígonos?'''

Select count(*)

from planet_osm_polygon

where historic = 'fort' or historic = 'castle'

resultado= 32

2 - Quantos castelos e fortes aparecem com nome?

Select historic, name

from planet_osm_polygon

where historic = 'fort' or historic = 'castle'

and name is not null

resultado=24

3 - Atribuir o tag "unclassified" aos castelos sem nome. Para fazer o mesmo com os fortes, basta substituir "castle" por "fort" na query.

update planet_osm_point set name = 'unclassified'

where planet_osm_point.historic = 'castle'
 
and planet_osm_point.name is null

4 - Quantos castelos e fortes surgem por municipio?

select count(*), municipio, historic

from planet_osm_point, cont_aad_caop2010

where historic = 'castle'

and st_contains(st_transform(wkb_geometry, 900913), way)

Group By historic, municipio

order by count desc

--Juliano Mattos

Bombeiros

O objectivo é mostrar que nem todos dos Concelhos têm bombeiros registados.

1º Passo

Saber os Concelhos existentes em Portugal Continental presentes no Open Street Map

select distinct municipio 
from cont_aad_caop2010
order by municipio

Esta query devolve os diferentes municípios de Portugal Continental, ou seja devolve 278 municípios.

2º Passo

Saber quantas corporações de Bombeiros estão registadas no Open Street Map

select name, st_astext(way)
from planet_osm_point
where amenity = 'fire_station'
order by name

Esta query devolve as corporações esxistentes no Open Street Map, que são 99 e em que 23 não têm nome e 4 estão fora do Comtinente.. Esta query devolve também as coordenadas de cada corporação.

3º Passo

Saber os concelhos que têm corporação de Bombeiros.

select municipio, planet_osm_point.name
from cont_aad_caop2010, planet_osm_point
where amenity = 'fire_station' and st_contains (the_geom,way)
order by municipio

Temos então com esta query os municipios que tẽm corporação de bombeiros. Esta query devolve 95 registos sendo que a diferença verificada diz respeito as corporações que não estão no Continente.

--LITO 15:57, 27 April 2011 (BST)

Identificação dos estádios e centros desportivos

Identificação dos estádios e centros desportivos

Parque de Estacionamento

1º Passo: Download da CAOP e importação para PgAdmin III

[1]

2º Passo: Quantos parques de estacionamento do tipo polígono estão representados no OSM

Select *
from planet_osm_polygon
where amenity = 'parking'

Resultado =628

2º Passo: Quantos parques de estacionamento do tipo polígono existem por concelho

SELECT COUNT (osm_id), municipio
FROM planet_osm_polygon, cont_aad_caop2010
WHERE amenity = 'parking'
AND st_contains(st_transform(wkb_geometry, 900913), way)
Group by municipio
order by municipio ASC

3º Passo: Qual média da soma dos parques de estacionamento por concelho?

Select avg (way_area), count (osm_id), municipio
from planet_osm_polygon, cont_aad_caop2010
WHERE amenity = 'parking'
AND st_contains(st_transform(wkb_geometry, 900913), way)
group by municipio
order by municipio ASC

4º Passo: Quantos lugares de estacionamento em média existem por concelho?

Select avg ((way_area)/6.5), count (osm_id), municipio
from planet_osm_polygon, cont_aad_caop2010
WHERE amenity = 'parking'
AND st_contains(st_transform(wkb_geometry, 900913), way)
group by municipio
order by avg desc

5º Passo: Verificar quantos parques de estacionamento no planet_osm_polygon têm a tag área ‘yes’ e comparar com os parques que não tem dados nessa coluna.

Área ‘NULL’ agrupados por município

SELECT COUNT (osm_id), municipio
FROM planet_osm_polygon, cont_aad_caop2010
WHERE amenity = 'parking'and area is null
AND st_contains(st_transform(wkb_geometry, 900913), way)
Group by municipio
order by municipio ASC

Resultado = 90

Área ‘YES’ Agrupado por município

SELECT COUNT (osm_id), municipio
FROM planet_osm_polygon, cont_aad_caop2010
WHERE amenity = 'parking'and area = 'yes'
AND st_contains(st_transform(wkb_geometry, 900913), way)
Group by municipio
order by municipio ASC

Resultado = 24

6º Passo: Modificar todas as tags de “área” que não tenham qualquer valor para ‘yes’.

Update planet_osm_polygon set area = 'yes'
where planet_osm_polygon.amenity ='parking'and planet_osm_polygon.area is null

Resultado = 573

7º Passo: Verificação do update efectuado na ultima querie.

SELECT COUNT (osm_id), municipio
FROM planet_osm_polygon, cont_aad_caop2010
WHERE amenity = 'parking'and area is null
AND st_contains(st_transform(wkb_geometry, 900913), way)
Group by municipio
order by municipio ASC

Resultado = 0

Locais de Culto: Quantos locais e culto possuem uma designação e quantos não possuem no concelho de Barcelos?

1º Passo: Aferir o número de locais de culto catalogados no OSM no concelho de Barcelos

select amenity, municipio, way from planet_osm_point, cont_aad_caop2010 where st_contains (st_transform (wkb_geometry, 900913), way) and amenity = 'place_of_worship' and municipio = 'BARCELOS'

Resposta: Existem 10 locais de culto.

2º Passo: Aferir o número de locais de culto que não possuem designação

select amenity, municipio, way from planet_osm_point, cont_aad_caop2010 where st_contains (st_transform (wkb_geometry, 900913), way) and amenity = 'place_of_worship' and municipio = 'BARCELOS' and name is null

Resposta: Existem 2 locais de culto sem designação

3º Passo: Resultados Finais

Pode-se concluir que dos 10 locais de culto catalogados no OSM no concelho de Barcelos, 2 deles não possuem designação constituindo-se portanto como um erro uma vez que todos os locais de culto deveriam ter designação atribuída.

Bibliotecas

O principal objectivo das seguintes queries é questionar a base de dados sobre informações relativas a bibliotecas: lista de bibliotecas, lista de bibliotecas sem nome, agrupamento espacial de bibliotecas, localização de bibliotecas e criação de tabelas com informações relativas a tabelas. Nota: de forma a realizar com sucesso as queries é necessário primeiro importar as shp planet_osm_point, caop2010_concelho.

1- Quais as bibliotecas representadas por pontos presentes numa determinada região?

SELECT distinct a.amenity, b.municipio
FROM planet_osm_point a, caop2010_concelho b
WHERE distrito = 'PORTO'
AND (amenity = ‘library’)
AND st_contains (st_transform(wkb_geometry, 900913), way)
GROUP BY municipio, amenity
ORDER BY municipio

2- Quais as bibliotecas que não possuem nome agrupadas por município?

SELECT distinct a.amenity, b.municipio
FROM planet_osm_point a, caop2010_concelho b
WHERE distrito = 'PORTO'
AND (amenity = ‘library’)
AND amenity is null
AND st_contains (st_transform(wkb_geometry, 900913), way)
GROUP BY municipio, way, amenity

3- Como fazer a contagem de bibliotecas por distrito e ordená-las por ordem decrescente?

SELECT count (a.*), distrito
FROM planet_osm_point a, caop2010_concelho
WHERE amenity = 'library'
AND st_contains (st_transform(wkb_geometry, 900913), way)
GROUP by distrito
ORDER by count desc

4- Qual a localização das bibliotecas representadas por pontos, agrupadas por municípios?

SELECT municipio, amenity, st_astext(way)
FROM planet_osm_point, caop2010_concelho
WHERE amenity = 'library'
AND st_contains (st_transform(wkb_geometry, 900913), way)
GROUP BY municipio, amenity, st_astext(way)
ORDER BY municipio ASC

5- Como criar uma tabela com todas as bibliotecas representadas por pontos?

CREATE table resultado as
SELECT a.way, b.municipio
FROM planet_osm_point a, caop2010_concelho b
WHERE amenity = 'library'
AND st_contains (st_transform(wkb_geometry, 900913), way)
GROUP BY municipio, amenity, a.way
ORDER BY municipio ASC

--Filipe1305 10:23, 30 April 2011 (BST)

Rede viária (Concelho de Esposende)

1º Passo: Em primeiro lugar através do editor do Open Street Map JOSM, seleccionei a minha área de trabalho que corresponde ao meu concelho de residência: Esposende. Fiz o download da informação, e depois gravei os dados no formato “OSM Server Files” (*.osm).

2º Passo: No software Quantum GIS, activei a extensão “OpenStreetMap plugin” e através deste carreguei a informação exportada no passo anterior como demonstra a seguinte figura:

3º Passo: Passamos a ter a informação dividida segundo o tipo de topologia (pontos, linhas e polígonos), no entanto, de forma a que esta seja compatível com o PostGreSQL é necessário gravar cada uma das layers para shapefile, bastando para isso, seleccionar com o botão direito do rato a layer e “salvar como”. Neste passo definimos o nome e a pasta em que gravamos e definimos o sistema de coordenadas para WGS 84/UTM 29N # EPSG: 32629 (coordenadas projectadas):

4º Passo: No pgAdmin é necessário em primeiro lugar conectar o servidor “localgeobox” através do botão direito e “conectar”. Após este passo procede-se à criação de uma nova base de dados com o nome de “Conc_Esposende”, tendo o cuidado de utilizar como modelo o “template_postis” e o espaço de tabelas “pg_default”

5º Passo: Depois de actualizar a base de dados existente, é necessário adicionar a informação que consta nas três shapefiles resultantes da exportação do Quantum Gis, desta forma através da linha de comandos do Ubuntu escrevi os seguintes comandos onde indiquei as coordenadas (32629) e o nome das tabelas:

Adicionar Polígonos: shp2pgsql -s 32629 Poligno.shp Poligonos |psql -d conc_esposende
	Adicionar Linhas: shp2pgsql -s 32629 Linhas.shp Linhas |psql -d conc_esposende
	Adicionar Pontos: shp2pgsql -s 32629 Pontos.shp Pontos |psql -d conc_esposende

6º Passo: Após a elaboração dos passos anteriores já é possível fazer as queries em SQL, a nossa base de dados:

Query 1: Quantos polígonos foram criados na tabela “polígonos” pelo user “Marcelo Silva”

Select count (user)
From poligonos
Where poligonos.user
Like ‘%Marcelo Silva%’

Query 2: Número de vias sem nome, criados pelo user “Marcelo Silva”

Select count (*)
From linhas
Where linhas.user like ‘%Marcelo Silva%’ and name is null

Query 3: Mostras os pontos cujo nome comece com “E” maiúsculo e seja seguido de 8 caracteres

select count(*)
from pontos	
where pontos.name like 'E________'

Query 4: Qual o somatório da área dos polígonos adicionados pelo User “Marcelo Silva”

select sum (st_area(the_geom)), 
user from poligonos
where poligonos.user
like '%Marcelo_Silva%'

Query 5: Mostra todos os nomes das vias não nulas por código postal “4740” (Esposende) criado pelo user ” Marcelo Silva”

Nota: Através do Quatum GIS, exportei para shapefile a informação referente aos códigos postais e através do seguinte comando adicionei a tabela ctts à base de dados conc_esposende: shp2pgsql -s 32629 ctts.shp ctts |psql -d conc_esposende

select linhas.name 
as name, linhas.user 
as name, ctts.cp4 
as Codigo_Postal 
from linhas, ctts
where ctts.cp4 like '4740' and linhas.user like '%Marcelo Silva%' and linhas.name like '_%'

Query 6: selecção de todos os pontos “bar” que se situem a menos de 100m de determinada rua (Av. Eng. Arantes e Oliveira) criado pelo user “Marcelo Silva”:

select linhas.name, pontos.user, pontos.name pontos, 
pontos.amenity, st_distance (linhas.the_geom,pontos.the_geom) 
from linhas, pontos 
where linhas.name 
like '%Av. Eng. Arantes e Oliveira%' 
and pontos.amenity
like'%bar%'
and pontos.user 
like 'Marcelo Silva' 
and st_distance (linhas.the_geom, pontos.the_geom) <100 
order by st_distance (linhas.the_geom, pontos.the_geom)

--Mrsilva 11:51, 30 April 2011 (BST)