Aula 13 - Slides Postgis

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


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);


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));


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


“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')


“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 ;


“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';


“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';


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

Documents

Aula 13 - Slides Postgis