TSI11-TEBD-Prova001-OMT-G - PostGIS

Bancos de Dados Geográficos e WebMapping NOME: GABARITO
DATA: 10/10/2013
PROVA 001 – Modelagem OMT-G e PostGIS
Estudo de caso: Sistema de Ocorrências Policiais
Considere a seguinte especificação de requisitos para um sistema voltado para o
acompanhamento de ocorrências policiais em uma região metropolitana.
1. A região envolve vários municípios conturbados e é dividida em áreas que não
se sobrepõem, sendo cada área de responsabilidade de uma companhia da
polícia militar. Serão registradas as unidades (quartéis) da PM, bem como
delegacias da polícia civil.
2. O sistema registrará e localizará espacialmente, para fins de análise
geoestatística, a ocorrência de crimes violentos dos seguintes tipos: assalto a
mão armada, sequestro, homicídio, tentativa de homicídio, estupro, tentativa de
estupro e roubo de veículos.
3. Sobre cada ocorrência, tem-se o tipo (dentro da tipologia acima), data e hora da
ocorrência e endereço aproximado.
4. O sistema deve manter informação de geocodificação que permita localizar
espacialmente as ocorrências registradas.
5. A PM mantém um cadastro de pontos tipicamente problemáticos (como bares,
motéis, pontos de venda de drogas, e outros), associando a cada ponto um
texto descritivo e fotos digitais obtidas no local.
6. Cada companhia dispõe de certo número de viaturas, que circulam por sua
região de responsabilidade.
7. As viaturas são equipadas com um receptor GPS, que transmite sua localização
para a central a cada 5 minutos. Dinamicamente, a posição instantânea de cada
viatura é usada pela central da PM para determinar o atendimento a
emergências.
8. O sistema mantém ainda a localização de todos os hospitais de pronto-socorro
na região, bem como os pontos de referência de cada viatura do SAMU, de
modo a poder orientar a tomada de decisões sobre um curso de ação no
momento da ocorrência de alguma situação de emergência.
[Adaptado de MONTEIRO, B. R.; JUNIOR DAVIS, C. A. Modelagem Conceitual de Banco de
Dados Geográficos. In: SIMPÓSIO BRASILEIRO DE BANCO DE DADOS – SBBD, 28.,
Recife-PE, 2013. Anais... (minicurso). Recife: SBC, 2013.]
1 Bancos de Dados Geográficos e WebMapping Com base no estudo de caso apresentado, resolva as questões a seguir.
1. Crie o diagrama OMT-G para o banco de dados do estudo de caso apresentado.
[ 3.5 pontos ].
2 Bancos de Dados Geográficos e WebMapping 2. Apresente o código SQL para criar: o banco de dados; uma tabela com campo do
tipo polígono; uma tabela com campo do tipo ponto [ 1 . 5 p o n t o s ] .
CREATE DATABASE ocorrencias template = template_postgis_20;
CREATE TABLE municipio (
cod_ibge SERIAL NOT NULL,
CONSTRAINT pk_municipio PRIMARY KEY (cod_ibge)
);
SELECT AddGeometryColumn ('public', 'municipio', 'the_geom', -1,
'POLYGON', 2);
CREATE TABLE hospital (
id_hospital SERIAL NOT NULL,
CONSTRAINT pk_hospital PRIMARY KEY (id_hospital)
);
SELECT AddGeometryColumn ( 'public' , 'hospital' , 'the_geom' ,
-1 , 'POINT', 2);
3. Apresente o código SQL para inserir dados em uma tabela com campo polígono
[ 1.0 ponto ].
INSERT INTO municipio (the_geom ) VALUES (ST_GeometryFromText(
'POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))', -1 ) );
4. Apresente o código SQL para inserir dados em uma tabela com campo ponto
[ 1.0 ponto ].
INSERT INTO hospital (the_geom ) VALUES (ST_GeometryFromText(
'POINT(1 1)', -1 ) );
5. E s c r e v a u m a c o n s u l t a q u e u t i l i z e
operações topológicas [ 1.0 ponto ].
uma
função
relacionada
a
SELECT h.id_hospital
FROM municipio m, hospital h
WHERE ST_Contains (m.the_geom, h.the_geom)
3 Bancos de Dados Geográficos e WebMapping 6. E s c r e v a u m a c o n s u l t a q u e u t i l i z e
operações métricas [ 1.0 ponto ].
uma
função
relacionada
a
SELECT ST_Distance(h1.the_geom, h2.the_geom)
FROM hospital h1, hospital h2
WHERE h1.id_hospital = 1 AND
h1.id_hospital <> h2.id_hospital
7. E s c r e v a u m a c o n s u l t a e n v o l v e n d o m a i s d e u m a e n t i d a d e e q u e
retorne um objeto geográfico [ 1.0 ponto ].
SELECT ST_AsText (h.the_geom)
FROM municipio m, hospital h
WHERE ST_Contains (m.the_geom, h.the_geom)
As respostas devem ser colocadas em um arquivo texto, nomeado como PROVA001TSI11-SeuNome, o qual será enviado para o professor conforme instruções em sala.
4