Banco de Dados Avanado Sistemas de BD Geogrficos

Banco de Dados Avançado Sistemas de BD Geográficos Post. GIS Por: Robson do Nascimento Fidalgo Valéria Times {rdnf, vct}@cin. ufpe. br CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 1

Introdução ao Post. GIS • Post. GIS: Uma extensão Geo para o Postgre. SQL § Download • http: //postgis. refractions. net/download/ § Diretórios • Windows Program FilesPostgre. SQL8. 1sharecontrib • UNIX src/contrib/ § Manual • http: //postgis. refractions. net/docs/ CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 2

Introdução ao Post. GIS • Post. GIS segue o padrão Open. GIS § Provê suporte para todos objetos e funções da especificação SFS (Simple Features for SQL) Fonte: INPE CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 3

Introdução ao Post. GIS • Formatos WKB e WKT do Open. GIS § Duas formas padrões para manipular Objetos Geográficos • Well-Known Text (WKT) e Well-Known Binary (WKB) • Guardam informações sobre tipo e coordenadas do Objeto. Geo § Exemplos: • POINT(0 0) • LINESTRING(0 0, 1 1, 1 2) • POLYGON((0 0, 4 4, 0 0), (1 1, 2 2, 1 1)) • MULTIPOINT(0 0, 1 2) • MULTILINESTRING((0 0, 1 1, 1 2), (2 3, 3 2, 5 4)) • MULTIPOLYGON(((0 0, 4 4, 0 0), (1 1, 2 2, 1 1)), ((-1 -1, -1 -2, -2 -2, 2 -1, -1 -1))) • GEOMETRYCOLLECTION(POINT(2 3), LINESTRING((2 3, 3 4))) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 4

Introdução ao Post. GIS • SRID (Spatial Referencing System Identifier) § Todo Objeto Geográfico deve ter um SRID para ser inserido no BDGeo • Por exemplo: § Considerando a interface Geom. From. Text • Geom. From. Text (text WKT, SRID); § Pode-se inserir o seguinte Objeto Geográfico • INSERT INTO Spatial. Table (THE_GEOM, THE_NAME) VALUES (Geom. From. Text('POINT(-126. 4 45. 32)', 2000), 'A Place'); CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 5

Introdução ao Post. GIS • A especificação SFS/Open. GIS define tipos, funções e metadados para manipular Objetos. Geo • As principais tabelas de metadados são: § SPATIAL_REF_SYS guarda os IDs e as descrições textuais do sistema de coordenadas usados no BDGeo § GEOMETRY_COLUMNS guarda informações do esquema Geográfico e das propriedades dos Objetos. Geo CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 6

Introdução ao Post. GIS TABLE SPATIAL_REF_SYS ( SRID INTEGER NOT NULL PRIMARY KEY, // identificador do SRS AUTH_NAME VARCHAR(256), // nome da autoridade que especificou o SRS AUTH_SRID INTEGER, // identificador do SRS definido pela autoridade SRTEXT VARCHAR(2048), // representação WKT do SRS PROJ 4 TEXT VARCHAR(2048) // especificações para transformação de SRS ) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 7

Introdução CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 8

Introdução ao Post. GIS GEOMETRY_COLUMNS ( F_TABLE_CATALOG VARCHAR(256) NOT NULL, F_TABLE_SCHEMA VARCHAR(256) NOT NULL, // nome qualificado da tabela F_TABLE_NAME VARCHAR(256) NOT NULL, F_GEOMETRY_COLUMN VARCHAR(256) NOT NULL, //nome coluna Geo da tabela COORD_DIMENSION INTEGER NOT NULL, // dimensão (2 D ou 3 D) da coluna SRID INTEGER NOT NULL, // ID do SRS usado na tabela TYPE VARCHAR(30) NOT NULL // Tipo do objeto. Geo (POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION ) ) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 9

Introdução ao Post. GIS Note: "catalog" e "schema" são Oracle-ish. Por isso, "catálogo" fica em branco e usa-se o nome do BD do Postgre. SQL para "schema". CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 10

Usando o Post. GIS • Criando uma tabela espacial (2 passos) § 1) Criar uma tabela normal (sem campo espacial) • Exemplo: § CREATE TABLE ROADS_GEOM ( ID int 4, NAME varchar(25) ); § 2) Adicionar uma coluna espacial ("Add. Geometry. Column“) • Sintaxe: § Add. Geometry. Column([<schema_name>], <table_name>, <column_name>, <srid>, <type>, <dimension>); • Exemplo: § SELECT Add. Geometry. Column('public', 'roads_geom', 'geom', 2000, 'LINESTRING', 2); § SELECT Add. Geometry. Column( 'LINESTRING', 2); CIn/UFPE – Banco de Dados Avançado 'roads_geom', 'geom', Robson Fidalgo e Valéria Times 2000, 11

Usando o Post. GIS • Outros exemplos para criar tabelas espaciais § Assumindo que o SRID 2001 já existe CREATE TABLE parks ( PARK_ID int 4, PARK_NAME varchar(128), PARK_DATE date, PARK_TYPE varchar(2) ); SELECT Add. Geometry. Column('parks', 'park_geom', 2001, 'MULTIPOLYGON', 2 ); § Usando o tipo genérico "geometry" e um SRID indefinido (-1) CREATE TABLE roads ( ROAD_ID int 4, ROAD_NAME varchar(128) ); SELECT Add. Geometry. Column( 'roads', 'roads_geom', -1, 'GEOMETRY', 3 ); CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 12

Usando o Post. GIS • Validando geometrias antes de inseri-las no BDGeo § Função ISVALID() • Valida as coordenadas de uma geometria § Exemplo: • SELECT ISVALID('LINESTRING(0 0, 1 1)'), ISVALID('LINESTRING(0 0, 0 0)'); t f § Opção default é não validar a entrada das geometrias • Para validar deve-se adicionar uma restrição à tabela § ALTER TABLE parks ADD CONSTRAINT geo_valid_chk CHECK (isvalid(park_geom)); • Cuidado: Validar polígonos pode ser muito custoso! CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 13

Usando o Post. GIS • Inserindo Objetos. Geo nas tabelas (2 formas) § 1) Usando SQL BEGIN; INSERT INTO ROADS_GEOM VALUES (1, 'Jeff Rd', Geom. From. Text('LINESTRING(191232 243118, 191108 243242)', 2000)); INSERT INTO ROADS_GEOM VALUES (2, 'Geordie Rd', Geom. From. Text('LINESTRING(189141 244158, 189265 244817)', 2000)); INSERT INTO ROADS_GEOM VALUES (3, 'Paul St', Geom. From. Text('LINESTRING(192783 228138, 192612 229814)', 2000)); INSERT INTO ROADS_GEOM VALUES (4, 'Graeme Ave', Geom. From. Text('LINESTRING(189412 252431, 189631 259122)', 2000)); INSERT INTO ROADS_GEOM VALUES (5, 'Phil Tce', Geom. From. Text('LINESTRING(190131 224148, 190871 228134)', 2000)); INSERT INTO ROADS_GEOM VALUES (6, 'Dave Cres', Geom. From. Text('LINESTRING(198231 263418, 198213 268322)', 2000)); COMMIT; CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 14

Usando o Post. GIS § 2) Usando o Loader shp 2 pgsql • Converte um shape file para pgsql. sql • Shp 2 pgsql [<options>] <shapefile> <tablename> <database name> § <shapefile> : nome do shape file s/ extensão (inclui shp, shx, dbf) § <tablename> : nome da tabela destino. Por default, a geometria fica na coluna 'geo_value' § <database name> : nome do BDGeo destino § [<options>] : opções de configuração » Principais: (-a || -c || -d || -p mutuamente exclusivas), -D. » -a : anexa dados a uma tabela existente » -c : cria uma tabela e insere os dados (modo padrão) » -d : apaga a tabela antes de criar outra » -p : lê o esquema do shape file para criar uma tabela » -D : permite fazer dump de grandes volumes de dados. Usa COPY no lugar de INSERT INTO). CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 15

Usando o Post. GIS § 2) Usando o Loader shp 2 pgsql – Cont. • Exemplo com arquivo intermediário: § Shp 2 pgsql -c C: ESRIAV_GIS 30AVTUTORARCVIEWqstartworld 94 teste > c: tempworld 94. sql § psql -d teste -U postgres -h g 1 c 10 -f c: tempworld 94. sql » (-d: nome do BD e -f nome do arquivo) • Exemplo sem arquivo intermediário: § shp 2 pgsql -c C: ESRIAV_GIS 30AVTUTORARCVIEWqstartmexico teste | psql -d teste -U postgres -h g 1 c 10 § shp 2 pgsql -c C: ESRIAV_GIS 30AVTUTORARCVIEWqstartcanada teste | psql -d teste -U postgres -h g 1 c 10 CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 16

Usando o Post. GIS • Consultas simples a Objetos. Geo § Forma básica • SELECT id, As. Text(geom) AS geom, name FROM ROADS_GEOM; § Operadores úteis • &&: Informa se o MBR de uma geometria intersecta o MBR de outra • ~= : Testa se duas geometrias são geometricamente idênticas • = : Testa se os MBR de duas geometrias são idênticos • Exemplo: SELECT ID, NAME FROM ROADS_GEOM WHERE GEOM = Geom. From. Text('LINESTRING(191232 243118, 191108 243242)', 2000); CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 17

Usando o Post. GIS • Consultas simples a Objetos. Geo § Exemplo (cont): SELECT ID, NAME FROM ROADS_GEOM WHERE GEOM ~= Geom. From. Text('LINESTRING(191232 243118, 191108 243242)', 2000); SELECT ID, NAME FROM ROADS_GEOM WHERE GEOM && Geom. From. Text('POLYGON((191232 243117, 191232 243119, 191234 243117, 191232 243117))', -1); CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 18

Usando o Post. GIS • Exportando dados para Shape File § pgsql 2 shp • Converte uma tabela. Geo do Postgre. SQL para shape file ESRI § pgsql 2 shp [<options>] <database name> <table name> § <database name> nome do BDGeo origem § <tablename> nome da tabela origem § [<options>] opções de configuração » -d: define o arquivo dump para 3 D (padrão = 2 D) » -f <filename>: especificar o nome do shape file (padrão = nome da tabela). » -h <host>: especifica o host onde está o banco de dados (padrão =localhost). » -p <port>: especifica a porta de conexão (padrão = 5432). » -P <password>: especifica a senha. » -u <user>: especifica o usuário. » -g <geometry_column> especifica a coluna. Geo a ser exportada. CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 19

Usando o Post. GIS • Exportando dados para Shape File (Cont. ) § pgsql 2 shp • Exemplos: § pgsql 2 shp -u postgres -P postgres teste world 94 § pgsql 2 shp -f World 94 Exp -h localhost -p 5432 -u postgres -P postgres teste world 94 CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 20

Usando o Post. GIS • Usando Índices Geográficos § Melhor opção: usar uma R-Tree implementada no topo do mecanismo Gi. ST (Generalized Search Tree). • A implementação nativa da R-Tree do Postgre. SQL não é tão robusta quanto a implementação feita pelo mecanismo Gi. ST • Desde a versão 0. 6 do Postgre. SQL não recomenda-se a sua R-Tree Consultas convencionais em tabelas geográficas não usufruem do mecanismo Gi. ST CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 21

Usando o Post. GIS • Usando Índices Geográficos (esquema R-Tree) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 22

Usando o Post. GIS • Usando Índices Geográficos § Sintaxe: CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] GIST_GEOMETRY_OPS ); § Exemplo: CREATE INDEX world 94_idx ON world 94 USING GIST (the_geom GIST_GEOMETRY_OPS); Depois de criar os índices é boa prática executar: VACUUM ANALYZE [table_name] [column_name] para liberar tuplas obsoletas/excluídas CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 23

Usando o Post. GIS • É possível usufruir do Gi. ST na consulta abaixo? § Selecione as casas que estejam a menos de 1000 metros do ponto (100000, 200000): SELECT geometria FROM casas WHERE distance(geometria, Geometry. From. Text(‘POINT(100000, 200000)’, -1)) < 1000; CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 24

Usando o Post. GIS • É possível usufruir do Gi. ST na consulta abaixo? § Selecione as padarias que estejam a menos de 1000 metros do ponto (100000, 200000): SELECT geometria FROM padarias WHERE distance(geometria, Geometry. From. Text( ‘POINT(100000, 200000)’, -1)) < 1000; Esta consulta será lenta se a tabela for grande! Somente consultas com operadores que usam MBR (ex: &&) tiram vantagem do índice espacial. Funções como distância não usufruem do índice. CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 25

Usando o Post. GIS • Pode-se usufruir do índice de consulta (“query box”) usando uma janela SELECT geometria FROM padarias WHERE geometria && ‘BOX 3 D(99000 199000, 101000 201000)’: : box 3 d AND distance(geometria, Geometry. From. Text(‘POINT(100000, 200000)’, -1)) < 1000; Note que a janela de consulta (BOX 3 D + &&) forma um quadrado centralizado sobre o ponto original CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 26

Usando o Post. GIS • Principais funções de relacionamento espacial § Distance(geometry, geometry) § Equals(geometry, geometry) § Disjoint(geometry, geometry) § Intersects(geometry, geometry) § Touches(geometry, geometry) § Crosses(geometry, geometry) § Within(geometry, geometry) § Overlaps(geometry, geometry) § Contains(geometry, geometry) § Intersects(geometry, geometry) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 27

Usando o Post. GIS • Principais funções de processamento geométrico § Centroid(geometry) § Area(geometry) § Length(geometry) § Point. On. Surface(geometry) § Boundary(geometry) § Buffer(geometry, double, [integer]) § Intersection(geometry, geometry) § Difference(geometry, geometry) § Geom. Union(geometry, geometry) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 28

Usando o Post. GIS Existem muitas outras funções! Consultar tópico 6 (Post. GIS Reference) do manual Post. Gis. CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 29

Usando o Post. GIS • Exemplos de consultas espaciais § Qual o comprimento total de todas as estradas? (em km) SELECT sum ( length ( the_geom ) ) / 1000 AS km_roads FROM bc_roads; § Qual é a área da cidade de RECIFE? (em hectares) SELECT area ( the_geom ) / 10000 AS hectares FROM bc_municipality WHERE name = ‘RECIFE‘ ; CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 30

Usando o Post. GIS • Exemplos de consultas espaciais § Qual é o maior município por área? (em hectares) SELECT name, area (the_geom ) / 10000 AS hectares FROM bc_municipality ORDER BY hectares DESC LIMIT 1 ; § Qual é o tamanho das estradas contidas em cada município? SELECT m. name, sum ( length ( r. the_geom ) ) / 1000 as roads_km FROM bc_roads AS r , bc_municipality AS m WHERE r. the_geom && m. the_geom AND contains(m. the_geom , r. the_geom) GROUP BY m. name ORDER BY roads_km ; CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 31

Usando o Post. GIS • Exemplos de consultas espaciais § Crie uma tabela com todas as estradas de Recife? CREATE TABLE pg_roads as SELECT intersection (r. the_geom, m. the_geom) AS intersection_geom, length ( r. the_geom ) AS rd_orig_length , r. * FROM bc_roads AS r, bc_municipality AS m WHERE r. the_geom && m. the_geom AND intersects ( r. the_geom, m. the_geom ) AND m. name = 'RECIFE‘ ; CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 32

Usando o Post. GIS • Exemplos de consultas espaciais § Qual é o tamanho (em km), da Av. Caxangá em Recife? SELECT sum ( length ( r. the_geom ) ) / 1000 AS kilometers FROM bc_roads r, bc_municipality m WHERE r. the_geom && m. the_geom AND r. name = ‘ Caxangá ' AND m. name = ‘ RECIFE ‘ ; CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 33

Usando o Post. GIS • Exemplos de consultas espaciais com otimização § Listar o ID das regiões vizinhas à região 1234. SELECT r 2. geo_id FROM regiao r 1, regiao r 2 WHERE touches (r 1. the_geom, r 2. the_geom) AND ( r 2. geo_id <> '1234‘ ) AND ( r 1. geo_id = '1234‘ ) SELECT r 2. geo_id FROM regiao r 1, regiao r 2 WHERE touches ( r 1. the_geom, r 2. the_geom ) AND (r 1. the_geom && r 2. the_geom) otimizando AND ( r 2. geo_id <> '1234‘ ) AND ( r 1. geo_id = '1234‘ ) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 34

Usando o Post. GIS • Exemplos de consultas espaciais com otimização § Listar o número de homicídios ocorridos em Pernambuco. SELECT COUNT(*) FROM homicidios h, estados e WHERE contains (e. the_geom, h. the_geom) AND e. nome = 'PERNAMBUCO'; SELECT COUNT(*) FROM homicidios h, estados e WHERE contains (e. the_geom, h. the_geom) AND ( e. the_geom && h. the_geom ) otimizando AND e. nome = ‘ PERNAMBUCO ‘ ; CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 35

Post. GIS – Exercício y • Criar uma tabela chamada “exemplo_lotes” para armazenar informações a respeito dos lotes abaixo: 13 11 9 L 4 7 L 3 L 5 L 1 5 3 L 2 1 1 3 5 7 CIn/UFPE – Banco de Dados Avançado 9 11 13 15 17 19 21 x Robson Fidalgo e Valéria Times 36

Post. GIS – Exercício • Criar uma tabela chamada “exemplo_quadras” para armazenar informações a respeito das quadras abaixo: y 13 11 9 Q 2 7 Q 1 5 3 1 1 3 5 7 CIn/UFPE – Banco de Dados Avançado 9 11 13 15 17 19 21 x Robson Fidalgo e Valéria Times 37

Post. GIS – Exercício • Responda: § Quais os lotes vizinhos ao lote L 4? § Quantos lotes estão dentro da quadra Q 1? § Uma pessoa resolveu comprar todos os lotes da quadra Q 1. Criar uma nova geometria L 6 que represente toda a área dos lotes originais. § Criar uma única tabela para armazenar os lotes e as quadras (exemplo_quadras_lotes). Esta tabela, além do identificador, possui o tipo do objeto e as geometrias. Mas, estas últimas não podem ter “sobreposição” (a área da quadra não deve sobrepor a do lote ? !!). CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 38

CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 39

Roteiro para Projeto BD Geo • Criar Minimundo, modelo conceitual e esquema lógico com tabelas espaciais • Implementar as tabelas no Post. GIS, fazendo uso do comando de carga shp 2 pgsql • Implementar as principais consultas de verificação de relacionamentos espaciais e de processamento geométrico fazendo uso das operações espaciais do Post. GIS • Testar e colocar o sistema em funcionamento, fazendo uso de um servidor de mapas para visualização dos resultados (JUMP, Terra. View, Geo. Client, Mapserver, Thuban, GRASS, QGIS) • Data da Entrega: 19 / 07 CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 40

Roteiro para Nota Máxima • • Descrição de Minimundo corretos Modelagem Conceitual Esquema Relacional Implementar as seguintes consultas de verificação de relacionamentos espaciais: § § § § § Distance ( geometry, geometry ) Equals ( geometry, geometry ) Disjoint ( geometry, geometry ) Intersects ( geometry, geometry ) Touches ( geometry, geometry ) Crosses ( geometry, geometry ) Within ( geometry, geometry ) Overlaps ( geometry, geometry ) Contains ( geometry, geometry ) Intersects ( geometry, geometry ) CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 41

Roteiro para Nota Máxima • Implementar as seguintes consultas de processamento geométrico: § § § § § Centroid (geometry) Area (geometry) Length (geometry) Point. On. Surface (geometry) Boundary (geometry) Buffer (geometry, double, [integer]) Intersection (geometry, geometry) Difference (geometry, geometry) Geom. Union (geometry, geometry) • Defesa do projeto com qualidade e segurança CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 42

CIn/UFPE – Banco de Dados Avançado Robson Fidalgo e Valéria Times 43