Banco de DadosAgosto, 2006
Banco de Dados GeográficosGeográficos
PostGISGilberto Ribeiro de Queiroz – [email protected] Câmara – [email protected] Reis Ferreira – [email protected]
Disponível em http://www.dpi.inpe.br/cursos/ser303
@ p p
Tópicos da AulaTópicos da Aula
Tipos de Dados EspaciaisTipos de Dados Espaciais
O d E i iOperadores Espaciais
I d ã E i lIndexação Espacial
C l E i iConsultas Espaciais
PostGISPostGIS
Extensão do SGBD PostgreSQLExtensão do SGBD PostgreSQLTipos de dados espaciaisOperadores EspaciaisOperadores EspaciaisÍndice Espacial
Segue a especificaçãoproposta na SFS-SQL
GEOMETRYGEOMETRY
POINT
LINESTRING
GEOMETRYCOLLECTION
MULTIPOINT
POINT
LINESTRING
GEOMETRYCOLLECTION
MULTIPOINT
POLYGON MULTILINESTRING
MULTIPOLYGON
POLYGON MULTILINESTRING
MULTIPOLYGON
PostGIS – Tipos de Dados EspaciaisPostGIS Tipos de Dados Espaciais
Point: (0 0 0)Point: (0 0 0)LineString: (0 0, 1 1, 2 2)Polygon: ((0 0 0, 4 0 0, 4 4 0, 0 4 0, 0 0 0), ( 1 0 0, ...), ...)MultiPoint: (0 0 0, 4 4 0)MultiLineString: ((0 0 0, 1 1 0, 2 2 0), (4 4 0, 5 5 0, 6 6 0))MultiPolygon: (((0 0 0, 4 0 0, 4 4 0, 0 4 0, 0 0 0), (...), ...), ...)MultiPolygon: (((0 0 0, 4 0 0, 4 4 0, 0 4 0, 0 0 0), (...), ...), ...)GeometryCollection: (POINT(2 2 0), LINESTRING((4 4 0, 9 9 0))
GEOMETRYGEOMETRY
POINT GEOMETRYCOLLECTIONPOINT GEOMETRYCOLLECTION
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGONMULTIPOLYGON
PostGIS – Tipos de Dados EspaciaisPostGIS Tipos de Dados Espaciais
Criação de Tabelas com tipos de dados espaciais:Criação de Tabelas com tipos de dados espaciais:CREATE TABLE distritossp( cod SERIAL,( cod SERIAL,
sigla VARCHAR(10),denominacao VARCHAR(50),PRIMARY KEY (cod)
);
SELECT AddGeometryColumn('terralibdb','distritossp', 'spatial data', -1, 'POLYGON', _2);
PostGIS – Tipos de Dados EspaciaisPostGIS Tipos de Dados Espaciais
Inserindo dados em tabelas com tipos de dadosInserindo dados em tabelas com tipos de dados espaciais:INSERT INTO distritosspINSERT INTO distritossp(sigla, denominacao, spatial_data) VALUES('CTR', 'Centro',GeometryFromText('Polygon((0 0 10 0 10 10 0GeometryFromText( Polygon((0 0, 10 0, 10 10, 0 10, 0 0), (5 5, 5 6, 6 6, 6 5, 5 5))', -1));
PostGIS – Tipos de Dados EspaciaisPostGIS Tipos de Dados Espaciais
Recuperando dados em tabelas com tipos de dadosRecuperando dados em tabelas com tipos de dados espaciais:SELECT sigla, denominacao, spatial dataSELECT sigla, denominacao, spatial_dataFROM distritossp;
SELECT sigla, denominacao, ASTEXT(spatial_data)FROM distritossp;
PostGIS – MetadadosPostGIS Metadados
Sistema de Coordenadas:Sistema de Coordenadas:
spatial_ref_sys
Attribute Type Modifier
srid INTEGER PK
auth_name VARCHAR(256)
auth srid INTEGERauth_srid INTEGER
srtext VARCHAR(2048)
proj4text VARCHAR(2048)
PostGIS – MetadadosPostGIS Metadados
Tabelas e colunas com tipos espaciais:Tabelas e colunas com tipos espaciais:
geometry_columnsg y
Attribute Type Modifier
f table catalog VARCHAR(256) PKf_table_catalog VARCHAR(256) PK
f_table_schema VARCHAR(256) PK
f table name VARCHAR(256) PKf_table_name VARCHAR(256) PK
f_geometry_column VARCHAR(256) PK
coord dimension INTEGERcoord_dimension INTEGER
srid INTEGER FK
VARCHAR(30)type VARCHAR(30)
PostGIS – Indexação EspacialPostGIS Indexação Espacial
Podemos utilizar uma R Tree implementada no topo doPodemos utilizar uma R-Tree implementada no topo do mecanismo GiST para os tipos de dados espaciais:
CREATE INDEX sp_idx_name ON distritossp
USING GIST (spatial_data GIST_GEOMETRY_OPS);
2
5
68
11
12
114
d
3
7
9
10
13a c
R-Tree
4 bR
*SET ENABLE_SEQSCAN=OFF
PostGIS – Indexação EspacialPostGIS Indexação Espacial
Exploração dos índices:Exploração dos índices:Em predicados que envolvam operadores espaciais;
Ex: consulta por janela
Operador: &&
SELECT COUNT(*)SELECT COUNT(*)FROM TABELA_POLIGONOS
WHERE 'BOX3D(375421.967006 7337341.000355, 438164.882699 7435582.150681)'::box3d && spatial_data;
PostGIS – Consultas EspaciaisPostGIS Consultas Espaciais
Operadores Topológicos:Operadores Topológicos:equals(geometry, geometry)disjoint(geometry geometry)disjoint(geometry, geometry)intersects(geometry, geometry)touches(geometry, geometry)crosses(geometry, geometry) within(geometry, geometry)
l ( t t )overlaps(geometry, geometry)contains(geometry, geometry)
PostGIS – Consultas EspaciaisPostGIS Consultas Espaciais
Operadores Conjunto:Operadores Conjunto:intersection(geometry, geometry):geometrygeomUnion(geometry geometry):geometrygeomUnion(geometry, geometry):geometrysymdifference(geometry, geometry):geometrydifference(geometry, geometry):geometry
Operadores Métricos:distance(geometry,geometry):doublearea(geometry):double
Cenário 1:O óUsando Operadores Topológicos
“Recuperar o identificador dos objetos que são vizinhos“Recuperar o identificador dos objetos que são vizinhos ao objeto 3550308” na tabela polygons4.
SELECT p2.object_idFROM polygons4 p1, polygons4 p2WHERE touches(p1.spatial_data, p2.spatial_data)AND (p2.object_id <> '3550308')AND (p1 object id = '3550308')AND (p1.object_id 3550308 )
3550308
Cenário 1:O óUsando Operadores Topológicos
“Recuperar o identificador dos objetos que são vizinhos“Recuperar o identificador dos objetos que são vizinhos ao objeto 3550308” na tabela polygons4.
SELECT p2.object_idFROM polygons4 p1, polygons4 p2WHERE touches(p1.spatial_data, p2.spatial_data)AND (p1.spatial_data && p2.spatial_data)AND (p2 object id <> '3550308')
3550308
AND (p2.object_id <> 3550308 )AND (p1.object_id = '3550308')
Cenário 2:O óUsando Operadores Topológicos
“Recuperar o número de focos de queimadas (tabela“Recuperar o número de focos de queimadas (tabela points5) ocorridos no município 3550308 (tabela polygons4)”polygons4)
SELECT COUNT(*)FROM points5 pt, polygons4 polWHERE contains(pol.spatial_data, pt.spatial_data)AND pol object id = '5108501';AND pol.object_id = 5108501 ;
Cenário 2:O óUsando Operadores Topológicos
“Recuperar o número de focos de queimadas (tabela“Recuperar o número de focos de queimadas (tabela points5) ocorridos no município 3550308 (tabela polygons4)”polygons4)
SELECT COUNT(*)FROM points5 pt, polygons4 polWHERE contains(pol.spatial_data, pt.spatial_data)AND (pol spatial data && pt spatial data)AND (pol.spatial_data && pt.spatial_data)AND pol.object_id = '5108501';
Cenário 3:O éUsando Operadores Métricos
“Recuperar os focos de queimadas (points5) que“Recuperar os focos de queimadas (points5) que estejam a menos de 3Km do bairro Boacava”.
SELECT pt1.*FROM points5 pt1,
points5 pt2WHERE (distance(pt1.spatial_data, pt2.spatial_data)
< 0 135)< 0.135)AND pt2.object_id <> '33819'AND pt1.object_id = '33819';
Cenário 3:O éUsando Operadores Métricos
“Recuperar os focos de queimadas (points5) que“Recuperar os focos de queimadas (points5) que estejam a menos de 3Km do bairro Boacava”.
SELECT pt1.*FROM points5 pt1,
points5 pt2WHERE (distance(pt1.spatial_data, pt2.spatial_data)
< 0 135)< 0.135)AND (expand(pt1.spatial_data, 0.135) &&
pt2.spatial_data)AND t2 bj t id <> '33819'AND pt2.object_id <> '33819'AND pt1.object_id = '33819';
Cenário 3:O éUsando Operadores Métricos
Aplicar a consulta anterior na tabela points7 ao objeto:Aplicar a consulta anterior na tabela points7 ao objeto:0000000000000000400844
PostGIS – ExercíciosPostGIS ExercíciosCriar uma tabela chamada “login_ft_lotes” para armazenar informações a respeito dos lotes abaixo:armazenar informações a respeito dos lotes abaixo:
y13
11
7
9
L3L4
5
L1
L2
1
3
L1
x1 3 5 7 9 11 13 15 17 19 21
1
PostGIS – ExercíciosPostGIS ExercíciosCriar uma tabela chamada “login_ft_quadras” para armazenar informações a respeito das quadras abaixo:armazenar informações a respeito das quadras abaixo:
y13
11
7
9
Q2
5Q1
1
3
x1 3 5 7 9 11 13 15 17 19 21
1
PostGIS – ExercíciosPostGIS ExercíciosQuais os lotes vizinhos ao lote L2 ?
Quantos lotes estão dentro da quadra Q1?
Quantos lotes estão dentro da quadra Q2?
Quais os lotes vizinhos ao lote L3?
PostGIS – ExercíciosPostGIS ExercíciosUma pessoa resolveu comprar todos os lotes da quadra Q1. Criar uma nova geometria L4 que represente toda a área dos lotesuma nova geometria L4 que represente toda a área dos lotes originais.
Criar uma única tabela para armazenar os lotes e as quadras (login_ft_quadras_lotes), além do identificador, deverá haver um tipo do objeto, além disso, as geometrias não podem ter p j g p“sobreposição” (ou seja a área da quadra não deve estar sobreposta com a do lote ?!!).
FIMFIM